Lock::Trace Flag 1204

원본출처: http://blogs.msdn.com/bartd/attachment/747119.ashx

 

Below you’ll see sample -T1204 output for a very simple deadlock between two spids, spid 51 and spid 52.   The deadlock is represented as a list of “nodes”.  Each node represents one resource – most commonly a lock – that is involved in the deadlock.  For each node, or lock, information is provided about both the spid that is blocked waiting for access to this resource, and about the spid is already has a lock on the resource that is blocking the first spid.  A simple deadlock involves two spids and two locked resources, but deadlocks involving 3 or more spids are also possible. 

 

The -T1204 output is annotated to explain the most relevant parts of the output.  If you are viewing this page in Internet Explorer, you may need to enable active content for the page before you can see the annotations.  Hover over the comment markers to see the annotations. 

 

spid4     Deadlock encountered .... Printing deadlock information

spid4    

spid4     Wait-for graph

spid4    

spid4     Node:1[BKD1]  

spid4     KEY: 7:2121058592:2 (a70064fb1eac)[BKD2]  CleanCnt:1 Mode: X[BKD3]  Flags: 0x0

spid4      Grant List 0::

spid4        Owner:0x42bdefa0 Mode: X[BKD4]          Flg:0x0 Ref:0 Life:02000000 SPID:52 ECID:0

spid4        SPID: 52[BKD5]   ECID: 0 Statement Type: DELETE[BKD6]   Line #: 6[BKD7]  

spid4        Input Buf: Language Event:

       EXEC spClearItemStatus 152363[BKD8]    

spid4      Requested By:

spid4        ResType:LockOwner Stype:'OR' Mode: U SPID:51[BKD9]   ECID:0 Ec:(0x43F5F588) Value:0x42bded20 Cost:(0/10AC)

spid4    

spid4     Node:2[BKD10]  

spid4     KEY: 7:1977058079:1 (02014f0bec4e)[BKD11]  CleanCnt:1 Mode: X Flags: 0x0

spid4      Grant List 0::

spid4        Owner:0x42bde9a0 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:51 ECID:0

spid4        SPID: 51 ECID: 0 Statement Type: UPDATE Line #: 47

spid4        Input Buf: Language Event:

       spUpdateItemProp 152363, ' QTY', 3525 

spid4      Requested By:

spid4        ResType:LockOwner Stype:'OR' Mode: X SPID:52 ECID:0 Ec:(0x43983588) Value:0x42bdee40 Cost:(0/54)

spid4     Victim Resource Owner:

spid4      ResType:LockOwner Stype:'OR' Mode: X SPID:52[BKD12]   ECID:0 Ec:(0x43983588) Value:0x42bdee40 Cost:(0/54)

By using the info highlighted in the first node above, and the same items from the second node, you can reconstruct the following more readable description of the deadlock scenario:

 

Spid 52 is running a DELETE statement on line 6 of the stored proc spClearItemStatus.  He holds an X lock on the key resource KEY: 7:2121058592:2 (a70064fb1eac).  This lock is blocking spid 51, who is waiting to acquire a U lock on the same key.  

 

Spid 51 is running an UPDATE statement on line 47 of the stored proc spUpdateItemProp.  He holds an X lock on key KEY: 7:1977058079:1 (02014f0bec4e).  His X lock is blocking spid 52, who is waiting to acquire an X lock on the same key. 

 

So, spid 51 is blocked by 52, while spid 52 is blocked by 51.  This is a circular blocking chain, which is another name for a deadlock. 

 

It would be better to identify the key lock using a more meaningful table name and index name instead of using cryptic resource IDs like “KEY: 7:2121058592:2 (a70064fb1eac)”.  It is not possible to figure out what tables and indexes are involved from -T1204 output alone because we don’t have the necessary data to decode these lock resource IDs.  For example, the resource “KEY: 7:2121058592:2 (a70064fb1eac)” corresponds to a particular index key in nonclustered index 2, on a table with object ID 2121058592, in the database with dbid 7.  You can figure out what table and index this is by looking at sysobjects and sysindexes for the relevant database.  After decoding the lock resource IDs and identifying the specific queries involved, the description of the first node might look like this: