What are Deadlocks?

A deadlock occurs when two transactions have locks on separate objects and each transaction requests a lock on the other transaction’s object. For example:

• Transaction A holds a share lock on row 1.
• Transaction B holds a share lock on row 2.
• Transaction A requests an exclusive lock on row 2, but it cannot be granted until Transaction B releases the share lock.
• Transaction B requests an exclusive lock on row 1, but it cannot be granted until Transaction A releases the share lock.
• Each transaction must wait for the other to release the lock.

A deadlock can occur when several long-running transactions execute concurrently in the same database. A deadlock also can occur as a result of the order in which the optimizer processes a complex query, such as a join, in which you cannot necessarily control the order of processing.

How SQL Sever Ends a Deadlock

SQL Server ends a deadlock by automatically terminating one of the transactions. The process SQL Server uses is in the following list:

• Rolls back the transaction of the deadlock victim.
• In a deadlock, SQL Server gives priority to the transaction that has been processing the longest; that transaction prevails. SQL Server rolls back the transaction with the least amount of time invested.
• Notifies the deadlock victim’s application (with message number 1205).
• Cancels the deadlock victim’s current request.
• Allows the other transaction to continue.

Important: In a multiuser environment, each client should check regularly for message number 1205, which indicates that the transaction was rolled back. If message 1205 is found, the application should attempt the transaction again.

Guidelines for Minimizing Deadlocks

• While it is not always possible to eliminate deadlocks, you can reduce the risk of a deadlock by observing the following guidelines:
• Use resources in the same sequence in all transactions. For example, in all transactions that reference more than one table, reference the tables in the same order whenever possible.
• Shorten transactions by minimizing the number of steps.
• Shorten transaction times by avoiding queries that affect many rows.
• Set the DEADLOCK_PRIORITY setting to LOW. When this setting is set and the process encounters a deadlock, it will be chosen as the deadlock victim. This is useful for online analytical processing (OLAP) database or reporting processes that you do not want to interfere with higher-priority OLTP processes.

Lock Timeout

If a transaction becomes locked while waiting for a resource and a deadlock results, SQL Server will terminate one of the participating transactions with no timeout.
If no deadlock occurs, SQL Server blocks the transaction requesting the lock until the other transaction releases the lock. By default, there is no mandatory timeout period that SQL Server observes. The only way to test whether a resource that you want to lock is already locked is to attempt to access the data, which could result in getting locked indefinitely.
The LOCK_TIMEOUT setting allows an application to set a maximum time that a statement waits on a blocked resource before the blocked statement is automatically cancelled. The cancellation does not roll back or cancel the transaction. The application must trap the error to handle the timeout situation and take remedial action, such as resubmitting the transaction or rolling it back.
The following example shows how to set the lock timeout. The timeout_period is the number of milliseconds that SQL Server will wait before returning a locking error.

SET LOCK_TIMEOUT timeout_period

The KILL command terminates a user process based on the server process ID (spid).

KILL {spid | UOW} [WITH STATUSONLY]

Questions and Answers

Question: Have you experienced deadlocking problems in your current environment? If so, how did you determine that deadlocks were a problem, and how was it resolved?

Useful Links

For more information on deadlocks, see "Deadlocking", http://go.microsoft.com/fwlink/?LinkID=126243