Deadlock or Just a Queue? Find the Wait Cycle

Deadlock or just a queue? Three sessions are waiting. Name the two stuck together without blaming the third.

A waiting session is not automatically a deadlock. That distinction matters when the phone rings and everyone is looking at a diagram with too many arrows. Some waits clear as soon as the session ahead finishes. A deadlock is different: the waiting forms a cycle.

In the picture, teal arrows mean HOLDS and red arrows mean WAITS FOR. Session A holds resource X and wants Y. Session B holds Y and wants X. Session C wants X but holds neither resource in this drawing. Look at the arrows and ask which sessions would remain stuck if nobody intervened. Take a moment before you read the answer; the extra session is there to make you work a little.

The labels are deliberately simple. There is no hidden isolation level or lock mode to guess. This is a graph puzzle about the shape of the wait, not a complete SQL Server deadlock report.

Deadlock or Just a Queue: The Answer

A and B form the deadlock. A cannot get Y until B lets it go. B cannot get X until A lets it go. That is the cycle. C is waiting for X, but C does not close a cycle; it is in the queue behind the trouble.

SQL Server detects deadlocks and chooses a victim so work can continue. A long wait by itself does not prove the same condition. The next time you read a deadlock graph, follow the ownership and requests around the loop before you decide which statement deserves your attention.

This is why I like drawing a problem before naming it. If three people are waiting, ‘three-way deadlock’ sounds dramatic, but the arrows may say something much smaller. Here the right explanation is two sessions in a cycle and one bystander.

The diagram is intentionally a toy. Real deadlock reports include more detail about processes and resources. The useful question remains the same: where does the wait circle back? Once you can point to that circle, you can start asking why those transactions took the locks in that order.

For the technical detail, see Microsoft Learn.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.