Dead Locks in SQL Server

 What are Dead Locks

• Dead Lock is a situation where, Two transaction which have already acquired locks on a resource. Try to acquire lock on each other resource.

Or

• Deadlock is what happens when two transactions need multiple resources to execute, and where some of the resources are locked by each of the transaction. This leads to the fact that A can't execute without something B has and vice versa.

• SQL Server have a dedicated process called "Background Dead Lock Monitor", which keeps an eye on dead lock situation. and chooses and kills one or more processes involved in a dead lock. Which are called dead lock victims.
• A process which is cheapest to roll back is choosen as dead lock victim. 

Example
 
--Connection 1
-- Session ID 53
--Step 1

Create Table Demo1
(
    ID Int
);

Create Table Demo2
(
    ID Int
);


--Step 2

BEGIN TRAN
Insert Into Demo1 values(1);

--Step 4
Select * from Demo2

-- Connection 2 (i.e. New Query Window) 
-- Session ID=54
 
--Step 3 (This will lead to blocking)

BEGIN TRAN
Insert Into Demo2 values(1);

--Step 5 (This will lead to a dead lock)

Select * from Demo1

Now, Dead lock monitor will choose one transaction as victim and roll it back. in out case Connection 1

How to detect Dead Locks

Dead Locks can be detected in variety of ways.

• Using Trace Flags 1204(Older Versions of SQL Server) and 1222 (2005 or above)
• Which writes dead lock info to SQL Server Logs. 
• Using trace flag in Startup Options via SQL Server Configuration Manager.

 

•  Using DBCC Command
• Enabling Trace for current session
• DBCC TraceON(1222)
• DBCC TraceStatus 
• Enabling Trace Globally
• DBCC TraceON(1222,-1)
• DBCC TraceStatus

• 

• Using Extended Events
• Which generates deadlock graph.
• System health sessions track dead locks.

•  Using Performonce Counter: 
• DeadLocks/Sec

• Using SQL Server Profiler events

 

 Dead Lock Resolution

• By Default SQL Server chooses to rollback least expensive process. This process is called Deadlock Victim.
• We can however change this behaviour
• SET DEADLOCK_PRIORITY LOW
• IN SQL SERVER 2005 or Higher. This value can be set to -10 through 10 Or LOW-MEDIUM-HIGH.
• Dead Lock Victim recieves error 1205.
• Your transaction (<tran id>) was deadlocked with another process and has been chosen the deadlock victim. Rerun your transaction.