Getting Deadlock details using Traces

Deadlock is a circular locking among two or more processes that is automatically identified by SQL Server and resolved by killing one process. Whenever our process is killed because of deadlock we get the following message:

“Msg 1205, Level 13, State 51, Line 4

Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

”

Here we not know the details of deadlock like blocking resource, queries of our process and other processes, etc. To get these details we can use SQL Server Profiler. But profiler is a bit complex in configuration and consumes resources heavily that may significantly degrade the performance of server.

We have another method that is easy in implementation and very light weighted. On the server execute the below statement:

DBCC TRACEON (1204,3605, -1)

That’s all to track and log the complete details of deadlock in any of database on that server. Now whenever a deadlock will occur details will be logged in the SQL Server error log file located in Log folder (for me this is at C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Log”). The details would be as below:

Deadlock encountered .... Printing deadlock information

Wait-for graph

Node:1

KEY: 7:72057594048413696 (0300146b1a34) CleanCnt:2 Mode:X Flags: 0x1

       Grant List 1:

         Owner:0x040E8740 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:54 ECID:0 XactLockInfo: 0x043BCC38

         SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 3

         Input Buf: Language Event: BEGIN TRAN

                UPDATE dbo.TestDeadlock WITH (ROWLOCK) SET Name = 'Mahesh' WHERE ID = 2

                UPDATE dbo.TestDeadlock WITH (ROWLOCK) SET Name = 'Tarun' WHERE ID = 1

COMMIT TRAN

      Requested by:

        ResType:LockOwner Stype:'OR'Xdes:0x0428F9F8 Mode: X SPID:53 BatchID:0 ECID:0 TaskProxy:(0x0491C354) Value:0x40e8cc0 Cost:(0/0)

     

      Node:2

      KEY: 7:72057594048413696 (0200c411ba73) CleanCnt:2 Mode:X Flags: 0x1

       Grant List 0:

         Owner:0x040E8D00 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:53 ECID:0 XactLockInfo: 0x0428FA20

         SPID: 53 ECID: 0 Statement Type: UPDATE Line #: 4

         Input Buf: Language Event: BEGIN TRAN

                UPDATE dbo.TestDeadlock WITH (ROWLOCK) SET Name = 'Rachit' WHERE ID = 1

                WAITFOR DELAY '00:00:10'

                UPDATE dbo.TestDeadlock WITH (ROWLOCK) SET Name = 'Karan' WHERE ID = 2

COMMIT TRAN

      Requested by:

        ResType:LockOwner Stype:'OR'Xdes:0x043BCC10 Mode: X SPID:54 BatchID:0 ECID:0 TaskProxy:(0x0495A354) Value:0x40ea500 Cost:(0/256)

     

      Victim Resource Owner:

       ResType:LockOwner Stype:'OR'Xdes:0x0428F9F8 Mode: X SPID:53 BatchID:0 ECID:0 TaskProxy:(0x0491C354) Value:0x40e8cc0 Cost:(0/0)

I extract useful information from above details as below:

Deadlock was between two nodes

Node: 1

SPID: 54

Blocking Resource: Key (clustered index row)

Mode of lock: X (exclusive)

T-SQL Batch that caused blocking:

BEGIN TRAN

                UPDATE dbo.TestDeadlock WITH (ROWLOCK) SET Name = 'Mahesh' WHERE ID = 2

                UPDATE dbo.TestDeadlock WITH (ROWLOCK) SET Name = 'Tarun' WHERE ID = 1

COMMIT TRAN

Node: 2

SPID: 53

Blocking Resource: Key (clustered index row)

Mode of lock: X (exclusive)

T-SQL Batch that caused blocking:

BEGIN TRAN

                UPDATE dbo.TestDeadlock WITH (ROWLOCK) SET Name = 'Rachit' WHERE ID = 1

                WAITFOR DELAY '00:00:10'

                UPDATE dbo.TestDeadlock WITH (ROWLOCK) SET Name = 'Karan' WHERE ID = 2

COMMIT TRAN

Because of this deadlock SPID 53 was killed and SPID 54 completed successfully.

To test this scenario execute the below script in SSMS:

 

DBCC TRACEON (1204,3605, -1)

GO

CREATE TABLE dbo.TestDeadlock(ID INT IDENTITY, Name VARCHAR(25))

GO

CREATE CLUSTERED INDEX ci_ID ON dbo.TestDeadlock (ID) 

GO

INSERT INTO dbo.TestDeadlock VALUES('Ajay')

INSERT INTO dbo.TestDeadlock VALUES('Vijay')

BEGIN TRAN

UPDATE dbo.TestDeadlock WITH (ROWLOCK) SET Name = 'Rachit' WHERE ID = 1

WAITFOR DELAY '00:00:10'

UPDATE dbo.TestDeadlock WITH (ROWLOCK) SET Name = 'Karan' WHERE ID = 2

COMMIT TRAN

Now start another session (by clicking on New Query button) and execute the below query within 10 seconds:

BEGIN TRAN

UPDATE dbo.TestDeadlock WITH (ROWLOCK) SET Name = 'Mahesh' WHERE ID = 2

UPDATE dbo.TestDeadlock WITH (ROWLOCK) SET Name = 'Tarun' WHERE ID = 1

COMMIT TRAN

One of these sessions would fail with deadlock error. Now open the SQL Server ERRORLOG file. You would get the deadlock details that I mentioned above.