April 6, 2009 at 12:48 pm
I am having little hardtime reading and understanding this deadlock print out. I would appreciate, if someone please take a moment to take a look at it and answer: One more thing: The MS kb/832524 arcitle gives an eg of node 1 and Node 2 list. but in my output i see like Node 1-2 and 3. Dont know, how to interpet this and figure out, where the problem lies.
Any suggestions would be greatly apprecaited.
Output from SQL Server error logs after enabling 1204 and 2605 trace flags:
------------------------------------------------------------------------
Date,Source,Severity,Message
04/06/2009 11:38:14,spid10s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x0000000607EB3110 Mode: U SPID:198 BatchID:0 ECID:2 TaskProxy:(0x00000002D41DD1A0) Value:0x8130adc0 Cost:(0/0)
04/06/2009 11:38:14,spid10s,Unknown,Victim Resource Owner:
04/06/2009 11:38:14,spid10s,Unknown,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:
04/06/2009 11:38:14,spid10s,Unknown,Producer: Xid Slot: 2 e_waitNone
04/06/2009 11:38:14,spid10s,Unknown,Producer List::
04/06/2009 11:38:14,spid10s,Unknown,Consumer: Xid Slot: 0 e_waitPipeGetRow
04/06/2009 11:38:14,spid10s,Unknown,Consumer List::
04/06/2009 11:38:14,spid10s,Unknown,Coordinator: Task = 0x000000000AF2FAC8 e_waitPipeGetRow
04/06/2009 11:38:14,spid10s,Unknown,Port: 0x000000008042F030 Xid Slot: 0 Exchange Wait Type :e_waitPipeGetRow
04/06/2009 11:38:14,spid10s,Unknown,Node:3
04/06/2009 11:38:14,spid10s,Unknown,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:
04/06/2009 11:38:14,spid10s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x0000000290EC9270 Mode: IU SPID:169 BatchID:0 ECID:0 TaskProxy:(0x00000000E76C4598) Value:0x5a12c300 Cost:(0/1000)
04/06/2009 11:38:14,spid10s,Unknown,Requested By:
04/06/2009 11:38:14,spid10s,Unknown,Input Buf: Language Event: DELETE FROM [dbo].[sgDataOPERATIONS] WHERE [LCKNUMBER] = 236653 AND [CellVal] IN(1 4)
04/06/2009 11:38:14,spid10s,Unknown,SPID: 198 ECID: 0 Statement Type: DELETE Line #: 1
04/06/2009 11:38:14,spid10s,Unknown,Owner:0x000000038A44E140 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:198 ECID:0 XactLockInfo: 0x0000000151793A08
04/06/2009 11:38:14,spid10s,Unknown,Grant List 3:
04/06/2009 11:38:14,spid10s,Unknown,PAGE: 9:3:7338952 CleanCnt:2 Mode:U Flags: 0x2
04/06/2009 11:38:14,spid10s,Unknown,Node:2
04/06/2009 11:38:14,spid10s,Unknown,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:
04/06/2009 11:38:14,spid10s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x0000000607EB3110 Mode: U SPID:198 BatchID:0 ECID:2 TaskProxy:(0x00000002D41DD1A0) Value:0x8130adc0 Cost:(0/0)
04/06/2009 11:38:14,spid10s,Unknown,Requested By:
04/06/2009 11:38:14,spid10s,Unknown,Input Buf: Language Event: DELETE [dbo].[sgDataOPERATIONS] WHERE [LCKNUMBER] IN (236649)
04/06/2009 11:38:14,spid10s,Unknown,SPID: 169 ECID: 0 Statement Type: DELETE Line #: 1
04/06/2009 11:38:14,spid10s,Unknown,Owner:0x000000065AA1DCC0 Mode: IX Flg:0x0 Ref:0 Life:02000000 SPID:169 ECID:0 XactLockInfo: 0x0000000290EC92A8
04/06/2009 11:38:14,spid10s,Unknown,Grant List 2:
04/06/2009 11:38:14,spid10s,Unknown,PAGE: 9:3:5504039 CleanCnt:3 Mode:IX Flags: 0x2
04/06/2009 11:38:14,spid10s,Unknown,Node:1
04/06/2009 11:38:14,spid10s,Unknown,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:
04/06/2009 11:38:14,spid10s,Unknown,Wait-for graph
04/06/2009 11:38:14,spid10s,Unknown,Deadlock encountered .... Printing deadlock information
04/06/2009 11:35:14,spid10s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000002479E8B30 Mode: U SPID:289 BatchID:0 ECID:2 TaskProxy:(0x00000007088AF170) Value:0xa234380 Cost:(0/0)
04/06/2009 11:35:14,spid10s,Unknown,Victim Resource Owner:
04/06/2009 11:35:14,spid10s,Unknown,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:
04/06/2009 11:35:14,spid10s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000002479E8B30 Mode: U SPID:289 BatchID:0 ECID:2 TaskProxy:(0x00000007088AF170) Value:0xa234380 Cost:(0/0)
04/06/2009 11:35:14,spid10s,Unknown,Requested By:
04/06/2009 11:35:14,spid10s,Unknown,Input Buf: Language Event: DELETE [dbo].[sgDataOPERATIONS] WHERE [LCKNUMBER] IN (236597)
04/06/2009 11:35:14,spid10s,Unknown,SPID: 366 ECID: 0 Statement Type: DELETE Line #: 1
04/06/2009 11:35:14,spid10s,Unknown,Owner:0x000000031A50E7C0 Mode: IX Flg:0x0 Ref:0 Life:02000000 SPID:366 ECID:0 XactLockInfo: 0x00000003ACA8C3A8
04/06/2009 11:35:14,spid10s,Unknown,Grant List 0:
04/06/2009 11:35:14,spid10s,Unknown,PAGE: 9:1:3239310 CleanCnt:2 Mode:IX Flags: 0x2
April 6, 2009 at 1:15 pm
Is there anything in the Windows event log?
I had one recently - the eventlog said something like deadlock query sp_xxx selected for termination
April 6, 2009 at 1:20 pm
1204 puts out a hard to use deadlock graph. If you can, can you recapture it using traceflag 1222? That one is much cleaner and easier to read.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 6, 2009 at 1:21 pm
It usually helps me to have a profiler trace to look at as well.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
April 6, 2009 at 1:31 pm
All three queries are going after sgDataOPERATIONS. Is there a read statement prior to the delete statements that would cause lock escalation? What do the execution plans look like on those queries? Are they long running, using indexes appropriately?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 6, 2009 at 3:25 pm
Thanks all for contribution.
I have enabled the 1222 trace flag and waiting to see deadlock error. will post if i see anything.
Thanks again,
April 6, 2009 at 4:39 pm
OK - here's what i got after enabling trace flag 1204 - 1222. I still do not understand where to read this deadlock information.
Apprecaited some assistance here:
Thanks,
--------------------------------------------------
04/06/2009 15:25:37,spid23s,Unknown,waiter event=e_waitPipeGetRow type=consumer id=process98785c8
04/06/2009 15:25:37,spid23s,Unknown,waiter-list
04/06/2009 15:25:37,spid23s,Unknown,owner event=e_waitNone type=producer id=processaf2eb08
04/06/2009 15:25:37,spid23s,Unknown,owner-list
04/06/2009 15:25:37,spid23s,Unknown,exchangeEvent id=port8042ed70 nodeId=2
04/06/2009 15:25:37,spid23s,Unknown,waiter id=processaf2eb08 mode=U requestType=wait
04/06/2009 15:25:37,spid23s,Unknown,waiter-list
04/06/2009 15:25:37,spid23s,Unknown,owner id=processfa96d8 mode=IX
04/06/2009 15:25:37,spid23s,Unknown,owner-list
04/06/2009 15:25:37,spid23s,Unknown,pagelock fileid=1 pageid=880556 dbid=9 objectname=Flex.dbo.sgDataOperations id=lock294b47c00 mode=IX associatedObjectId=72057611623268352
04/06/2009 15:25:37,spid23s,Unknown,waiter id=processfa96d8 mode=IU requestType=wait
04/06/2009 15:25:37,spid23s,Unknown,waiter-list
04/06/2009 15:25:37,spid23s,Unknown,owner id=process98785c8 mode=U
04/06/2009 15:25:37,spid23s,Unknown,owner-list
04/06/2009 15:25:37,spid23s,Unknown,pagelock fileid=3 pageid=5494249 dbid=9 objectname=Flex.dbo.sgDataOperations id=lock23de3e480 mode=U associatedObjectId=72057611623268352
04/06/2009 15:25:37,spid23s,Unknown,resource-list
04/06/2009 15:25:37,spid23s,Unknown,inputbuf
04/06/2009 15:25:37,spid23s,Unknown,DELETE FROM [dbo].[sgDataOPERATIONS] WHERE [LCKNUMBER] = 237578 AND [CellVal] IN(1 4)
04/06/2009 15:25:37,spid23s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000006f7bbc088e5a02620980bb8a8fc943855ba22d5a
04/06/2009 15:25:37,spid23s,Unknown,executionStack
04/06/2009 15:25:37,spid23s,Unknown,process id=processaf2eb08 taskpriority=0 logused=0 waitresource=PAGE: 9:1:880556 waittime=4531 ownerId=646787247 transactionname=DELETE lasttranstarted=2009-04-06T15:25:33.293 XDES=0x10b310c80 lockMode=U schedulerid=8 kpid=5724 status=suspended spid=379 sbid=0 ecid=2 priority=0 transcount=0 lastbatchstarted=2009-04-06T15:25:33.293 lastbatchcompleted=2009-04-06T15:25:33.293 clientapp=Microsoft® Windows® Operating System hostname=SACNT239 hostpid=7208 isolationlevel=read committed (2) xactid=646787247 currentdb=9 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
04/06/2009 15:25:37,spid23s,Unknown,DELETE FROM [dbo].[sgDataOPERATIONS] WHERE [LCKNUMBER] = 237578 AND [CellVal] IN(1 4)
04/06/2009 15:25:37,spid23s,Unknown,inputbuf
04/06/2009 15:25:37,spid23s,Unknown,DELETE FROM [dbo].[sgDataOPERATIONS] WHERE [LCKNUMBER] = 237578 AND [CellVal] IN(1 4)
04/06/2009 15:25:37,spid23s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000006f7bbc088e5a02620980bb8a8fc943855ba22d5a
04/06/2009 15:25:37,spid23s,Unknown,executionStack
04/06/2009 15:25:37,spid23s,Unknown,process id=process98785c8 taskpriority=0 logused=20002 waittime=4234 schedulerid=5 kpid=5000 status=suspended spid=379 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-04-06T15:25:33.293 lastbatchcompleted=2009-04-06T15:25:33.293 lastattention=2009-04-06T15:25:28.967 clientapp=Microsoft® Windows® Operating System hostname=SACNT239 hostpid=7208 loginname=AMERICAS\sjc_osoft isolationlevel=read committed (2) xactid=646787247 currentdb=9 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
04/06/2009 15:25:37,spid23s,Unknown,DELETE [dbo].[sgDataOPERATIONS] WHERE [LCKNUMBER] IN (237577)
04/06/2009 15:25:37,spid23s,Unknown,inputbuf
04/06/2009 15:25:37,spid23s,Unknown,DELETE [dbo].[sgDataOPERATIONS] WHERE [LCKNUMBER] IN (237577)
04/06/2009 15:25:37,spid23s,Unknown,frame procname=adhoc line=1 sqlhandle=0x0200000064e01029f00486d72240973f7c1bfbe60c53d6a8
04/06/2009 15:25:37,spid23s,Unknown,DELETE [dbo].[sgDataOPERATIONS] WHERE [LCKNUMBER]=@1
04/06/2009 15:25:37,spid23s,Unknown,frame procname=adhoc line=1 stmtstart=16 sqlhandle=0x02000000c4387e3105e44a9353008cac4725281658683f49
04/06/2009 15:25:37,spid23s,Unknown,executionStack
04/06/2009 15:25:37,spid23s,Unknown,process id=processfa96d8 taskpriority=0 logused=32528 waitresource=PAGE: 9:3:5494249 waittime=4453 ownerId=646784508 transactionname=DELETE lasttranstarted=2009-04-06T15:25:28.560 XDES=0x1c1ddae20 lockMode=IU schedulerid=1 kpid=5772 status=suspended spid=399 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-04-06T15:25:28.560 lastbatchcompleted=2009-04-06T15:25:28.543 clientapp=.Net SqlClient Data Provider hostname=SACNT239 hostpid=0 loginname=AMERICAS\sjc_osoft isolationlevel=read committed (2) xactid=646784508 currentdb=9 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
04/06/2009 15:25:37,spid23s,Unknown,process-list
04/06/2009 15:25:37,spid23s,Unknown,deadlock victim=processaf2eb08
04/06/2009 15:25:37,spid23s,Unknown,deadlock-list
04/06/2009 15:25:37,spid10s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x000000010B310C80 Mode: U SPID:379 BatchID:0 ECID:2 TaskProxy:(0x000000035EF5B8B0) Value:0xbba6b40 Cost:(0/0)
04/06/2009 15:25:37,spid10s,Unknown,Victim Resource Owner:
04/06/2009 15:25:37,spid10s,Unknown,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:
04/06/2009 15:25:37,spid10s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x000000010B310C80 Mode: U SPID:379 BatchID:0 ECID:2 TaskProxy:(0x000000035EF5B8B0) Value:0xbba6b40 Cost:(0/0)
04/06/2009 15:25:37,spid10s,Unknown,Requested By:
04/06/2009 15:25:37,spid10s,Unknown,Input Buf: Language Event: DELETE [dbo].[sgDataOPERATIONS] WHERE [LCKNUMBER] IN (237577)
04/06/2009 15:25:37,spid10s,Unknown,SPID: 399 ECID: 0 Statement Type: DELETE Line #: 1
04/06/2009 15:25:37,spid10s,Unknown,Owner:0x00000005A3394700 Mode: IX Flg:0x0 Ref:0 Life:02000000 SPID:399 ECID:0 XactLockInfo: 0x00000001C1DDAE58
04/06/2009 15:25:37,spid10s,Unknown,Grant List 0:
04/06/2009 15:25:37,spid10s,Unknown,PAGE: 9:1:880556 CleanCnt:2 Mode:IX Flags: 0x2
04/06/2009 15:25:37,spid10s,Unknown,Node:3
04/06/2009 15:25:37,spid10s,Unknown,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:
04/06/2009 15:25:37,spid10s,Unknown,Node:2
04/06/2009 15:25:37,spid10s,Unknown,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:
04/06/2009 15:25:37,spid10s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000001C1DDAE20 Mode: IU SPID:399 BatchID:0 ECID:0 TaskProxy:(0x000000056DD52598) Value:0x8745bd40 Cost:(0/32528)
04/06/2009 15:25:37,spid10s,Unknown,Requested By:
04/06/2009 15:25:37,spid10s,Unknown,Input Buf: Language Event: DELETE FROM [dbo].[sgDataOPERATIONS] WHERE [LCKNUMBER] = 237578 AND [CellVal] IN(1 4)
04/06/2009 15:25:37,spid10s,Unknown,SPID: 379 ECID: 0 Statement Type: DELETE Line #: 1
04/06/2009 15:25:37,spid10s,Unknown,Owner:0x000000010A01B580 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:379 ECID:0 XactLockInfo: 0x000000036A15E548
04/06/2009 15:25:37,spid10s,Unknown,Grant List 2:
04/06/2009 15:25:37,spid10s,Unknown,PAGE: 9:3:5494249 CleanCnt:3 Mode:U Flags: 0x2
04/06/2009 15:25:37,spid10s,Unknown,Node:1
04/06/2009 15:25:37,spid10s,Unknown,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:
04/06/2009 15:25:37,spid10s,Unknown,Wait-for graph
04/06/2009 15:25:37,spid10s,Unknown,Deadlock encountered .... Printing deadlock information
04/06/2009 15:25:32,spid20s,Unknown,waiter event=e_waitPortOpen type=producer id=processaf28ef8
04/06/2009 15:25:32,spid20s,Unknown,waiter-list
04/06/2009 15:25:32,spid20s,Unknown,owner event=e_waitNone type=producer id=processaf2eb08
04/06/2009 15:25:32,spid20s,Unknown,owner-list
04/06/2009 15:25:32,spid20s,Unknown,exchangeEvent id=port8042ed70 nodeId=4
04/06/2009 15:25:32,spid20s,Unknown,waiter id=processaf2eb08 mode=U requestType=wait
04/06/2009 15:25:32,spid20s,Unknown,waiter-list
04/06/2009 15:25:32,spid20s,Unknown,owner id=processfa96d8 mode=IX
04/06/2009 15:25:32,spid20s,Unknown,owner-list
04/06/2009 15:25:32,spid20s,Unknown,pagelock fileid=1 pageid=880556 dbid=9 objectname=Flex.dbo.sgDataOperations id=lock294b47c00 mode=IX associatedObjectId=72057611623268352
04/06/2009 15:25:32,spid20s,Unknown,waiter id=processfa96d8 mode=IU requestType=wait
04/06/2009 15:25:32,spid20s,Unknown,waiter-list
04/06/2009 15:25:32,spid20s,Unknown,owner id=processaf28ef8 mode=U
04/06/2009 15:25:32,spid20s,Unknown,owner-list
04/06/2009 15:25:32,spid20s,Unknown,pagelock fileid=3 pageid=5488000 dbid=9 objectname=Flex.dbo.sgDataOperations id=lock1faddfa00 mode=U associatedObjectId=72057611623268352
04/06/2009 15:25:32,spid20s,Unknown,resource-list
04/06/2009 15:25:32,spid20s,Unknown,inputbuf
04/06/2009 15:25:32,spid20s,Unknown,EXECUTE [dbo].[up_IsAvailableSendOPERATIONS] N'AMERICAS\sjcdflac' N'DM
04/06/2009 15:25:32,spid20s,Unknown,frame procname=adhoc line=1 stmtstart=310 stmtend=472 sqlhandle=0x020000007570f128ae280379d25f69eb98d339287c68a688
04/06/2009 15:25:32,spid20s,Unknown,EXEC (@Sql1)
04/06/2009 15:25:32,spid20s,Unknown,frame procname=Flex.dbo.up_IsAvailableSendOperations line=1 stmtstart=1976 stmtend=2006 sqlhandle=0x0300090019a6de3a8ec7fb00e39b00000100000000000000
04/06/2009 15:25:32,spid20s,Unknown,AND [sgDataOperations].[LckNumber] =237578
04/06/2009 15:25:32,spid20s,Unknown,AND D.[StatusCode]=C.[Status]
04/06/2009 15:25:32,spid20s,Unknown,WHERE D.[BusOrg] = [sgDataOperations].[BusOrg] AND D.[Category] = [sgDataOperations].[Category] AND D.[Time] = [sgDataOperations].[TimeID]
04/06/2009 15:25:32,spid20s,Unknown,) D [dbo].[tblStatusCode] C
04/06/2009 15:25:32,spid20s,Unknown,INNER JOIN [dbo].[tblOperationsLock] B ON A.[BusOrg] = B.[BusOrg] AND A.[Category] = B.[Category] AND A.[Time] = B.[Time] AND A.[LastUpdate] = B.[LastUpdate]
04/06/2009 15:25:32,spid20s,Unknown,(SELECT [BusOrg] [Time]) A
04/06/2009 15:25:32,spid20s,Unknown,FROM
04/06/2009 15:25:32,spid20s,Unknown,(SELECT B.[StatusCode] A.[Time]
04/06/2009 15:25:32,spid20s,Unknown,FROM
04/06/2009 15:25:32,spid20s,Unknown,SET [CellVal] = C.[DM]
04/06/2009 15:25:32,spid20s,Unknown,UPDATE [dbo].[sgDataOperations]
04/06/2009 15:25:32,spid20s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000004ba80c3a5d390567ca147f443197db832ac8b5d7
04/06/2009 15:25:32,spid20s,Unknown,executionStack
04/06/2009 15:25:32,spid20s,Unknown,process id=processaf2eb08 taskpriority=0 logused=0 waitresource=PAGE: 9:1:880556 waittime=3671 ownerId=646785849 transactionname=UPDATE lasttranstarted=2009-04-06T15:25:29.153 XDES=0x10b310c80 lockMode=U schedulerid=8 kpid=2108 status=suspended spid=287 sbid=0 ecid=1 priority=0 transcount=0 lastbatchstarted=2009-04-06T15:25:29.043 lastbatchcompleted=2009-04-06T15:25:29.043 clientapp=.Net SqlClient Data Provider hostname=SACNT239 hostpid=0 isolationlevel=read committed (2) xactid=646785849 currentdb=9 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
04/06/2009 15:25:32,spid20s,Unknown,inputbuf
04/06/2009 15:25:32,spid20s,Unknown,EXECUTE [dbo].[up_IsAvailableSendOPERATIONS] N'AMERICAS\sjcdflac' N'DM
04/06/2009 15:25:32,spid20s,Unknown,frame procname=adhoc line=1 stmtstart=310 stmtend=472 sqlhandle=0x020000007570f128ae280379d25f69eb98d339287c68a688
04/06/2009 15:25:32,spid20s,Unknown,EXEC (@Sql1)
04/06/2009 15:25:32,spid20s,Unknown,frame procname=Flex.dbo.up_IsAvailableSendOperations line=1 stmtstart=1976 stmtend=2006 sqlhandle=0x0300090019a6de3a8ec7fb00e39b00000100000000000000
04/06/2009 15:25:32,spid20s,Unknown,AND [sgDataOperations].[LckNumber] =237578
04/06/2009 15:25:32,spid20s,Unknown,AND D.[StatusCode]=C.[Status]
04/06/2009 15:25:32,spid20s,Unknown,WHERE D.[BusOrg] = [sgDataOperations].[BusOrg] AND D.[Category] = [sgDataOperations].[Category] AND D.[Time] = [sgDataOperations].[TimeID]
04/06/2009 15:25:32,spid20s,Unknown,) D [dbo].[tblStatusCode] C
04/06/2009 15:25:32,spid20s,Unknown,INNER JOIN [dbo].[tblOperationsLock] B ON A.[BusOrg] = B.[BusOrg] AND A.[Category] = B.[Category] AND A.[Time] = B.[Time] AND A.[LastUpdate] = B.[LastUpdate]
04/06/2009 15:25:32,spid20s,Unknown,(SELECT [BusOrg] [Time]) A
04/06/2009 15:25:32,spid20s,Unknown,FROM
04/06/2009 15:25:32,spid20s,Unknown,(SELECT B.[StatusCode] A.[Time]
04/06/2009 15:25:32,spid20s,Unknown,FROM
04/06/2009 15:25:32,spid20s,Unknown,SET [CellVal] = C.[DM]
04/06/2009 15:25:32,spid20s,Unknown,UPDATE [dbo].[sgDataOperations]
04/06/2009 15:25:32,spid20s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000004ba80c3a5d390567ca147f443197db832ac8b5d7
04/06/2009 15:25:32,spid20s,Unknown,executionStack
04/06/2009 15:25:32,spid20s,Unknown,process id=processaf28ef8 taskpriority=0 logused=20004 waittime=3359 schedulerid=7 kpid=6116 status=suspended spid=287 sbid=0 ecid=2 priority=0 transcount=0 lastbatchstarted=2009-04-06T15:25:29.043 lastbatchcompleted=2009-04-06T15:25:29.043 clientapp=.Net SqlClient Data Provider hostname=SACNT239 hostpid=0 isolationlevel=read committed (2) xactid=646785849 currentdb=9 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
04/06/2009 15:25:32,spid20s,Unknown,DELETE [dbo].[sgDataOPERATIONS] WHERE [LCKNUMBER] IN (237577)
04/06/2009 15:25:32,spid20s,Unknown,inputbuf
04/06/2009 15:25:32,spid20s,Unknown,DELETE [dbo].[sgDataOPERATIONS] WHERE [LCKNUMBER] IN (237577)
04/06/2009 15:25:32,spid20s,Unknown,frame procname=adhoc line=1 sqlhandle=0x0200000064e01029f00486d72240973f7c1bfbe60c53d6a8
04/06/2009 15:25:32,spid20s,Unknown,DELETE [dbo].[sgDataOPERATIONS] WHERE [LCKNUMBER]=@1
04/06/2009 15:25:32,spid20s,Unknown,frame procname=adhoc line=1 stmtstart=16 sqlhandle=0x02000000c4387e3105e44a9353008cac4725281658683f49
04/06/2009 15:25:32,spid20s,Unknown,executionStack
04/06/2009 15:25:32,spid20s,Unknown,process id=processfa96d8 taskpriority=0 logused=32528 waitresource=PAGE: 9:3:5488000 waittime=3625 ownerId=646784508 transactionname=DELETE lasttranstarted=2009-04-06T15:25:28.560 XDES=0x1c1ddae20 lockMode=IU schedulerid=1 kpid=5772 status=suspended spid=399 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-04-06T15:25:28.560 lastbatchcompleted=2009-04-06T15:25:28.543 clientapp=.Net SqlClient Data Provider hostname=SACNT239 hostpid=0 loginname=AMERICAS\sjc_osoft isolationlevel=read committed (2) xactid=646784508 currentdb=9 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
04/06/2009 15:25:32,spid20s,Unknown,process-list
04/06/2009 15:25:32,spid20s,Unknown,deadlock victim=processaf2eb08
04/06/2009 15:25:32,spid20s,Unknown,deadlock-list
04/06/2009 15:25:32,spid10s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x000000010B310C80 Mode: U SPID:287 BatchID:0 ECID:1 TaskProxy:(0x00000002AF55F110) Value:0x9cafa00 Cost:(0/0)
04/06/2009 15:25:32,spid10s,Unknown,Victim Resource Owner:
04/06/2009 15:25:32,spid10s,Unknown,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:
04/06/2009 15:25:32,spid10s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x000000010B310C80 Mode: U SPID:287 BatchID:0 ECID:1 TaskProxy:(0x00000002AF55F110) Value:0x9cafa00 Cost:(0/0)
04/06/2009 15:25:32,spid10s,Unknown,Requested By:
04/06/2009 15:25:32,spid10s,Unknown,Input Buf: Language Event: DELETE [dbo].[sgDataOPERATIONS] WHERE [LCKNUMBER] IN (237577)
04/06/2009 15:25:32,spid10s,Unknown,SPID: 399 ECID: 0 Statement Type: DELETE Line #: 1
04/06/2009 15:25:32,spid10s,Unknown,Owner:0x00000005A3394700 Mode: IX Flg:0x0 Ref:0 Life:02000000 SPID:399 ECID:0 XactLockInfo: 0x00000001C1DDAE58
04/06/2009 15:25:32,spid10s,Unknown,Grant List 0:
04/06/2009 15:25:32,spid10s,Unknown,PAGE: 9:1:880556 CleanCnt:2 Mode:IX Flags: 0x2
04/06/2009 15:25:32,spid10s,Unknown,Node:3
04/06/2009 15:25:32,spid10s,Unknown,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:
04/06/2009 15:25:32,spid10s,Unknown,Node:2
04/06/2009 15:25:32,spid10s,Unknown,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:
04/06/2009 15:25:32,spid10s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000001C1DDAE20 Mode: IU SPID:399 BatchID:0 ECID:0 TaskProxy:(0x000000056DD52598) Value:0x91c5280 Cost:(0/32528)
04/06/2009 15:25:32,spid10s,Unknown,Requested By:
04/06/2009 15:25:32,spid10s,Unknown,Input Buf: No Event:
04/06/2009 15:25:32,spid10s,Unknown,SPID: 287 ECID: 2 Statement Type: UPDATE Line #: 1
04/06/2009 15:25:32,spid10s,Unknown,Owner:0x000000010C1FE740 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:287 ECID:2 XactLockInfo: 0x000000036A15E548
04/06/2009 15:25:32,spid10s,Unknown,Grant List 3:
04/06/2009 15:25:32,spid10s,Unknown,PAGE: 9:3:5488000 CleanCnt:3 Mode:U Flags: 0x2
04/06/2009 15:25:32,spid10s,Unknown,Node:1
04/06/2009 15:25:32,spid10s,Unknown,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:
04/06/2009 15:25:32,spid10s,Unknown,Wait-for graph
04/06/2009 15:25:32,spid10s,Unknown,Deadlock encountered .... Printing deadlock information
April 6, 2009 at 5:09 pm
Here is my understanding:-
1. There are 3 delete statements on the same table.
2. One has a UPDATE Lock on the page and the other 2 have a INTENT-EXCLUSIVE locks on the same page resource.
My solution:-
1. Using profiler, DMVs - find if indexes are missing ? and try to reduce the execution time for your delete queries.
2. If the delete operation deletes large number of rows then change the logic to delete few at a time.
OR
Specify ROWLOCK while deleting ( ps: this should be the last resort )
April 6, 2009 at 6:36 pm
First, turn off 1204 when you're collecting the info with 1222. It'll cut down on the noise and make it a bit easier to read.
Second. You're getting lock escalation as the cause. So, one or several of these queries is probably reading first then attempting to delete. Do you have an IF EXISTS or something like that ahead of the delete clause in these procs? You may need to run the SELECT with an UPDATE_LOCK query hint to lock the data it reads prior to attempting to udpate it. This will slow down your system. So, better to try to eliminate the check, assuming there is one. Usually instead of a check, I'll do the update or delete or whatever and then check for row count to see if it affected data. If not, raise a flag, or error, or what have you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 6, 2009 at 6:41 pm
Thanks so much for the contribution. I will implement the proposed solution. in-case i see this happening again, will come back to bug you guys again.
Thanks again,
April 6, 2009 at 6:45 pm
True whatever Grant advised.
But my two cents:
Its a deadlock between SPID 287 wich has Update lock and SPID 399 which has IX lock and SPID 287 has been chosen as a deadlock victim.
DBCC INPUTBUFFER(SPID)- identify the queries, fire it up onto DTA (for the moment) see what it advises, see if you have any improvement if not you could create your own indexes. And also please turn off 1204 its a bit misleading. Any chances just post with 1222 on, no needed though just to be safe side.
April 6, 2009 at 6:59 pm
It is possible that checking the queries and getting a good index in place may help as well. I'm personally not a fan of the DTA, but it can help. Generally I'd just go after the queries, check the execution plans, see what you can do to tune them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 7, 2009 at 4:15 pm
Grant you are the best. Krishna thanks for your contribution as well.
The application i am runing is a vendor specific and i have to go back to the vendor to make changes. but the information what i have should make things easier.
Thanks again for all your help.
April 7, 2009 at 4:24 pm
Welcome, after all its a learning experience but next time just put 1222 on turn off 1204 its a bit misleading
thanks,
April 7, 2009 at 7:11 pm
Thanks. Good luck with the vendor.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply