Microsoft SQL Server 2012 Bible

Dealing with Deadlocks

A deadlock is a special situation that occurs when two or more processes are competing for the same set of resources; each prevents the other from obtaining the source it needs to complete its work.

Deadlocks are not a relational database specific problem; they can occur in any system where there is potential for resource contention, such as operating systems. However, because this is SQL Server-specific literature, you focus on the deadlock nuances inside the database engine.

Following is a simple example of a common deadlock scenario:

• Transaction 1 has a lock on data A and needs to lock data B to complete its transaction.

and

• Transaction 2 has a lock on data B and needs to lock data A to complete its transaction.

Each transaction is stuck waiting for the other to release its lock, and neither can complete until the other does, and each process will not release the resource it already has for the other process to use. This stalemate continues until the database engine chooses a victor.

Deadlocks do not always only include two transactions. It is completely possible that Process A can be waiting on a resource held by Process B, which is, in turn, waiting on a resource held by Process C. If C is waiting on a resource that Process A or Process B has locked, a deadlock is created.

Creating a Deadlock

It's easy to create a deadlock situation in SQL Server using two connections in Management Studio's Query Editor, as illustrated in Figure 47.5. Transaction ...