Detecting and Ending Deadlocks
In Microsoft® SQL Server™ 2000, a single user session may have one or more threads running on its behalf. Each thread may acquire or wait to acquire a variety of resources, such as:
- Locks.
- Parallel query execution-related resources (coordinator, producer, consumer threads associated with an exchange port).
- Threads.
- Memory.
All these resources, except memory, participate in the SQL Server deadlock detection scheme. For memory, SQL Server uses a time-out based mechanism, which is controlled by the query wait option in sp_configure.
In SQL Server 2000, deadlock detection is performed by a separate thread called the lock monitor thread. The lock monitor thread initiates a deadlock search for a particular thread in one of the following conditions:
- The thread has been waiting for the same resource for a specified period of time. The lock monitor thread periodically wakes up and identifies all the threads waiting on some resource. If these threads continue to wait on the same resource when the lock monitor wakes up again, it initiates a deadlock search for the waiting thread.
- The thread waits on a resource and initiates an eager deadlock search.
SQL Server typically performs periodic deadlock detection only; it does not use the eager mode. Because the number of deadlocks encountered in the system is usually small, periodic deadlock detection helps to reduce the overhead of deadlock detection in the system.
When the lock monitor initiates deadlock search for a particular thread, it identifies the resource on which the thread is waiting. The lock monitor then finds the owner(s) for that particular resource and recursively continues the deadlock search for those threads until it finds a cycle. A cycle identified in this manner forms a deadlock.
After a deadlock is identified, SQL Server ends a deadlock by choosing the thread automatically (the deadlock victim) that can break the deadlock. SQL Server rolls back the deadlock victim's transaction, notifies the thread's application (by returning error message number 1205), cancels the thread's current request, and then allows the transactions of the nonbreaking threads to continue.
Typically, SQL Server chooses the thread running the transaction that is least expensive to undo as the deadlock victim. Alternatively, a user can set the DEADLOCK_PRIORITY of a session to LOW, using the SET statement. The DEADLOCK_PRIORITY option controls how sessions are weighed in deadlock situations. If a session's setting is set to LOW, that session becomes the preferred victim when involved in a deadlock situation.
Identifying Deadlocks
After a deadlock is identified, SQL Server chooses a particular thread as the deadlock victim and returns an error message with a list of resources involved in the deadlock. The deadlock message takes the following form:
Your transaction (process ID #52) was deadlocked on {lock | communication buffer | thread} resources with another process and has been chosen as the deadlock victim. Rerun your transaction.
The threads and resources involved in a deadlock are located in the error log. For more information about how to identify the deadlocked threads and the resources involved in a deadlock, see Troubleshooting Deadlocks.