SQL Server Performance

Dead lock troubleshooting.

Discussion in 'General DBA Questions' started by california6, Jan 9, 2008.

  1. california6 New Member

    I have enabled dbcc traceon(1204) on my production SQL Box and see the following dead lock error messages dumped to sql log. Unfortunately the dead lock output attached is confusing to me and unable to determine, which SPID was dead lock. Can somebody please take a look at it and suggest?
    Dead lock Error:
    2008-01-09 10:47:56.36 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:62 ECID:0 Ec:(0x252C3588) Value:0x6bb
    2008-01-09 10:47:56.36 spid4 Victim Resource Owner:
    2008-01-09 10:47:56.36 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:62 ECID:0 Ec:(0x252C3588) Value:0x6bb
    2008-01-09 10:47:56.36 spid4 Requested By:
    2008-01-09 10:47:56.36 spid4 Input Buf: Language Event: BEGIN TRANSACTION; Execute Ins2OPERATIONS N'ALLOC_C_
    2008-01-09 10:47:56.36 spid4 SPID: 57 ECID: 0 Statement Type: INSERT Line #: 19
    2008-01-09 10:47:56.36 spid4 Owner:0x47292c80 Mode: IX Flg:0x0 Ref:15 Life:02000000 SPID:57 ECID:0
    2008-01-09 10:47:56.36 spid4 Grant List 3::
    2008-01-09 10:47:56.36 spid4 PAG: 10:1:133446 CleanCnt:2 Mode: IX Flags: 0x2
    2008-01-09 10:47:56.36 spid4 Node:2
    2008-01-09 10:47:56.36 spid4
    2008-01-09 10:47:56.36 spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:57 ECID:0 Ec:(0x6F953588) Value:0x69
    2008-01-09 10:47:56.36 spid4 Requested By:
    2008-01-09 10:47:56.36 spid4 Input Buf: Language Event: select ACCOUNT_OPERATIONS,BUSORG,BUSPARTNER,COSTCENT
    2008-01-09 10:47:56.36 spid4 SPID: 62 ECID: 0 Statement Type: INSERT Line #: 5
    2008-01-09 10:47:56.36 spid4 Owner:0x3b05fe60 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:62 ECID:0
    2008-01-09 10:47:56.36 spid4 Grant List 0::
    2008-01-09 10:47:56.36 spid4 PAG: 10:1:158185 CleanCnt:2 Mode: S Flags: 0x2
    2008-01-09 10:47:56.36 spid4 Node:1
    2008-01-09 10:47:56.36 spid4
    2008-01-09 10:47:56.36 spid4 Wait-for graph
    2008-01-09 10:47:56.36 spid4
    2008-01-09 10:47:56.36 spid4 ...
    If I have to read this error log, do i read from bottom to top?
    Many thanks,
    Cali
  2. MohammedU New Member

    I believe SPID 62 blocked by SPID 57...
    Check BOL index "Trace Flag 1204 Report" for details...
  3. california6 New Member

    Thanks for the answer MohammedU. When i looked at the error output, i see SPID#62 Mode was “S” (Assuming it’s a shared lock) and SPID#57 lock was “IX” (Intent Exclusive). My understanding is, If It’s an “S” lock, then it should not block any other processes? Isnt that true?Looked at BOL and don’t find anything about how to read the output. I was wondering, if I have to read it, shall I read from bottom to top?
    Thanks again,
  4. satya Moderator

  5. california6 New Member

    Gone through the URL's and they are very helpful.
    Thanks a million Satya
  6. MohammedU New Member

    <P mce_keep="true">From BOL:</P><H1><A class="" name=_lock_compatibility></A>Lock Compatibility</H1><P>Only compatible lock types can be placed on a resource that is already locked. For example, while an exclusive (X) lock is held, no other transaction can acquire a lock of any kind (shared, update, or exclusive) on that resource until the exclusive (X) lock is released at the end of the first transaction. Alternatively, if a shared (S) lock has been applied to a resource, other transactions can also acquire a shared lock or an update (U) lock on that item, even if the first transaction has not completed. However, other transactions cannot acquire an exclusive lock until the shared lock has been released.</P><P>Resource lock modes have a compatibility matrix that shows which locks are compatible with other locks obtained on the same resource (listed in increasing lock strength).</P><P mce_keep="true"><TABLE class="" cols=7 cellPadding=2 rules=all width=587 border=1 frame=box><TBODY><TR vAlign=top><TH class=label width="28%" class="label">&nbsp;</TH><TH class=label width="72%" colSpan=6 class="label">Existing granted mode</TH></TR><TR vAlign=top><TH class=label width="28%" class="label">Requested mode</TH><TH class=label width="12%" class="label">IS</TH><TH class=label width="13%" class="label">S</TH><TH class=label width="12%" class="label">U</TH><TH class=label width="12%" class="label">IX</TH><TH class=label width="11%" class="label">SIX</TH><TH class=label width="12%" class="label">X</TH></TR><TR vAlign=top><TD class="" width="28%"><B>Intent shared (IS)</B></TD><TD class="" width="12%">Yes</TD><TD class="" width="13%">Yes</TD><TD class="" width="12%">Yes</TD><TD class="" width="12%">Yes</TD><TD class="" width="11%">Yes</TD><TD class="" width="12%">No</TD></TR><TR vAlign=top><TD class="" width="28%"><B>Shared (S)</B></TD><TD class="" width="12%">Yes</TD><TD class="" width="13%">Yes</TD><TD class="" width="12%">Yes</TD><TD class="" width="12%">No</TD><TD class="" width="11%">No</TD><TD class="" width="12%">No</TD></TR><TR vAlign=top><TD class="" width="28%"><B>Update (U)</B></TD><TD class="" width="12%">Yes</TD><TD class="" width="13%">Yes</TD><TD class="" width="12%">No</TD><TD class="" width="12%">No</TD><TD class="" width="11%">No</TD><TD class="" width="12%">No</TD></TR><TR vAlign=top><TD class="" width="28%"><B>Intent exclusive (IX)</B></TD><TD class="" width="12%">Yes</TD><TD class="" width="13%">No</TD><TD class="" width="12%">No</TD><TD class="" width="12%">Yes</TD><TD class="" width="11%">No</TD><TD class="" width="12%">No</TD></TR><TR vAlign=top><TD class="" width="28%"><B>Shared with intent exclusive (SIX)</B></TD><TD class="" width="12%">Yes</TD><TD class="" width="13%">No</TD><TD class="" width="12%">No</TD><TD class="" width="12%">No</TD><TD class="" width="11%">No</TD><TD class="" width="12%">No</TD></TR><TR vAlign=top><TD class="" width="28%"><B>Exclusive (X)</B></TD><TD class="" width="12%">No</TD><TD class="" width="13%">No</TD><TD class="" width="12%">No</TD><TD class="" width="12%">No</TD><TD class="" width="11%">No</TD><TD class="" width="12%">No</TD></TR></TBODY></TABLE><BR></P><P class=indent><!--NOTE--></P><P mce_keep="true"><IMG style="MARGIN-LEFT: 0em" height=11 alt="" src="mk:mad:MSITStore:C:program%20FilesMicrosoft%20SQL%20Server80ToolsBooksacdata.chm::/Basics/note.gif" width=12 border=0> </P><P style="MARGIN-TOP: -1.1em; MARGIN-LEFT: 1.5em"><B>Note</B>&nbsp;&nbsp;An intent exclusive (IX) lock is compatible with an IX lock mode because IX means the intention to update only some of the rows rather than all of them. Other transactions that want to read or update some of the rows are also permitted providing they are not the same rows being updated by other transactions.</P><!--/NOTE--><P mce_keep="true">&nbsp;</P><P>The schema stability (Sch-S) lock is compatible with all lock modes except the schema modification (Sch-M) lock mode.</P><P>The schema modification (Sch-M) lock is incompatible with all lock modes.</P><P>The bulk update (BU) lock is compatible only with schema stability (Sch-S) and other bulk update (BU) locks.</P><!--END-->
  7. california6 New Member

    Satya and MohammedU,
    Thanks for the answers. I have gone through the URL's provided by Satya and noticed one thing:
    The URL dead lock output reads like this:
    Wait-for Graph
    Node:1
    ResType:LockOwner ................
    When i looked at my dead lock error log, here's what i see:
    2008-01-09 10:47:56.36 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:62 ECID:0 Ec:(0x252C3588) Value:0x6bb
    2008-01-09 10:47:56.36 spid4 Victim Resource Owner:
    2008-01-09 10:47:56.36 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:62 ECID:0 Ec:(0x252C3588) Value:0x6bb
    2008-01-09 10:47:56.36 spid4 Requested By:
    2008-01-09 10:47:56.36 spid4 Input Buf: Language Event: BEGIN TRANSACTION; Execute Ins2OPERATIONS N'ALLOC_C_
    2008-01-09 10:47:56.36 spid4 SPID: 57 ECID: 0 Statement Type: INSERT Line #: 19
    2008-01-09 10:47:56.36 spid4 Owner:0x47292c80 Mode: IX Flg:0x0 Ref:15 Life:02000000 SPID:57 ECID:0
    2008-01-09 10:47:56.36 spid4 Grant List 3::
    2008-01-09 10:47:56.36 spid4 PAG: 10:1:133446 CleanCnt:2 Mode: IX Flags: 0x2
    2008-01-09 10:47:56.36 spid4 Node:2
    2008-01-09 10:47:56.36 spid4
    2008-01-09 10:47:56.36 spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:57 ECID:0 Ec:(0x6F953588) Value:0x69
    2008-01-09 10:47:56.36 spid4 Requested By:
    2008-01-09 10:47:56.36 spid4 Input Buf: Language Event: select ACCOUNT_OPERATIONS,BUSORG,BUSPARTNER,COSTCENT
    2008-01-09 10:47:56.36 spid4 SPID: 62 ECID: 0 Statement Type: INSERT Line #: 5
    2008-01-09 10:47:56.36 spid4 Owner:0x3b05fe60 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:62 ECID:0
    2008-01-09 10:47:56.36 spid4 Grant List 0::
    2008-01-09 10:47:56.36 spid4 PAG: 10:1:158185 CleanCnt:2 Mode: S Flags: 0x2
    2008-01-09 10:47:56.36 spid4 Node:1
    2008-01-09 10:47:56.36 spid4
    2008-01-09 10:47:56.36 spid4 Wait-for graph
    2008-01-09 10:47:56.36 spid4
    My question is, why do is see Node:2 listed at top of my error log and not Node:1 ? The URL's KB's are all listed with Node:1 and proceed to Node:2. Why this difference in my environment?
    Appreciated any input.
    Thanks,
    Cali
  8. satya Moderator

    Probability that process is initiated on node2 and to proceed on node 1, main cause is the code and you have to identify that to fix it. Follow the URLs and reduce such blocking.

Share This Page