SQL Server Performance Forum – Threads Archive
2 connection holding X and U lock !!!
Hi guys,<br /><br />I had a weird deadlock issue. See text in red. 1 connection hold U lock and another hold X lock at the same time???? Pls advice….<br /><br /><br /><br />2005-04-12 11:53:55.11 spid4 ResType:LockOwner Stype:’OR’ Mode: U <font color="blue">SPID:69</font id="blue"> ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ />0x1CC67550) Value:0x42b<br />2005-04-12 11:53:55.11 spid4 Victim Resource Owner:<br />2005-04-12 11:53:55.11 spid4 ResType:LockOwner Stype:’OR’ Mode: U <font color="blue">SPID:53 </font id="blue"> ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />0x5666951<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Value:0x42b<br />2005-04-12 11:53:55.11 spid4 Requested By: <br />2005-04-12 11:53:55.11 spid4 Input Buf: RPC Event: objSNRGetNextSN;1<br />2005-04-12 11:53:55.11 spid4 <font color="blue">SPID: 69 </font id="blue"> ECID: 0 Statement Type: INSERT Line #: 122<br />2005-04-12 11:53:55.11 spid4 Owner:0x64c058a0 Mode: U Flg:0x0 Ref:0 Life:02000000 <font color="blue">SPID:69 </font id="blue"> ECID:0<br />2005-04-12 11:53:55.11 spid4 Grant List 0::<br />2005-04-12 11:53:55.11 spid4 <font color="red">KEY: 10:1890105774:1 (1800935f164b) CleanCnt:1 Mode: U </font id="red"> Flags: 0x0<br />2005-04-12 11:53:55.11 spid4 Node:2<br />2005-04-12 11:53:55.11 spid4 <br />2005-04-12 11:53:55.11 spid4 ResType:LockOwner Stype:’OR’ Mode: U <font color="blue">SPID:69 </font id="blue"> ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />0x1CC67550) Value:0x42b<br />2005-04-12 11:53:55.11 spid4 Requested By: <br />2005-04-12 11:53:55.11 spid4 Input Buf: RPC Event: objTSKGetUnitInfo;1<br />2005-04-12 11:53:55.11 spid4 <font color="blue">SPID: 53 </font id="blue"> ECID: 0 Statement Type: INSERT Line #: 122<br />2005-04-12 11:53:55.11 spid4 Owner:0x42bdb6a0 Mode: X Flg:0x0 Ref:0 Life:02000000 <font color="blue">SPID:53 </font id="blue"> ECID:0<br />2005-04-12 11:53:55.11 spid4 Grant List 2::<br />2005-04-12 11:53:55.11 spid4 <font color="red">KEY: 10:1890105774:1 (1900f638aaf3) CleanCnt:1 Mode: X</font id="red"> Flags: 0x0<br />2005-04-12 11:53:55.11 spid4 Node:1<br />2005-04-12 11:53:55.11 spid4 <br />2005-04-12 11:53:55.11 spid4 Wait-for graph<br />
Aren’t these the same spid? SPID 4? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
http://www.sql-server-performance.com/deadlocks.asp
http://www.sql-server-performance.com/lock_contention_tamed_article.asp
http://www.sql-server-performance.com/sf_block_prevention.asp
http://www.sql-server-performance.com/deadlocks.asp
.. .with related information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
gaurav_bindlish, Yea same SPID. Is there any issue on that? My concern is, how come there are 2 connections, 1 hold X and 1 hold U at the same time? I thought this is not possible at all…. Cheers,
Jason X
If there is one SPId, that means both the locks are being help by the same connection. I am not sure if even one connection can hold two kinds of locks on the same resource. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
http://blogs.msdn.com/gauravbi/default.aspx
gaurav_bindlish, I just update my first posting again. Notice the text color in blue. I think that’s the actual connection ID. SPID:69 and SPID:53 .Meaning is 2 connection Cheers,
Jason X
]]>