Anybody with little experience with SQL Server must have seen few deadlock errors and probably understood why it happens. So a definition may not be necessary. Books online defines deadlock as “A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock”

SQL server automatically detects deadlock conditions and choose one of the processes as the victim and rollback that transaction based on the DEADLOCK PRIORITY and log used. There are few exceptions to this rule for victim selection and in some cases more than one process may selected as a victim. We will discuss about this in detail in a later post.

A deadlock can occur on Locks, Worker Threads, Memory, Parallel query resources and MARS resources. Our discussions in this series will be around handling Lock related deadlocks.

A deadlock is not a bug, except for very few cases which we will discuss later. A deadlock is a natural side effect of a busy system and are not completely avoidable. But with careful database design, proper indexing and by handling deadlock errors, the possibility of deadlocks and the impact of it can be greatly reduced. Some of the deadlock scenarios we are going to discuss in the coming posts require use of additional locking hints or the use of sp_getAppLock

Deadlock Properties

• If a lock can be described as a unit of contention, a deadlock involves at least two units of contention.

• At least two processes will be involved in a deadlock.

• Each of the processes involved in a deadlock will be – directly or indirectly – holding a lock and will be waiting for another.

• The Pending requests are not compatible with the existing locks.

Deadlock Categories

Cyclic deadlock vs Conversion Deadlocks : Deadlocks are generally categorized as cyclic deadlocks or conversion deadlocks.

A cyclic deadlock happens when two processes accesses two resource in the opposite order. A cyclic deadlock involves two or more resources.

Following is a slightly modified version of the deadlock example from BOL

1. Transaction A acquires an exclusive lock on row 1.

1. Transaction B acquires an exclusive lock on row 2.

1. Transaction A now requests an exclusive lock on row 2, and is blocked until transaction B finishes and releases the exclusive lock it has on row 2.

1. Transaction B now requests an exclusive lock on row 1, and is blocked until transaction A finishes and releases the exclusive lock it has on row 1.

A conversion deadlock happens when two processes holding compatible locks try to Convert the lock into non-compatible mode. A conversion deadlock involves one or more resources.

If we modify the above example to show a conversion deadlock , it will look like

1. Transaction A acquires a shared lock on row 1.

1. Transaction B acquires a shared lock on row 1.

1. Transaction A now requests an exclusive lock on row1, and is blocked until transaction B finishes and releases the share lock it has on row 1.

1. Transaction B now requests an exclusive lock on row 1, and is blocked until transaction A finishes and releases the share lock it has on row 1.

Multi – Statement vs Single Statement deadlocks : Another way I would like to classify deadlocks are based on how many statements are involved in the deadlock. Simple deadlock scenarios involve multiple statements from multiple batches holding and waiting for conflicting locks. These deadlocks are relatively easy to troubleshoot. There are cases where the exact same statement executed by different processes ending up in a deadlock. We will see examples of both the cases in the coming posts.

Deadlocks and Isolation Levels

Deadlocks can happen in any isolation levels. The probability of deadlocks are higher in more restrictive isolation levels like SERIALIZABLE compared to less restrictive isolation levels like READ UNCOMMITTED. In the context of locks, Isolation levels only controls the duration and mode of Shared locks. Regardless of the isolation level, exclusive locks and update locks will be held until the end of the transaction.

In the next post I’ll introduce you to the resources I use for troubleshooting deadlocks.

This post is part of the series SQL Server deadlocks and live locks – Common Patterns. If you have questions, please post a comment here or reach me at @sqlindian