Troubleshooting Deadlocks
In a deadlock, various threads cannot proceed because they are waiting on a set of resources held by each other or held by other threads. The deadlock is a cyclic dependency that is best addressed by first identifying the participants in the dependency chain and the resources involved, and then choosing the thread that breaks the deadlock on the resources for the various other threads.
When a deadlock occurs, the user receives the following error.
Transaction (Process ID xxx) was deadlocked on (xxx) resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
The error identifies the resource or resources on which the transaction is deadlocked. The resources can be locks, parallelism (or communication buffer), waiting threads, or a combination of them.
To resolve the deadlock, one of the participants in the cycle must be terminated. In picking the deadlock victim, Microsoft® SQL Server™ 2000 looks at all participating threads and how much work each one has done. Generally, SQL Server chooses the participating thread that has done the least amount of work as the deadlock victim.
With SQL Server 2000, you can determine the resources involved in a deadlock and troubleshoot locking by using SQL Profiler or Trace Flag 1204. SQL Profiler is a graphical tool available in SQL Server Enterprise Manager. SQL Profiler provides information for basic deadlock detection. For more information, see Monitoring with SQL Profiler.
This troubleshooting section focuses on the use of Trace Flag 1204, which provides a report that allows for a detailed analysis of deadlock situations.
Using Trace Flag 1204
In a deadlock situation, Trace Flag 1204 graphs the cycle of dependency among waiting threads, the resources on which the threads are waiting, and which threads hold these resources.
Terms in a Trace Flag 1204 Report
Although Trace Flag 1204 returns different information depending on the resources involved, the report typically includes the following terms.
Node:x
Represents the entry number (x) in the deadlock chain.
Lists
The lock owner can be part of these lists: Grant, Convert, and Wait.
- Grant List
- Enumerates the current owners of the resource.
- Convert List
- Enumerates the current owners that are trying to convert their locks to a higher level.
- Wait List
- Enumerates current new lock requests for the resource.
SPID: x ECID: x
Identifies the system process ID thread in cases of parallel processes. The entry SPID x ECID 0 represents the main thread, and SPID x ECID > 0 represents the sub-threads for the same SPID.
Statement Type
Is the SELECT, INSERT, UPDATE, or DELETE statement on which the threads have permissions.
Line #
Lists the line in the current batch of statements that was being executed when the deadlock occurred.
Input Buf
Lists all the statements in the current batch.
Mode
Specifies the type of lock for a particular resource that is requested, granted, or waited on by a thread. Mode can be IS (Intent shared), S (Shared), U (Update), IX (Intent exclusive), SIX (Shared with intent exclusive), and X (Exclusive). For more information, see Understanding Locking in SQL Server.
RID
Identifies the single row within a table on which a lock is held or requested.
RID is represented in Trace Flag 1204 as RID: db_id:file_id:page_no:row_no; for example, RID: 1:1:1253:0.
TAB
Identifies the table on which a lock is held or requested.
TAB is represented in Trace Flag 1204 as TAB: db_id:object_id; for example, TAB: 2:2009058193.
KEY
Identifies the key range within an index on which a lock is held or requested.
KEY is represented in Trace Flag 1204 as KEY: db_id:object_id:index_id; for example, KEY: 2:1977058079:1.
PAG
Identifies the page resource on which a lock is held or requested.
PAG is represented in Trace Flag 1204 as PAG: db_id:file_id:page_no; for example, PAG: 7:1:168.
EXT
Identifies the extent structure.
EXT is represented in Trace Flag 1204 as EXT: db_id:file_id:extent_no; for example, EXT: 7:1:9.
DB
Identifies the database lock.
DB is represented in Trace Flag 1204 in one of the following ways:
- DB: db_id
- DB:db_id[BULK-OP-DB], which identifies the database lock taken by the backup database.
- DB:db_id[BULK-OP-LOG], which identifies the lock taken by the backup log for that particular database.
IND
Identifies the lock taken by the index created on index resources.
IND is represented in Trace Flag 1204 in one of the following ways:
- IND: db_id:object_id:index_id
- IND: db_id:object_id:index_id[INDEX_ID], which indicates that the lock is on the index ID.
- IND: db_id:object_id:index_id[INDEX_NAME], which indicates that the lock is on the index name.
APP
Identifies the lock taken by an application resource.
APP is represented in Trace Flag 1204 as APP: lock_resource; for example, APP: Formf370f478.
When SQL Server chooses an application resource as the deadlock victim, the application resource owner will not get the error message described earlier. Instead, the application owner will get a "-3" return code when the sp_getapplock stored procedure is executed on that application resource. For information, see sp_getapplock.
Victim Resource Owner
Specifies the participating thread that SQL Server chooses as the victim to break the deadlock cycle. The chosen thread (identified by SPID x ECID 0 ) and all existing sub-threads (identified by SPID x ECID > 0) will be terminated .
Next Branch
Represents the two or more sub-threads from the same SPID that are involved in the deadlock cycle.
When the deadlock involves parallelism, the various sub-threads could be blocked on communication buffers, and one thread ends up waiting for the other sub-threads. It is a deadlock situation only if all the other threads also are involved in a deadlock. The next branch represents the deadlock chain tracing alternative paths.
For more information about Trace Flag 1204 deadlock reports involving specific resources, see Deadlocks involving locks, Deadlocks involving parallelism, and Deadlocks involving threads