SQL Server: Cyclic Deadlocks

Cyclic deadlock occurs when a process say P1 is waiting on a process say P2 to release a lock and process P2 is waiting on P1 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.

1_SQL_Server_Cyclic_Deadlocks

   

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

   

8 Comments on “SQL Server: Cyclic Deadlocks”

  1. Nice information Ahmad,

    I have a confusion.

    We can avoide Deadlocack, but what should we do if a deadloack occer?
    Is Restarting SQL Server Service is the only solution?

  2. Hi Vivek – restarting sql server services is not at all a solution to avoid deadlocks. The recommended process is to find out the root cause of the deadlock and fix it. A temporary solution is to rerun the transactions once a deadlock occurs. I will soon write a blog post on these topics too.

  3. can you please explain the term “Rerun the transactions”

    if a deadlock occur during running of 2 SPs simulteniously, how can we solve this problem?

    We can avoid deadlock conditions, but if it occur, how can we rerun the transactions?

    did you mean hendel deadlock condition in SPs itself?

    Please advice.

  4. Hi Vivek – by rerun the transaction I mean detect and retry the failed transaction with in procedure itself.

  5. thanks ahmad.

    You means that that I need to catch the deadlock error in catch block and then try to rerun the process again.

    Please validate my understanding.

    I have 1 more issue encountered today.

    In a SP I forgot to rollback a transaction in catch block. An Error occured and process enteres into catch, since there was no rollback transaction, the lock on a perticular table holds infinitely due to missmatch no. begin transaction and commit/rollback transaction.

    This was in development server.

    when I recognize the issue after 2 Hours, I don’t have any other solution and I restarted the SQL server service.

    Is there any machenism, by which we can overcome such type of Deadlock/Locking situation.

    Please Advise.

  6. yes you need to catch deadlock error in catch block… I will provide you with code soon.. you don’t need to restart sql service.. just figure out and kill the open transacation.. in case you are unable to login to sql server through management studio use sqlcmd dedicated admin connection to get into sql server and kill open transaction..

Leave a Reply

Your email address will not be published.