SQL Server: Cyclic Deadlocks

Cyclic deadlock occurs when a process say P₁ is waiting on a process say P₂ to release a lock and process P₂ is waiting on P₁ to release a lock. The two process involved form a cyclic dependency resulting in a deadlock

The below queries when run in parallel in two different sessions, produce a cyclic deadlock scenario.

Use AdventureWorks2008R2
GO  
-- Run the below script in connection 1
BEGIN TRAN
 
UPDATE Sales.SalesOrderDetail 
SET OrderQty=10 
WHERE SalesOrderID=57024 
AND SalesOrderDetailID=60591 
 
WAITFOR DELAY '00:00:05'
 
UPDATE Person.Person SET Suffix='Mr.' WHERE BusinessEntityID=1 

ROLLBACK TRAN
 
--Run the below script in connection 2
Use AdventureWorks2008R2
GO
BEGIN TRAN
	 
UPDATE Person.Person SET Suffix='Mr.' WHERE BusinessEntityID=1 
 
WAITFOR DELAY '00:00:05'
	 
UPDATE Sales.SalesOrderDetail 
SET OrderQty=10 
WHERE SalesOrderID=57024 
AND SalesOrderDetailID=60591 
	 
ROLLBACK TRAN

One of the sessions will terminate with below error.

Msg 1205, Level 13, State 51, Line 7

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

The deadlock graph for the above scenario is given below. The deadlock graph can be captured using profiler or any other deadlock detection method.

The above deadlock graph shows that the session ID 57 has an exclusive lock on table Person.Person and is trying to get an exclusive lock on Sales.SalesOrderDetail. Similarly, the session ID 56 has an exclusive lock on table Sales.SalesOrderDetail and is waiting to get an exclusive lock on the table Person.Person.  Thus 57 is waiting on 56 to release lock on Sales.SalesOrderDetail table and 56 is waiting on 57 to release lock on Person.Person table. The two processes are caught in an endless loop. The SQL Server detects and ends this endless loop by terminating session ID 57.

Resoution:

The cyclic deadlock above occurs because the tables are being accessed in different order in the two sessions. If you check the demo queries shown above, tables are accesses as shown below.

Connection 1(session 56)

1. Update Sales.SalesOrderDetail
2. Update Person.Person

Connection 2(session 57)

1. Update Person.Person
2. Update Sales.SalesOrderDetail

Thus, session 56 first acquires X lock on Sales.SalesOrderDetail and session 57 acquires X lock Person.Person and then they get into endless loop as explained above.

So, to resolve these kind of deadlocks access the tables in same order in different sessions.

 
Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook