January 4, 2012 at 9:51 am
Hi All,
Hope all is well.
I have RCSI enabled. Select statements are being deadlocked by update statements. Both are EF generated. The update statement is:
update [dbo].[SmsJob]
set [MessagesSent] = @0, [MessagesCompleted] = @1, [LastUpdatedAt] = @2
where (((([JobID] = @3) and ([Status] = @4)) and ([MessagesSent] = @5)) and ([MessagesCompleted] = @6))
This is not part of any batch and the deadlocks are not occurring whenever the update is happening. Its only happening few times and its always the culprit. Its trying to update few records.
The other thing is when the database was designed the primary keys that were created, were created on non unique values (no clustered indexes for most tables) and we are using GUIDs. I recently added few non clustered indexes and initially application was fast for few days but due to heavy inserts and updates the speed started to go down. I have scheduled my update statistics job and also am rebuilding indexes once in every 10 days. But the issue right now is the deadlocks. Although I have the RCSI isolation on the database I am seeing deadlocks.
Need your valuable inputs on this.
Thanks much
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
January 4, 2012 at 9:55 am
The snapshot isolations don't prevent deadlocks. They just prevent reader-writer deadlocks.
Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.
DBCC TRACEON(1222,-1)
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
January 4, 2012 at 10:27 am
Hi Gail,
First off I wish you a happy new year!!!
deadlock information:
Deadlock encountered .... Printing deadlock information
Wait-for graph
Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
Node:1
KEY: 6:72057594055229440 (1476b613a76f) CleanCnt:3 Mode:X Flags: 0x1
Grant List 0:
Owner:0x00000005321DA8C0 Mode: X Flg:0x40 Ref:0 Life:02000000 SPID:304 ECID:0 XactLockInfo: 0x000000018F411990
SPID: 304 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: (@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 ([MessagesC
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x0000000232541950 Mode: S SPID:247 BatchID:2 ECID:0 TaskProxy:(0x000000022F136538) Value:0x2f20f540 Cost:(0/0)
Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
Node:2
KEY: 6:72057594050248704 (1ec6a47d814b) CleanCnt:2 Mode:RangeS-S Flags: 0x1
Grant List 3:
Owner:0x00000005D7318340 Mode: RangeS-S Flg:0x40 Ref:0 Life:02000000 SPID:247 ECID:0 XactLockInfo: 0x0000000232541990
SPID: 247 ECID: 0 Statement Type: SELECT Line #: 1
Input Buf: Language Event: (@p__linq__0 datetime2(7),@p__linq__1 datetime2(7))SELECT
[Project2].[JobID] AS [JobID],
[Project2].[Name] AS [Name],
[Project2].[OwnerAppName] AS [OwnerAppName],
[Project2].[OwnerAppVersion] AS [OwnerAppVersion],
[Project2].[Pri] AS [Pri],
[P
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x000000018F411950 Mode: X SPID:304 BatchID:2 ECID:0 TaskProxy:(0x0000000231798538) Value:0x6c4374c0 Cost:(0/312)
Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
Victim Resource Owner:
ResType:LockOwner Stype:'OR'Xdes:0x0000000232541950 Mode: S SPID:247 BatchID:2 ECID:0 TaskProxy:(0x000000022F136538) Value:0x2f20f540 Cost:(0/0)
I am also attaching the deadlock graph image.
Thank You
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
January 4, 2012 at 10:28 am
Pasting the deadlock information again to remove the emoticons:
Deadlock encountered .... Printing deadlock information
Wait-for graph
Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
Node:1
KEY: 6:72057594055229440 (1476b613a76f) CleanCnt:3 Mode:X Flags: 0x1
Grant List 0:
Owner:0x00000005321DA8C0 Mode: X Flg:0x40 Ref:0 Life:02000000 SPID:304 ECID:0 XactLockInfo: 0x000000018F411990
SPID: 304 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: (@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 ([MessagesC
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x0000000232541950 Mode: S SPID:247 BatchID:2 ECID:0 TaskProxy:(0x000000022F136538) Value:0x2f20f540 Cost:(0/0)
Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
Node:2
KEY: 6:72057594050248704 (1ec6a47d814b) CleanCnt:2 Mode:RangeS-S Flags: 0x1
Grant List 3:
Owner:0x00000005D7318340 Mode: RangeS-S Flg:0x40 Ref:0 Life:02000000 SPID:247 ECID:0 XactLockInfo: 0x0000000232541990
SPID: 247 ECID: 0 Statement Type: SELECT Line #: 1
Input Buf: Language Event: (@p__linq__0 datetime2(7),@p__linq__1 datetime2(7))SELECT
[Project2].[JobID] AS [JobID],
[Project2].[Name] AS [Name],
[Project2].[OwnerAppName] AS [OwnerAppName],
[Project2].[OwnerAppVersion] AS [OwnerAppVersion],
[Project2].[Pri] AS [Pri],
[P
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x000000018F411950 Mode: X SPID:304 BatchID:2 ECID:0 TaskProxy:(0x0000000231798538) Value:0x6c4374c0 Cost:(0/312)
Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
Victim Resource Owner:
ResType:LockOwner Stype:'OR'Xdes:0x0000000232541950 Mode: S SPID:247 BatchID:2 ECID:0 TaskProxy:(0x000000022F136538) Value:0x2f20f540 Cost:(0/0)
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
January 4, 2012 at 10:30 am
Traceflag 1222 please, not 1204, and a picture of the deadlock graph is not very useful.
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
January 4, 2012 at 12:12 pm
apologize for the delay:
deadlock-list
deadlock victim=process3ed3948
process-list
process id=process3ed3948 taskpriority=0 logused=0 waitresource=KEY: 6:72057594055229440 (c85b14958736) waittime=4753 ownerId=267795423 transactionname=user_transaction lasttranstarted=2012-01-04T14:02:25.960 XDES=0x4fcb4b950 lockMode=S schedulerid=6 kpid=156 status=suspended spid=159 sbid=2 ecid=0 priority=0 trancount=1 lastbatchstarted=2012-01-04T14:02:25.963 lastbatchcompleted=2012-01-04T14:02:25.960 clientapp=.Net SqlClient Data Provider hostname=APPSERVER hostpid=6580 loginname=PCSetadmin isolationlevel=serializable (4) xactid=267795423 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=adhoc line=1 stmtstart=102 sqlhandle=0x02000000ee5f9a347525532fdabd8edeaedf298318000da3
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]
FROM ( SELECT
[Extent1].[JobID] AS [JobID],
[Extent1].[Name] AS [Name],
[Extent1].[OwnerAppVersion] AS [OwnerAppVersion],
[Extent1].[SubmittedAt] AS [SubmittedAt]
[Ext
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
Unknown
Inputbuf
Message
(@p__linq__0 datetime2(7),@p__linq__1 datetime2(7))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]
FROM ( SELECT
[Extent1].[JobID] AS [JobID],
[Extent1].[Name] AS [Name],
[Extent1].[OwnerAppVersion] AS [OwnerAppVersion],
[Extent1].[SubmittedAt] AS [SubmittedAt]
[Ext
process id=process2d1990bc8 taskpriority=0 logused=312 waitresource=KEY: 6:72057594050248704 (c2eb06fba112) waittime=4753 ownerId=267795422 transactionname=user_transaction lasttranstarted=2012-01-04T14:02:25.957 XDES=0x2e508d950 lockMode=X schedulerid=4 kpid=3032 status=suspended spid=139 sbid=2 ecid=0 priority=0 trancount=2 lastbatchstarted=2012-01-04T14:02:25.963 lastbatchcompleted=2012-01-04T14:02:25.960 clientapp=.Net SqlClient Data Provider hostname=APPSERVER hostpid=6580 loginname=PCSetadmin isolationlevel=serializable (4) xactid=267795422 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))
keylock hobtid=72057594055229440 dbid=6 objectname=PPMOnlineMsgEngine.dbo.SmsJob indexname=PK_SmsJob id=lock25b2e5280 mode=X associatedObjectId=72057594055229440
resource-list
owner-list
owner id=process2d1990bc8 mode=X
waiter-list
waiter id=process3ed3948 mode=S requestType=wait
keylock hobtid=72057594050248704 dbid=6 objectname=PPMOnlineMsgEngine.dbo.SmsJob indexname=IX_SmsJob_PriorityStatus id=lock660787200 mode=RangeS-S associatedObjectId=72057594050248704
owner-list
owner id=process3ed3948 mode=RangeS-S
waiter-list
waiter id=process2d1990bc8 mode=X requestType=wait
Thanks much
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
January 4, 2012 at 12:27 pm
The other point that I would like to bring in is if i query on the database the DBCC USerOptions I get the isolation level as read commited snapshot but in the deadlock graph information i notice in the processid list that the isolation level=serializable; does that mean in these EF queries the isolation levels are hardcoded. if so why does the dbcc useroptions still return the isolation level as RCSI
Thanks
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
January 4, 2012 at 12:41 pm
DBCC USEROPTIONS gives you the default options. Connections can override that and the connections involved in the deadlocks is explicitly setting an isolation level of serialisable, overriding the default.
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
January 4, 2012 at 12:47 pm
ok. The problem is I cant modify the EF generated scripts since they are generated dynamically. I meant if these scripts are hardcoded with the isolation level as serializable and I want to exclude that from the scripts.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
January 4, 2012 at 1:20 pm
It won't be the scripts, it'll be in the connection properties or one of the data access classes. Speak to the .net developers on that.
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
January 4, 2012 at 1:28 pm
As for the deadlock, there's a select being run on the connection before that update. I need to see what that select does.
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
January 4, 2012 at 1:49 pm
The complete select is listed below (captured from the trace)
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
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
January 4, 2012 at 2:05 pm
The fact that the queries and updates are being run with isolation level of serializable is probably why you are getting deadlocks. There is rarely a good reason to use an isolation level of serializable, except for schema changes.
You need to investigate why the spplicaiton is setting that isolation level, and fix that.
January 4, 2012 at 2:11 pm
Michael Valentine Jones (1/4/2012)
You need to investigate why the spplicaiton is setting that isolation level, and fix that.
I believe ADO.NET (and by extension entitiy framework) does it by default.
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
January 4, 2012 at 2:12 pm
Can you post the definition of the SMSJob table and any indexes, constraints or triggers
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
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply