Tracing a SQL Server Deadlock

Deadlock information can be captured in the SQL Server Error Log or by using Profiler / Server Side Trace. If you want to capture this information in the SQL Server Error Log you need to enable one or both of these trace flags.
1.  1204 - this provides information about the nodes involved in the deadlock
2.  1222 - returns deadlock information in an XML format


You can turn on each of these separately or turn them on together.

To turn these on you can issue the following commands in a query window or you can add these as startup parameters.  If these are turned on from a query window, the next time SQL Server starts these trace flags will not be active, so if you always want to capture this data the startup parameters is the best option.


Deadlocking occurs when two or more SQL Server processes have locks on separate database objects and each process is trying to acquire a lock on an object that the other processes have previously locked.  For example, process one has an exclusive lock on object one, process two has an exclusive lock on object two, and process one also wants an exclusive lock on object two, and object two wants an exclusive lock on object one. Because two processes can’t have an exclusive lock on the same object at the same time, the two processes become entangled in a deadlock, with neither process willing to yield of its own accord.

Since a deadlock is not a good thing for an application, SQL Server is smart enough to identify the problem and ends the deadlock by choosing one process over another. It does this by killing one of the processes (usually the process that has used the least amount of server resources up to this point) and lets the other one to continue to run. The aborted transaction is rolled back and an error message is sent to the application. If the application is deadlock aware, it will resubmit the killed transaction automatically and the user may never know the deadlock happened. If the application is not deadlock aware, then most likely an error message appears on the application’s screen and you get a call from a disgruntled user. Besides irritating users, deadlocks can use up SQL Server's resources unnecessarily as transactions are killed, rolled back, and resubmitted again.


-- Activate deadlock tracing
dbcc traceon (1204, 3605, -1)
go
dbcc tracestatus(-1)
go

-- Deadlock simulation - CONNECTION (session) 1
USE AdventureWorks;
WHILE ( 1< 2)
BEGIN
      BEGIN TRANSACTION
            UPDATE Sales.SalesOrderHeader SET ModifiedDate=ModifiedDate
            UPDATE Purchasing.PurchaseOrderHeader SET ModifiedDate=ModifiedDate
      COMMIT TRANSACTION
END

-- Deadlock simulation - CONNECTION (session) 2
USE AdventureWorks;
WHILE ( 1< 2)
BEGIN
      BEGIN TRANSACTION
            UPDATE Purchasing.PurchaseOrderHeader SET ModifiedDate=ModifiedDate
            UPDATE Sales.SalesOrderHeader SET ModifiedDate=ModifiedDate
      COMMIT TRANSACTION
END
/* Messages

(4000 row(s) affected)
Msg 1205, Level 13, State 56, Line 7
Transaction (Process ID 60) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.

*/

/* SQL Server ERROR LOG
2019-03-02 05:44:58.59 spid55      DBCC TRACEON 1204, server process ID (SPID) 55. This is an informational message only; no user action is required.
2019-03-02 05:44:58.59 spid55      DBCC TRACEON 3605, server process ID (SPID) 55. This is an informational message only; no user action is required.
2019-03-02 05:45:48.09 spid4s      Deadlock encountered .... Printing deadlock information
2019-03-02 05:45:48.09 spid4s      Wait-for graph
2019-03-02 05:45:48.09 spid4s  
2019-03-02 05:45:48.09 spid4s      Node:1

2019-03-02 05:45:48.12 spid4s      OBJECT: 6:402100473:0          CleanCnt:3 Mode:X Flags: 0x0
2019-03-02 05:45:48.12 spid4s       Grant List 1:
2019-03-02 05:45:48.12 spid4s         Owner:0x10195FA0 Mode: X        Flg:0x0 Ref:3 Life:02000000 SPID:62 ECID:0 XactLockInfo: 0x2A4CCD0C
2019-03-02 05:45:48.12 spid4s         SPID: 62 ECID: 0 Statement Type: UPDATE Line #: 7
2019-03-02 05:45:48.12 spid4s         Input Buf: Language Event: -- Deadlock simulation - CONNECTION (session) 2
USE AdventureWorks;
WHILE ( 1< 2)
BEGIN
      BEGIN TRANSACTION
            UPDATE Purchasing.PurchaseOrderHeader SET ModifiedDate=ModifiedDate
            UPDATE Sales.SalesOrderHeader SET ModifiedDate=ModifiedDate
      COMMIT T
2019-03-02 05:45:48.12 spid4s       Requested By:
2019-03-02 05:45:48.12 spid4s         ResType:LockOwner Stype:'OR'Xdes:0x2A4CC250 Mode: IX SPID:55 BatchID:0 ECID:0 TaskProxy:(0x189CA378) Value:0x16bb9420 Cost:(0/6371044)
2019-03-02 05:45:48.12 spid4s  
2019-03-02 05:45:48.12 spid4s      Node:2

2019-03-02 05:45:48.12 spid4s      OBJECT: 6:722101613:0          CleanCnt:2 Mode:X Flags: 0x0
2019-03-02 05:45:48.12 spid4s       Grant List 1:
2019-03-02 05:45:48.12 spid4s         Owner:0x101A9940 Mode: X        Flg:0x0 Ref:14 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x2A4CC274
2019-03-02 05:45:48.12 spid4s         SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 6
2019-03-02 05:45:48.12 spid4s         Input Buf: Language Event: USE AdventureWorks;
WHILE ( 1< 2)
BEGIN
      BEGIN TRANSACTION
            UPDATE Sales.SalesOrderHeader SET ModifiedDate=ModifiedDate
            UPDATE Purchasing.PurchaseOrderHeader SET ModifiedDate=ModifiedDate
      COMMIT TRANSACTION
END
2019-03-02 05:45:48.12 spid4s       Requested By:
2019-03-02 05:45:48.12 spid4s         ResType:LockOwner Stype:'OR'Xdes:0x2A4CCCE8 Mode: IX SPID:62 BatchID:0 ECID:0 TaskProxy:(0x19F2A378) Value:0x1019ba20 Cost:(0/800164)
2019-03-02 05:45:48.12 spid4s  
2019-03-02 05:45:48.12 spid4s      Victim Resource Owner:
2019-03-02 05:45:48.12 spid4s       ResType:LockOwner Stype:'OR'Xdes:0x2A4CCCE8 Mode: IX SPID:62 BatchID:0 ECID:0 TaskProxy:(0x19F2A378) Value:0x1019ba20 Cost:(0/800164)

*/

-- Turn off deadlock tracing
dbcc traceoff (1204, 3605)
go