SQL Server Performance Forum – Threads Archive
dead lock
i got this dead lock info could someone help with readingit<br /><br /><br />Deadlock encountered …. Printing deadlock information<br /><br />Wait-for graph<br /><br />Node:1<br /><br />TAB: 9:77243330 [] CleanCnt:1 Mode: IX Flags: 0x0<br /><br />Grant List 0::<br /><br />Owner:0x6da93080 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:124 ECID:0<br /><br />SPID: 124 ECID: 0 Statement Type: DELETE Line #: 1<br /><br />Input Buf: Language Event: delete MyTable where Session_Id=2000204044795<br /><br />Grant List 2::<br />Grant List 2::<br />requested by<br /> ResType:LockOwner Stype:’OR’ Mode: X SPID:70 ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ />0x48BDD570) Value:0x66f97000 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />0/0)<br /><br />NOde2<br />KEY: 9:77243330:6 (ffffffffffff) CleanCnt:2 Mode: Range-S-U Flags: 0x0<br /><br />Wait list<br /><br />Owner:0x2c833760 Mode: Range-S-U Flg:0x0 Ref:1 Life:02000000 SPID:80 ECID:0<br />SPID: 80 ECID: 0 Statement Type: DELETE Line #: 1<br />Input Buf: Language Event: delete MyTable where Session_Id=2000204044814<br />Requested By: <br />ResType:LockOwner Stype:’OR’ Mode: Range-S-U SPID:124 ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />0x437D9570) Value:0x6da92160 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />0/0)<br />node3<br />KEY: 9:77243330:6 (ffffffffffff) CleanCnt:2 Mode: Range-S-U Flags: 0x0<br />Grant list 2<br /><br />Owner:0x6d4051e0 Mode: Range-S-U Flg:0x0 Ref:1 Life:02000000 SPID:70 ECID:0<br /><br />SPID: 70 ECID: 0 Statement Type: DELETE Line #: 14<br /><br />Input Buf: Language Event: execute sp_MYStoredProcedure’20030204112827718′,’2000204044774′,’73311,73281,73324,73293,73366,73314,73325,73378,73383,73270,73315,73365,73375,73368,73376,73295,73428,73274,73296,73381,73379,73363,73310,73433,73331,73341,73377,73326,73312,73271,7<br /><br />requested <br /><br />ResType:LockOwner Stype:’OR’ Mode: Range-S-U SPID:80 ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />0x41C7F570) Value:0x2c833760 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />0/0)<br /><br />Victim Resource Owner:<br /><br />ResType:LockOwner Stype:’OR’ Mode: Range-S-U SPID:80 ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />0x41C7F570) Value:0x2c833760 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />0/0)<br /><br /><br /><br />
I hope I can explain this well enough. I’m sure there are details that I missed, but I think I have the basic cause for the deadlock. SPID 124 has IX table lock on object 77243330 in db 9
SPID 70 requests X lock on object 77243330 in db 9
SPID 80 requests Range-S-U key lock on object 77243330 in db 9
SPID 70 request Range-S-U lock on object 77243330 in db 9 Basically, the process for SPID 70 (execute sp_insert_mei_avail_emp_list) has an exclusive lock on a resource. Then SPID 80 (delete MEI_Avail_Emp_List where Session_Id=2000204044814) requests a lock on a range of values in that resource. Before SPID 70 commits it’s transaction, it requests a lock on a range of values that overlap those of SPID 80. Because SPID 80 is waiting for the exclusive lock from SPID 70 and now SPID 70 is waiting for the lock from SPID 80, neither process will be able to finish and then . . . deadlock. HTH
Chris
thank you
]]>