Home Forums SQL Server 2008 SQL Server Newbies SQL Deadlocks RE: SQL Deadlocks

  • January 23, 2013 at 2:34 am

    #1579960

    anthony.green (1/23/2013)

    Do you have the deadlock XML in the error log, should be many many lines that looks like this

    <TextData><deadlock-list>

    <deadlock victim="process53b9288">

    <process-list>

    <process id="process53b9288" taskpriority="0" logused="476" waitresource="KEY: 6:72057594136035328 (ac00e9dcc382)" waittime="3386" ownerId="26173739408" transactionname="user_transaction" lasttranstarted="2013-01-22T22:47:20.203" XDES="0x11a64d970" lockMode="S" schedulerid="8" kpid="15240" status="suspended" spid="119" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-01-22T22:47:20.207" lastbatchcompleted="2013-01-22T22:47:20.207" clientapp="EntityFramework" hostname="App1" hostpid="8824" loginname="Ant" isolationlevel="read committed (2)" xactid="26173739408" currentdb="1" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">

    Hi Anthony, I don't have any deadlock XML in my error log but i do have the information above just broken up into lines.

    01/23/2013 07:25:31,spid23s,Unknown,waiter id=process27317b4c8 mode=X requestType=wait

    01/23/2013 07:25:31,spid23s,Unknown,waiter-list

    01/23/2013 07:25:31,spid23s,Unknown,owner id=processdb54c8 mode=S

    01/23/2013 07:25:31,spid23s,Unknown,owner-list

    01/23/2013 07:25:31,spid23s,Unknown,keylock hobtid=72057605790367744 dbid=4 objectname=dbo.order indexname=IX_status id=lock3d60c8e80 mode=S associatedObjectId=72057605790367744

    01/23/2013 07:25:31,spid23s,Unknown,waiter id=processdb54c8 mode=S requestType=wait

    01/23/2013 07:25:31,spid23s,Unknown,waiter-list

    01/23/2013 07:25:31,spid23s,Unknown,owner id=process27317b4c8 mode=X

    01/23/2013 07:25:31,spid23s,Unknown,owner-list

    01/23/2013 07:25:31,spid23s,Unknown,keylock hobtid=72057602492792832 dbid=4 objectname=dbo.order indexname=PK_order id=lock3d27aec80 mode=X associatedObjectId=72057602492792832

    01/23/2013 07:25:31,spid23s,Unknown,resource-list

    01/23/2013 07:25:31,spid23s,Unknown,update order set shipped=1<c/>status=3<c/>shipdate=getdate() where sessionid='125693' and order=1

    01/23/2013 07:25:31,spid23s,Unknown,inputbuf

    01/23/2013 07:25:31,spid23s,Unknown,update order set shipped=1<c/>status=3<c/>shipdate=getdate() where sessionid='125693' and order=1

    01/23/2013 07:25:31,spid23s,Unknown,frame procname=adhoc line=1 sqlhandle=0x02000000880e2612bdc1c85889b28ed1a83337436c23418e

    01/23/2013 07:25:31,spid23s,Unknown,(@1 int<c/>@2 int<c/>@3 varchar(8000)<c/>@4 tinyint)UPDATE [order] set [shipped] = @1<c/>[status] = @2<c/>[shipdate] = getdate() WHERE [sessionid]%%=%%@3 AND [order]%%=%%@4

    01/23/2013 07:25:31,spid23s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000006d90e92510cd4c7f9a9af4c3c7d68bb4cd95914a

    01/23/2013 07:25:31,spid23s,Unknown,executionStack

    01/23/2013 07:25:31,spid23s,Unknown,process id=process27317b4c8 taskpriority=0 logused=292 waitresource=KEY: 9:72057605790367744 (2e001d1b37ce) waittime=1840 ownerId=18323630766 transactionname=UPDATE lasttranstarted=2013-01-23T07:25:29.427 XDES=0xe4e2b970 lockMode=X schedulerid=8 kpid=2092 status=suspended spid=59 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-01-23T07:25:29.420 lastbatchcompleted=2013-01-23T07:25:29.420 clientapp=PHP 5 hostpid=227446 isolationlevel=read committed (2) xactid=18323630766 currentdb=4 lockTimeout=4294967295

    01/23/2013 07:25:31,spid23s,Unknown,inputbuf

    01/23/2013 07:25:31,spid23s,Unknown,SELECT count(rsn) As Count FROM order WHERE status<3 AND shipopt <99 AND dateadded <= '2013-01-23 13:00:59'

    01/23/2013 07:25:31,spid23s,Unknown,frame procname=adhoc line=1 sqlhandle=0x02000000712b952eb475b9b249be45c1513acca8b2b31ed1

    01/23/2013 07:25:31,spid23s,Unknown,(@1 tinyint<c/>@2 tinyint<c/>@3 tinyint<c/>@4 varchar(8000))SELECT COUNT([rsn]) [Count] FROM [order] WHERE [status]<@1 AND [shipopt]<@3 AND [dateadded]<=@4

    01/23/2013 07:25:31,spid23s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000007c853201340a85cf70825b9c171de622351e36b1

    01/23/2013 07:25:31,spid23s,Unknown,executionStack

    01/23/2013 07:25:31,spid23s,Unknown,process id=processdb54c8 taskpriority=0 logused=0 waitresource=KEY: 9:72057602492792832 (2c00849cfb3c) waittime=1840 ownerId=18323630729 transactionname=SELECT lasttranstarted=2013-01-23T07:25:29.423 XDES=0x1c384fb80 lockMode=S schedulerid=7 kpid=8128 status=suspended spid=51 sbid=0 ecid=4 priority=0 trancount=0 lastbatchstarted=2013-01-23T07:25:29.423 lastbatchcompleted=2013-01-23T07:25:29.280 clientapp=PHP 5 hostpid=227446 isolationlevel=read committed (2) xactid=18323630729 currentdb=4 lockTimeout=4294967295

    01/23/2013 07:25:31,spid23s,Unknown,process-list

    01/23/2013 07:25:31,spid23s,Unknown,deadlock victim=processdb54c8

    01/23/2013 07:25:31,spid23s,Unknown,deadlock-list

    01/23/2013 07:25:31,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000001C384FB80 Mode: S SPID:51 BatchID:0 ECID:4 TaskProxy:(0x000000035B1E3770) Value:0xce952bc0 Cost:(0/0)

    01/23/2013 07:25:31,spid5s,Unknown,Victim Resource Owner:

    01/23/2013 07:25:31,spid5s,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:

    01/23/2013 07:25:31,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000000E4E2B970 Mode: X SPID:59 BatchID:0 ECID:0 TaskProxy:(0x000000024AD44538) Value:0xbd6264c0 Cost:(0/292)

    01/23/2013 07:25:31,spid5s,Unknown,Requested by:

    01/23/2013 07:25:31,spid5s,Unknown,Input Buf: No Event:

    01/23/2013 07:25:31,spid5s,Unknown,SPID: 51 ECID: 4 Statement Type: SELECT Line #: 1

    01/23/2013 07:25:31,spid5s,Unknown,Owner:0x00000001BF286480 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:51 ECID:4 XactLockInfo: 0x00000001C384FBC0

    01/23/2013 07:25:31,spid5s,Unknown,Grant List 2:

    01/23/2013 07:25:31,spid5s,Unknown,KEY: 9:72057605790367744 (2e001d1b37ce) CleanCnt:2 Mode:S Flags: 0x1

    01/23/2013 07:25:31,spid5s,Unknown,Node:2

    01/23/2013 07:25:31,spid5s,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:

    01/23/2013 07:25:31,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000001C384FB80 Mode: S SPID:51 BatchID:0 ECID:4 TaskProxy:(0x000000035B1E3770) Value:0xce952bc0 Cost:(0/0)

    01/23/2013 07:25:31,spid5s,Unknown,Requested by:

    01/23/2013 07:25:31,spid5s,Unknown,Input Buf: Language Event: update order set shipped=1<c/>status=3<c/>shipdate=getdate() where sessionid='125693' and order=1

    01/23/2013 07:25:31,spid5s,Unknown,SPID: 59 ECID: 0 Statement Type: UPDATE Line #: 1

    01/23/2013 07:25:31,spid5s,Unknown,Owner:0x000000008164B2C0 Mode: X Flg:0x40 Ref:1 Life:02000000 SPID:59 ECID:0 XactLockInfo: 0x00000000E4E2B9B0

    01/23/2013 07:25:31,spid5s,Unknown,Grant List 1:

    01/23/2013 07:25:31,spid5s,Unknown,KEY: 9:72057602492792832 (2c00849cfb3c) CleanCnt:2 Mode:X Flags: 0x1

    01/23/2013 07:25:31,spid5s,Unknown,Node:1

    01/23/2013 07:25:31,spid5s,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:

    01/23/2013 07:25:31,spid5s,Unknown,Wait-for graph

    01/23/2013 07:25:31,spid5s,Unknown,Deadlock encountered .... Printing deadlock information