SQL Server Deadlocks

You are an accidental/junior DBA. One of your developer says he's getting a lot of deadlock messages in the application log suddenly.  He is asking for the DBA's (Your) help.  What are you going to do.  This blog post will help you to handle this issue.

What is deadlock
Deadlock is a situation when two transactions have mutually locked out each other. 

For example, Lets take two transactions
 "TRAN-1",  it has two insert statements,  First insert on Table-A and Second Insert on Table-B
 "TRAN-2" ,  This also has two insert statements, First insert in on Table-B,  Second Insert is on Table-A.

When both of these Transactions execute at the same time,
Tran-1 will finish the insert on Table-A and wait for the lock to be released (acquired by Tran-2) on Table-B. The lock on Table-A will still be active because it is inside a transaction.
Tran-2 would have completed the insert on Table-B but would not have released the lock since the transaction is not commited yet, but will be waiting for the lock to be released for Table-A.

At this point,  The deadlock situation has encountered. 

SQL Server is smart enough to diagnose the deadlock situation and identify one of the processes as victim and kill it so that the other process can continue to execute.

How to analyze deadlock
Deadlock related information will be logged to the errorlog only if the related trace flags are turned on. Otherwise, you won't find any useful info from the errorlog.

The traceflags are 1204 and 1222(After SQL Server 2005).

How to identify if the trace flags are already turned on

DBCC TRACESTATUS command will return the trace flags that are currently turned on.

How to turn on the trace flag.

The following commands will turn the trace flags on.

DBCC TRACEON (1222, -1)
DBCC TRACEON (1204, -1)

What to look for in the error logs

Here is a sample error message from the log

2014-04-11 15:24:22.22 spid4s      Requested by:
2014-04-11 15:24:22.22 spid4s        ResType:LockOwner Stype:'OR'Xdes:0x00000004E9FD8BC0 Mode: U SPID:63 BatchID:0 ECID:0 TaskProxy:(0x00000004E9BBC608) Value:0xeb284540 Cost:(0/144)
2014-04-11 15:24:22.22 spid4s     
2014-04-11 15:24:22.22 spid4s      Victim Resource Owner:
2014-04-11 15:24:22.22 spid4s       ResType:LockOwner Stype:'OR'Xdes:0x00000004F08D6D28 Mode: U SPID:58 BatchID:0 ECID:0 TaskProxy:(0x00000004E97B8608) Value:0xf5b126c0 Cost:(0/144)
2014-04-11 15:24:22.22 spid18s     deadlock-list
2014-04-11 15:24:22.22 spid18s      deadlock victim=process4f9025498
2014-04-11 15:24:22.22 spid18s       process-list
2014-04-11 15:24:22.22 spid18s        process id=process4f9025498 taskpriority=0 logused=144 waitresource=RID: 8:1:301:0 waittime=5851 ownerId=32694 transactionname=user_transaction lasttranstarted=2014-04-11T15:24:16.370 XDES=0x4f08d6d28 lockMode=U schedulerid=3 kpid=3476 status=suspended spid=58 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2014-04-11T15:24:16.370 lastbatchcompleted=2014-04-11T15:24:07.050 lastattention=1900-01-01T00:00:00.050 clientapp=Microsoft SQL Server Management Studio - Query hostname=SIVA-PC hostpid=4024 loginname=Siva-PC\Siva isolationlevel=read committed (2) xactid=32694 currentdb=8 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2014-04-11 15:24:22.22 spid18s         executionStack
2014-04-11 15:24:22.22 spid18s          frame procname=adhoc line=4 stmtstart=16 sqlhandle=0x020000006306cb0282580a95a5146f4b2ce8d05ad05f852e0000000000000000000000000000000000000000
2014-04-11 15:24:22.22 spid18s     UPDATE [dbo].[DeadLockTest] set [col1] = @1    
2014-04-11 15:24:22.22 spid18s          frame procname=adhoc line=4 stmtstart=106 sqlhandle=0x020000000f8d9d36c56b96bdcb04670ca5b75bc17de9868c0000000000000000000000000000000000000000
2014-04-11 15:24:22.22 spid18s     UPDATE dbo.DeadLockTest SET col1 = 1    
2014-04-11 15:24:22.22 spid18s         inputbuf
2014-04-11 15:24:22.22 spid18s     BEGIN TRAN
2014-04-11 15:24:22.22 spid18s     UPDATE dbo.DeadLockTest2 SET col1 = 1
2014-04-11 15:24:22.22 spid18s     UPDATE dbo.DeadLockTest SET col1 = 1   
2014-04-11 15:24:22.22 spid18s        process id=process4f04fb0c8 taskpriority=0 logused=144 waitresource=RID: 8:1:303:0 waittime=650 ownerId=32286 transactionname=user_transaction lasttranstarted=2014-04-11T15:22:53.877 XDES=0x4e9fd8bc0 lockMode=U schedulerid=2 kpid=4148 status=suspended spid=63 sbid=0 ecid=0 priority=0 trancount=5 lastbatchstarted=2014-04-11T15:24:21.570 lastbatchcompleted=2014-04-11T15:24:12.090 lastattention=2014-04-11T15:23:49.210 clientapp=Microsoft SQL Server Management Studio - Query hostname=SIVA-PC hostpid=4024 loginname=Siva-PC\Siva isolationlevel=read committed (2) xactid=32286 currentdb=8 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2014-04-11 15:24:22.22 spid18s         executionStack
2014-04-11 15:24:22.22 spid18s          frame procname=adhoc line=1 stmtstart=16 sqlhandle=0x02000000ca356b202147fbe58aa2b109b537cf37cb3083430000000000000000000000000000000000000000
2014-04-11 15:24:22.22 spid18s     UPDATE [dbo].[DeadLockTest2] set [col1] = @1    
2014-04-11 15:24:22.22 spid18s          frame procname=adhoc line=1 sqlhandle=0x0200000087d432229b1acc5bc82908fa19f6f23bbc0e4e820000000000000000000000000000000000000000
2014-04-11 15:24:22.22 spid18s     UPDATE dbo.DeadLockTest2 SET col1 = 1    
2014-04-11 15:24:22.22 spid18s         inputbuf
2014-04-11 15:24:22.22 spid18s     UPDATE dbo.DeadLockTest2 SET col1 = 1   
2014-04-11 15:24:22.22 spid18s       resource-list
2014-04-11 15:24:22.22 spid18s        ridlock fileid=1 pageid=301 dbid=8 objectname=ReplB.dbo.DeadLockTest id=lock4edbe2480 mode=X associatedObjectId=72057594039828480
2014-04-11 15:24:22.22 spid18s         owner-list
2014-04-11 15:24:22.22 spid18s          owner id=process4f04fb0c8 mode=X
2014-04-11 15:24:22.22 spid18s         waiter-list
2014-04-11 15:24:22.22 spid18s          waiter id=process4f9025498 mode=U requestType=wait
2014-04-11 15:24:22.22 spid18s        ridlock fileid=1 pageid=303 dbid=8 objectname=ReplB.dbo.DeadLockTest2 id=lock4f5abe780 mode=X associatedObjectId=72057594039894016
2014-04-11 15:24:22.22 spid18s         owner-list
2014-04-11 15:24:22.22 spid18s          owner id=process4f9025498 mode=X
2014-04-11 15:24:22.22 spid18s         waiter-list
2014-04-11 15:24:22.22 spid18s          waiter id=process4f04fb0c8 mode=U requestType=wait


The above given information will be helpful in identifying the transactions involved, the logins involved, time of the deadlock etc.

You can start analysing the issue after gathering these informations.

How to turn off trace flags

Once you have obtained the necessary deadlock information from the log file, you can turn off the trace flags.

Command to turn off the traceflags

DBCC TRACEOFF (1204, -1)
DBCC TRACEOFF (1222, -1)

There is no need to restart the instance for turning these trace flags on/off.

Hope this helps.