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.

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.


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.



Ahmad Osama

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

Follow me on TwitterFollow me on FaceBook