A deadlock occurs when two or more tasks permanently block each other because each task has a lock on a resource that the other tasks are trying to lock. For example:
- Transaction A acquires a shared lock on row 1.
- Transaction B acquires a shared lock on row 2.
- Transaction A now requests an exclusive lock on row 2. It is blocked until transaction B finishes and releases the shared lock it has on row 2.
- Transaction B now requests an exclusive lock on row 1. It is blocked until transaction A finishes and releases the shared lock it has on row 1. Transaction A cannot complete until transaction B completes, but transaction B is blocked by transaction A. This condition is also called a cyclic dependency: Transaction A has a dependency on transaction B, and transaction B closes the circle by having a dependency on transaction A.
Both transactions in a deadlock will wait forever unless an external process breaks the deadlock. The SQL Server Database Engine deadlock monitor periodically checks for tasks that are in a deadlock. If the monitor detects a cyclic dependency, it chooses one of the tasks as a victim and terminates its transaction with an error. This allows the other task to complete its transaction. The application with the transaction that terminated with an error can retry the transaction, which usually completes after the other deadlocked transaction has finished.
In each deadlock, there is a single winner and one or more victims that are killed by the system.
| Legend | |||||||||||||||||||||||
| 1 | Main Deadlock List – according to the selected timeframe. Notice that if you don’t see deadlocks, it might be because the time range is too short.
|
||||||||||||||||||||||
| 2 | DeadlockExpand Button – the button expands the selected deadlock and shows the 4 grid | ||||||||||||||||||||||
| 3 | Show Details button – shows the Deadlock Chart | ||||||||||||||||||||||
| 4 | Inner Victim Grid. Once 2 Is pressed, the victim grid is displayed. For most occurrences, there will be a single victim.
|
||||||||||||||||||||||
| 5 | The deadlock chart | ||||||||||||||||||||||
| 6 | The victim expand button – press to see process details on the right panel (#8, #9) | ||||||||||||||||||||||
| 7 | The winner expand button – press to see winner process details on the right panel (#8, #9) | ||||||||||||||||||||||
| 8 | Expanded process details
|
||||||||||||||||||||||
The deadlock chart
| Legend | |
| 1,3 | Victim lock mode |
| 2 | Victim expand button (expands panels on the right with victim’s details) |
| 4 | Victim object lock |
| 5 | Winner object lock |
| 6,8 | Winner lock mode |
| 7 | Winner expand button (expands panels on the right with winner’s details) |