Interpreting Trace Flag 1204 Output

 

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:

 

Spid 52 is running a DELETE statement on line 6 of the stored proc spClearItemStatus.  He holds an X lock on a key within nonclustered index [ncidx1] on table [tbl_ItemList].  This lock is blocking spid 52, who is waiting to acquire a U lock on the same key. 

 

 


[BD5] [BD7][BD8] [BD10]

[BD11][BD12]


 [BKD1] [BD1]The first “node” in the deadlock.  Each node corresponds to a locked resource, and includes details about both the blocked and the blocking spid.

 [BKD2]One of the two lock resources involved in this deadlock.

 [BKD3]The highest lock mode granted lock on this resource.  In most cases this can be ignored.

 [BKD4] [BD4]The key lock owner’s lock mode.

 [BKD5]Spid 52 is causing the blocking in this node. Note “Grant List” on the preceding line – this spid has already been granted the lock successfully.

 [BKD6] [BD6]The type of command spid 52 is currently running.

 [BKD7]The line number for the start of the currently executing query. This lets you identify the specific statements involved in the deadlock if either spid’s inputbuffer is a stored procedure.

 [BKD8]Spid 52’s current inputbuffer.

 

 [BKD9] [BD9]Spid 51 is waiting for an Update lock on this KEY.  He is blocked by the eXclusive lock already granted to spid 52.

 [BKD10]The second node, representing another locked resource.  The relationships of the two spids have been reversed.  In node 1, spid 52 was blocking spid 51.  In this node, spid 51 is blocking spid 52.

 [BKD11]The second lock resource involved in the deadlock – another KEY lock on a different table.

 [BKD12]Spid 52 was chosen as the deadlock victim.  He will receive a 1205 error and have his transaction rolled back.