INF: Resolving Deadlocks With Distributed Transactions

 Last reviewed: December 15, 1997
Article ID: Q149935

The information in this article applies to:
  • Microsoft SQL Server, version 6.5

SUMMARY

In distributed transactions, deadlocks can occur and application developers planning to implement distributed transactions with SQL Server 6.5 need to understand their options.

MORE INFORMATION

SQL Server 6.5 includes the Microsoft Distributed Transaction Coordinator (MS DTC), which provides a robust, high-performance, scalable, easy-to-use distributed transaction facility for the Microsoft Windows NT and Windows 95 operating systems. MS DTC addresses the challenges of processing transactions over a distributed set of software components that exist on a single computer or on a system of networked computers. MS DTC is fully integrated with Microsoft SQL Server. Through MS DTC, remote stored procedure calls can now participate in distributed transactions, so that changes on multiple servers can be committed as a single unit of work.

MS DTC includes no facilities for deadlock avoidance or detection. Application developers who plan on implementing distributed transactions with MS DTC and SQL Server have two options:

- Implement deadlock avoidance in the application by serializing their 

  access to common tables among different servers.
- Implement timeouts either within the application or via a SQL Server 
  server-wide trace flag.
If the application has tight control over what distributed transactions are being performed, it can serialize access to common resources such that any deadlocks that might occur would be contained within a single SQL Server where they could be detected and dealt with as normal deadlocks are handled. For instance, if the intention is to maintain transactional integrity between three tables on three different SQL Servers (T1 on SERVER1, T2 on SERVER2, and T3 on SERVER3), you should always issue the table modification queries in the same table order for any distributed transaction that affects these tables. This might, for example, always be T1 first, T2 second, and T3 third.

Another option that you can use when there is little control over the types of distributed transactions that might be issued is timeouts. For DB- Library and ODBC applications, this can be implemented in the calling program. For ad-hoc Transact-SQL usage, SQL Server 6.5 has implemented trace flag -T8503, which allows a system administrator to define a server- wide DTC transaction timeout value for server to server DTC transactions started with either BEGIN DISTRIBUTED TRAN or through the implicit transaction mechanism, ("remote proc trans", or SET REMOTE_PROCEDURE_TRANS ON). When the trace flag is set, the value from the sp_configure "remote query timeout" value is used as the transaction timeout value, in seconds. If the transaction is still active after the time has elapsed, the DTC initiates transaction abort. A value of 0 represents an infinite timeout.

Additional query words: sql65 dtc distributed deadlock
Keywords : SSrvLock kbusage
Version : 6.5
Platform : WINDOWS
Issue type : kbhowto

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: December 15, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.