BUG: 1205 - Deadlock Situation Not Detected by SQL Server

ID: Q239753

The information in this article applies to:

Microsoft SQL Server version 7.0

BUG #: 56284 (SQLBUG_70)

SYMPTOMS

Bound sessions and DTC operations allow multiple SPIDs to bind or enlist in the same transaction scope. This can lead to a condition termed Distributed Deadlock. The SQL Server does not detect the deadlock situation when three or more SPIDs are engaged in two bound or enlisted transactions.

Here is a simple example of a distributed deadlock the SQL Server will not detect:

Sequence order DTC Tran #1 DTC Tran #2

1 SPID 10 enlists

2 SPID 10 updates authors

3 SPID 11 enlists

4 SPID 11 updates titles

5 SPID 11 updates authors
Becoming blocked on SPID 10

6 SPID 12 enlists

7 SPID 12 updates titles
Becoming blocked on SPID 11

The example clearly shows a deadlock between the two transactions but the deadlock is distributed across multiple SPIDs enlisted in those transactions.

If the final update to titles was attempted on SPID 10 instead of SPID 12, the correct deadlock message will be raised.

Your transaction (process ID #%d) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction.

For more details on bound or enlisted sessions refer to the following sections of SQL Server Books Online:

Using Bound Connections

dbenlisttrans

Distributed Transactions

How to use Microsoft Distributed Transaction Coordinator (ODBC)

WORKAROUND

Reduce the chances of encountering the deadlock by employing standard deadlock avoidance techniques. Refer to the Avoiding Deadlocks section of SQL Server Books Online for complete details.

The following articles contain additional deadlock information.
Q149935 INF: Resolving Deadlocks With Distributed Transactions
Q75722 INF: Reducing Lock Contention in SQL Server

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

MORE INFORMATION

The design of Microsoft Transaction Server (MTS) is such that the behavior can be encountered easier. The MTS components can be registered to use a DTC transaction to complete the proper business logic. However, use of ODBC or OLE DB connections can be pooled by OLE DB Resource Pooling and/or ODBC connection pooling.

Many sections of the MTS documentation encourage a developer to open the connection, perform the work and close the connection. In reality, this is opening a connection and automatically enlisting the connection in a DTC transaction based on transaction properties established for the MTS-based object. The default transaction level is also SERIALIZABLE. This can cause shared locks at the SQL Server to be held increasing the chance of blocking and deadlock activity.

The COM object may contain multiple methods that are invoked to complete the given actions for the transaction. If multiple object methods are used to complete the business logic associated with the transaction it is very likely that the pooling can and will use multiple connections (SPIDs) to the SQL Server to complete the transactional work. However, when the undetected deadlock situation is encountered the default transaction timeout for DTC is 60 seconds. In many cases 60 seconds later a transaction will time out and the processing can continue.

In addition, the query timeout could play a significant roll. The default query timeout for many MDAC components is 30 seconds, thus the query timeout would be exceeded before the DTC transaction timeout expired.

[ODBC SQL Server]Query Timeout

The following article contains detailed information and techniques for handling and identifying blocking issues:

Q162361 INF: Understanding and Resolving SQL Server Blocking Problems

A special consideration: When looking at blocked SPIDs bound or enlisted in the same transaction watch the sysprocesses column open_tran. SPIDs bound or enlisted in the same transaction space contain identical transaction counts. This can help to pattern which SPIDs are part of the same transaction space.

Use of SQL Profiler, capturing the text column of the DTC events, records the DTC transaction IDs. (The binary data column can also provide valuable information about bound and enlisted transaction IDs.) When working with DTC transactions specifically refer to the PROPAGATE DTC event to cross reference the SPIDs and which transactions were propagated to which SPIDs.

Additional query words:

Keywords :
Version : winnt:7.0
Platform : winnt
Issue type : kbbug