INF: Resolving Deadlocks With Distributed Transactions

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.