April 3, 2012 at 8:47 am
Hi Everyone,
Hope All is well.
I am noticing deadlocks in my production environment. All the deadlocks seem to come from one table from the 4 update statements below:
update [dbo].[SmsJob]
set [MessagesSent] = @0, [MessagesCompleted] = @1, [LastUpdatedAt] = @2
where (((([JobID] = @3) and ([Status] = @4)) and ([MessagesSent] = @5)) and ([MessagesCompleted] = @6))
',N'@0 int,@1 int,@2 datetime2(7),@3 uniqueidentifier,@4 int,@5 int,@6 int',@0=1343,@1=1343,@2='2012-03-21 13:02:37.0338951',@3='6CE48B2A-31F9-46ED-80D1-E9F2B516E61F',@4=1,@5=1342,@6=1342
update [dbo].[SmsJob]
set [MessagesSent] = @0, [MessagesCompleted] = @1, [LastUpdatedAt] = @2
where (((([JobID] = @3) and ([Status] = @4)) and ([MessagesSent] = @5)) and ([MessagesCompleted] = @6))
',N'@0 int,@1 int,@2 datetime2(7),@3 uniqueidentifier,@4 int,@5 int,@6 int',@0=1344,@1=1344,@2='2012-03-21 13:02:37.7680586',@3='6CE48B2A-31F9-46ED-80D1-E9F2B516E61F',@4=1,@5=1343,@6=1343
update [dbo].[SmsJob]
set [Status] = @0, [LastUpdatedAt] = @1
where (((([JobID] = @2) and ([Status] = @3)) and ([MessagesSent] = @4)) and ([MessagesCompleted] = @5))
',N'@0 int,@1 datetime2(7),@2 uniqueidentifier,@3 int,@4 int,@5 int
update [dbo].[SmsJob]
set [Status] = @0, [LastUpdatedAt] = @1
where (((([JobID] = @2) and ([Status] = @3)) and ([MessagesSent] = @4)) and ([MessagesCompleted] = @5))
',N'@0 int,@1 datetime2(7),@2 uniqueidentifier,@3 int,@4 int,@5 int
Please find the deadlock information below:
deadlock-list
deadlock victim=process9a5948
process-list
process id=process9a5948 taskpriority=0 logused=0 waitresource=KEY: 6:72057594068664320 (87fe34f8082b) waittime=501 ownerId=91400952 transactionname=user_transaction lasttranstarted=2012-04-03T09:13:44.043 XDES=0x928aee80 lockMode=U schedulerid=3 kpid=1504 status=suspended spid=63 sbid=2 ecid=0 priority=0 trancount=2 lastbatchstarted=2012-04-03T09:13:44.540 lastbatchcompleted=2012-04-03T09:13:44.053 lastattention=2012-04-03T08:55:19.923 clientapp=.Net SqlClient Data Provider hostname=APPSERVER hostpid=4564 loginname=PCSetadmin isolationlevel=serializable (4) xactid=91400952 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=adhoc line=1 stmtstart=144 sqlhandle=0x02000000d0e338368562522edeee18412fca2f10e520f002
update [dbo].[SmsJob]
set [MessagesSent] = @0, [MessagesCompleted] = @1, [LastUpdatedAt] = @2
where (((([JobID] = @3) and ([Status] = @4)) and ([MessagesSent] = @5)) and ([MessagesCompleted] = @6))
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
inputbuf
(@0 int,@1 int,@2 datetime2(7),@3 uniqueidentifier,@4 int,@5 int,@6 int)update [dbo].[SmsJob]
set [MessagesSent] = @0, [MessagesCompleted] = @1, [LastUpdatedAt] = @2
where (((([JobID] = @3) and ([Status] = @4)) and ([MessagesSent] = @5)) and ([MessagesCompleted] = @6))
process id=process98b4c8 taskpriority=0 logused=0 waitresource=KEY: 6:72057594068664320 (87fe34f8082b) waittime=975 ownerId=91400957 transactionname=user_transaction lasttranstarted=2012-04-03T09:13:44.063 XDES=0x825243b0 lockMode=X schedulerid=2 kpid=4436 status=suspended spid=120 sbid=2 ecid=0 priority=0 trancount=2 lastbatchstarted=2012-04-03T09:13:44.067 lastbatchcompleted=2012-04-03T09:13:44.063 clientapp=.Net SqlClient Data Provider hostname=APPSERVER hostpid=4564 loginname=PCSetadmin isolationlevel=serializable (4) xactid=91400957 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=adhoc line=1 stmtstart=130 sqlhandle=0x02000000f390b7018943ae493c03b60ef5d3dd5884759ef7
update [dbo].[SmsJob]
set [Status] = @0, [LastUpdatedAt] = @1
where (((([JobID] = @2) and ([Status] = @3)) and ([MessagesSent] = @4)) and ([MessagesCompleted] = @5))
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
unknown
inputbuf
(@0 int,@1 datetime2(7),@2 uniqueidentifier,@3 int,@4 int,@5 int)update [dbo].[SmsJob]
set [Status] = @0, [LastUpdatedAt] = @1
where (((([JobID] = @2) and ([Status] = @3)) and ([MessagesSent] = @4)) and ([MessagesCompleted] = @5))
resource-list
keylock hobtid=72057594068664320 dbid=6 objectname=DBName.dbo.SmsJob indexname=PK_SmsJob id=lock64d427b00 mode=U associatedObjectId=72057594068664320
owner-list
owner id=process98b4c8 mode=U
waiter-list
waiter id=process9a5948 mode=U requestType=convert
keylock hobtid=72057594068664320 dbid=6 objectname=DBName.dbo.SmsJob indexname=PK_SmsJob id=lock64d427b00 mode=U associatedObjectId=72057594068664320
owner-list
owner id=process9a5948 mode=S
waiter-list
waiter id=process98b4c8 mode=X requestType=convert
The DDL of the table is as follows:
CREATE TABLE [dbo].[SmsJob](
[JobID] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[OwnerAppName] [nvarchar](50) NOT NULL,
[OwnerAppVersion] [nvarchar](50) NOT NULL,
[Pri] [int] NOT NULL,
[Status] [int] NOT NULL,
[Size] [int] NOT NULL,
[MessagesSent] [int] NOT NULL,
[MessagesCompleted] [int] NOT NULL,
[PackagedAt] [smalldatetime] NOT NULL,
[SubmittedAt] [smalldatetime] NOT NULL,
[CompletedAt] [smalldatetime] NULL,
[LastUpdatedAt] [smalldatetime] NOT NULL,
CONSTRAINT [PK_SmsJob] PRIMARY KEY CLUSTERED
(
[JobID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SmsJob] ADD CONSTRAINT [DF_SmsJob_JobID] DEFAULT (newid()) FOR [JobID]
GO
ALTER TABLE [dbo].[SmsJob] ADD CONSTRAINT [DF_SmsJob_Name] DEFAULT ('') FOR [Name]
GO
ALTER TABLE [dbo].[SmsJob] ADD CONSTRAINT [DF_SmsJob_OwnerAppName] DEFAULT ('') FOR [OwnerAppName]
GO
ALTER TABLE [dbo].[SmsJob] ADD CONSTRAINT [DF_SmsJob_OwnerAppVersion] DEFAULT ('') FOR [OwnerAppVersion]
GO
ALTER TABLE [dbo].[SmsJob] ADD CONSTRAINT [DF_SmsJob_Pri] DEFAULT ((2)) FOR [Pri]
GO
ALTER TABLE [dbo].[SmsJob] ADD CONSTRAINT [DF_SmsJob_Status] DEFAULT ((1)) FOR [Status]
GO
ALTER TABLE [dbo].[SmsJob] ADD CONSTRAINT [DF_SmsJob_Size] DEFAULT ((0)) FOR [Size]
GO
ALTER TABLE [dbo].[SmsJob] ADD CONSTRAINT [DF_SmsJob_MessagesSent] DEFAULT ((0)) FOR [MessagesSent]
GO
ALTER TABLE [dbo].[SmsJob] ADD CONSTRAINT [DF_SmsJob_MessagesCompleted] DEFAULT ((0)) FOR [MessagesCompleted]
GO
ALTER TABLE [dbo].[SmsJob] ADD CONSTRAINT [DF_SmsJob_PackagedAt] DEFAULT ('1900-01-01') FOR [PackagedAt]
GO
ALTER TABLE [dbo].[SmsJob] ADD CONSTRAINT [DF_SmsJob_SubmittedAt] DEFAULT (getdate()) FOR [SubmittedAt]
GO
ALTER TABLE [dbo].[SmsJob] ADD CONSTRAINT [DF_SmsJob_LastUpdatedAt] DEFAULT (getdate()) FOR [LastUpdatedAt]
GO
I think these statements should be locking different rows, and even if they were updating the same row, one should just wait for the other. Can anyone explain why they are causing deadlocks, or give any suggestions as to how to prevent them? Is adding appropriate indexes the best solution?
Thanks
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
April 3, 2012 at 9:11 am
What runs before those updates in the transaction?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 3, 2012 at 10:05 am
Thanks for the Quick response Gail. An EF generated select runs first which is as shown below:
SELECT
[Extent1].[Pri] AS [Pri],
[Extent1].[JobID] AS [JobID],
[Extent1].[Name] AS [Name],
[Extent1].[OwnerAppName] AS [OwnerAppName],
[Extent1].[OwnerAppVersion] AS [OwnerAppVersion],
[Extent1].[Status] AS [Status],
[Extent1].[Size] AS [Size],
[Extent1].[MessagesSent] AS [MessagesSent],
[Extent1].[MessagesCompleted] AS [MessagesCompleted],
[Extent1].[PackagedAt] AS [PackagedAt],
[Extent1].[SubmittedAt] AS [SubmittedAt],
[Extent1].[CompletedAt] AS [CompletedAt],
[Extent1].[LastUpdatedAt] AS [LastUpdatedAt],
[Extent3].[JobID] AS [JobID1],
[Extent3].[ScheduledStartAt] AS [ScheduledStartAt],
[Extent3].[ExpiresAt] AS [ExpiresAt],
[Extent3].[ContactOnDays] AS [ContactOnDays],
[Extent3].[DontContactBeforeSun] AS [DontContactBeforeSun],
[Extent3].[DontContactAfterSun] AS [DontContactAfterSun],
[Extent3].[DontContactBeforeMon] AS [DontContactBeforeMon],
[Extent3].[DontContactAfterMon] AS [DontContactAfterMon],
[Extent3].[DontContactBeforeTue] AS [DontContactBeforeTue],
[Extent3].[DontContactAfterTue] AS [DontContactAfterTue],
[Extent3].[DontContactBeforeWed] AS [DontContactBeforeWed],
[Extent3].[DontContactAfterWed] AS [DontContactAfterWed],
[Extent3].[DontContactBeforeThu] AS [DontContactBeforeThu],
[Extent3].[DontContactAfterThu] AS [DontContactAfterThu],
[Extent3].[DontContactBeforeFri] AS [DontContactBeforeFri],
[Extent3].[DontContactAfterFri] AS [DontContactAfterFri],
[Extent3].[DontContactBeforeSat] AS [DontContactBeforeSat],
[Extent3].[DontContactAfterSat] AS [DontContactAfterSat],
[Extent3].[NightAfterSun] AS [NightAfterSun],
[Extent3].[NightAfterMon] AS [NightAfterMon],
[Extent3].[NightAfterTue] AS [NightAfterTue],
[Extent3].[NightAfterWed] AS [NightAfterWed],
[Extent3].[NightAfterThu] AS [NightAfterThu],
[Extent3].[NightAfterFri] AS [NightAfterFri],
[Extent3].[NightAfterSat] AS [NightAfterSat],
[Extent3].[UsePauseJob] AS [UsePauseJob],
[Extent3].[PauseJobAt] AS [PauseJobAt],
[Extent3].[ResumeJobAt] AS [ResumeJobAt]
FROM [dbo].[EmailJob] AS [Extent1]
LEFT OUTER JOIN [dbo].[EmailJobOptions] AS [Extent2] ON [Extent1].[JobID] = [Extent2].[JobID]
LEFT OUTER JOIN [dbo].[EmailJobOptions] AS [Extent3] ON [Extent2].[JobID] = [Extent3].[JobID]
WHERE [Extent1].[Status] IN (1,0,5)
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
April 3, 2012 at 10:13 am
Can you post all the statements from when the transaction starts to when it commits?
Is there any particular reason you're running in serialisable isolation level?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 3, 2012 at 10:19 am
My bad, I pasted the wrong sql. The correct select statement is pasted below:
exec sp_executesql N'SELECT
[Project2].[JobID] AS [JobID],
[Project2].[Name] AS [Name],
[Project2].[OwnerAppName] AS [OwnerAppName],
[Project2].[OwnerAppVersion] AS [OwnerAppVersion],
[Project2].[Pri] AS [Pri],
[Project2].[Status] AS [Status],
[Project2].[Size] AS [Size],
[Project2].[MessagesSent] AS [MessagesSent],
[Project2].[MessagesCompleted] AS [MessagesCompleted],
[Project2].[PackagedAt] AS [PackagedAt],
[Project2].[SubmittedAt] AS [SubmittedAt],
[Project2].[CompletedAt] AS [CompletedAt],
[Project2].[LastUpdatedAt] AS [LastUpdatedAt]
FROM ( SELECT
[Extent1].[JobID] AS [JobID],
[Extent1].[Name] AS [Name],
[Extent1].[OwnerAppName] AS [OwnerAppName],
[Extent1].[OwnerAppVersion] AS [OwnerAppVersion],
[Extent1].[Pri] AS [Pri],
[Extent1].[Status] AS [Status],
[Extent1].[Size] AS [Size],
[Extent1].[MessagesSent] AS [MessagesSent],
[Extent1].[MessagesCompleted] AS [MessagesCompleted],
[Extent1].[PackagedAt] AS [PackagedAt],
[Extent1].[SubmittedAt] AS [SubmittedAt],
[Extent1].[CompletedAt] AS [CompletedAt],
[Extent1].[LastUpdatedAt] AS [LastUpdatedAt]
FROM [dbo].[SmsJob] AS [Extent1]
WHERE (1 = [Extent1].[Status]) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[SmsPerson] AS [Extent2]
WHERE ([Extent1].[JobID] = [Extent2].[JobID]) AND ([Extent2].[EarliestNextContact] < @p__linq__0) AND (([Extent2].[Queued] <> cast(1 as bit)) OR ([Extent2].[QueuedAt] < @p__linq__1)) AND (0 = [Extent2].[Status])
))
) AS [Project2]
ORDER BY [Project2].[Pri] ASC',N'@p__linq__0 datetime2(7),@p__linq__1 datetime2(7)',@p__linq__0='2012-03-21 13:02:37.7992996',@p__linq__1='2012-03-21 12:47:37.7992996'
The definition of the SMSPerson table is as shown:
CREATE TABLE [dbo].[SmsPerson](
[PersonID] [uniqueidentifier] NOT NULL,
[WorkspaceID] [uniqueidentifier] NOT NULL,
[JobID] [uniqueidentifier] NOT NULL,
[Status] [int] NOT NULL,
[Order] [int] NOT NULL,
[Queued] [bit] NOT NULL,
[QueuedAt] [smalldatetime] NULL,
[ContactAttempts] [int] NOT NULL,
[EarliestNextContact] [smalldatetime] NOT NULL,
[LastContactStatus] [int] NULL,
[LastSmsNumberID] [uniqueidentifier] NULL,
[LastContactedAt] [smalldatetime] NULL,
[Tagged] [bit] NOT NULL,
[CustomerID] [nvarchar](50) NOT NULL,
[Company] [nvarchar](50) NOT NULL,
[JobTitle] [nvarchar](50) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[MiddleName] [nvarchar](50) NOT NULL,
[FullName] [nvarchar](200) NOT NULL,
[NickName] [nvarchar](50) NOT NULL,
[Suffix] [nvarchar](50) NOT NULL,
[Initials] [nvarchar](50) NOT NULL,
[Birthday] [nvarchar](1) NOT NULL,
[Language] [nvarchar](50) NOT NULL,
[Gender] [nvarchar](50) NOT NULL,
[PIN] [nvarchar](50) NOT NULL,
[PContactMethod] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_SmsPerson] PRIMARY KEY NONCLUSTERED
(
[PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SmsPerson] WITH CHECK ADD CONSTRAINT [FK_SmsPerson_PersonStatus] FOREIGN KEY([Status])
REFERENCES [dbo].[PersonStatus] ([Status])
GO
ALTER TABLE [dbo].[SmsPerson] CHECK CONSTRAINT [FK_SmsPerson_PersonStatus]
GO
ALTER TABLE [dbo].[SmsPerson] WITH CHECK ADD CONSTRAINT [FK_SmsPerson_SmsJob] FOREIGN KEY([JobID])
REFERENCES [dbo].[SmsJob] ([JobID])
GO
ALTER TABLE [dbo].[SmsPerson] CHECK CONSTRAINT [FK_SmsPerson_SmsJob]
GO
ALTER TABLE [dbo].[SmsPerson] ADD CONSTRAINT [DF_SmsPerson_PersonID] DEFAULT (newid()) FOR [PersonID]
GO
ALTER TABLE [dbo].[SmsPerson] ADD CONSTRAINT [DF_SmsPerson_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[SmsPerson] ADD CONSTRAINT [DF_SmsPerson_Queued] DEFAULT ((0)) FOR [Queued]
GO
ALTER TABLE [dbo].[SmsPerson] ADD CONSTRAINT [DF_SmsPerson_ContactAttempts] DEFAULT ((0)) FOR [ContactAttempts]
GO
ALTER TABLE [dbo].[SmsPerson] ADD CONSTRAINT [DF_SmsPerson_EarliestNextContact] DEFAULT (getdate()) FOR [EarliestNextContact]
GO
ALTER TABLE [dbo].[SmsPerson] ADD CONSTRAINT [DF_SmsPerson_Tagged] DEFAULT ((1)) FOR [Tagged]
GO
ALTER TABLE [dbo].[SmsPerson] ADD CONSTRAINT [DF_SmsPerson_CustomerID] DEFAULT ('') FOR [CustomerID]
GO
ALTER TABLE [dbo].[SmsPerson] ADD CONSTRAINT [DF_SmsPerson_Company] DEFAULT ('') FOR [Company]
GO
ALTER TABLE [dbo].[SmsPerson] ADD CONSTRAINT [DF_SmsPerson_JobTitle] DEFAULT ('') FOR [JobTitle]
GO
ALTER TABLE [dbo].[SmsPerson] ADD CONSTRAINT [DF_SmsPerson_FirstName] DEFAULT ('') FOR [FirstName]
GO
ALTER TABLE [dbo].[SmsPerson] ADD CONSTRAINT [DF_SmsPerson_LastName] DEFAULT ('') FOR [LastName]
GO
ALTER TABLE [dbo].[SmsPerson] ADD CONSTRAINT [DF_SmsPerson_MiddleName] DEFAULT ('') FOR [MiddleName]
GO
ALTER TABLE [dbo].[SmsPerson] ADD CONSTRAINT [DF_SmsPerson_FullName] DEFAULT ('') FOR [FullName]
GO
ALTER TABLE [dbo].[SmsPerson] ADD CONSTRAINT [DF_SmsPerson_NickName] DEFAULT ('') FOR [NickName]
GO
ALTER TABLE [dbo].[SmsPerson] ADD CONSTRAINT [DF_SmsPerson_Suffix] DEFAULT ('') FOR [Suffix]
GO
ALTER TABLE [dbo].[SmsPerson] ADD CONSTRAINT [DF_SmsPerson_Initials] DEFAULT ('') FOR [Initials]
GO
ALTER TABLE [dbo].[SmsPerson] ADD CONSTRAINT [DF_SmsPerson_Birthday] DEFAULT ('') FOR [Birthday]
GO
ALTER TABLE [dbo].[SmsPerson] ADD CONSTRAINT [DF_SmsPerson_Language] DEFAULT ('') FOR [Language]
GO
ALTER TABLE [dbo].[SmsPerson] ADD CONSTRAINT [DF_SmsPerson_Gender] DEFAULT ('') FOR [Gender]
GO
ALTER TABLE [dbo].[SmsPerson] ADD CONSTRAINT [DF_SmsPerson_PIN] DEFAULT ('') FOR [PIN]
GO
ALTER TABLE [dbo].[SmsPerson] ADD CONSTRAINT [DF_SmsPerson_PContactMethod] DEFAULT ('') FOR [PContactMethod]
GO
Actually I have the isolation level set to Read Committed Snapshot Isolation but they all get executed with serializable isolation level and thats because of the nature of EF generated scripts its all hardcoded.
Thanks for your help again
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
April 3, 2012 at 10:44 am
Well the deadlock is a result of the serialisable isolation level. The select takes locks on a lot of rows, two or more of those can run concurrently that's not a problem, then when the update goes to update one row it can't because another select still holds that lock.
Solution is to get rid of the serialisable isolation level (it should be a setting on the connection or in EF) or to hint a UPD lock in the select.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 3, 2012 at 11:51 am
Thanks Gail.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply