SQL Server Performance

2 connection holding X and U lock !!!

Discussion in 'T-SQL Performance Tuning for Developers' started by jasonlel, Apr 14, 2005.

  1. jasonlel New Member

    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 />
  2. gaurav_bindlish New Member

    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.
  3. satya Moderator

  4. jasonlel New Member

    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
  5. gaurav_bindlish New Member

    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
  6. jasonlel New Member

    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

Share This Page