Distributed Transactions (OLTP)---a Technical Reference Guide for Designing Mission-Critical OLTP Solutions

  • Article

Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions.

Distributed transactions are required when an application needs to update resources that span multiple instances or servers that are managed by different resource managers. For example, customers use distributed transactions when they update data both on Microsoft SQL Server and mainframe DB2 database within the same application transaction. A distributed transaction must be synchronized (that is provide atomicity, consistency, isolation, and durability [ACID]) among multiple participating databases that are distributed among different instances or servers.

A transaction within a single instance of the SQL Server Database Engine that spans two or more databases is actually a distributed transaction. However, the instance manages the distributed transaction logic internally; to the user, the transaction operates as a local transaction.

At the application level, a distributed transaction is managed much the same as a local transaction. At the end of the transaction, the application requests that the transaction be either committed or rolled back. A distributed commit must be managed differently by the transaction manager to avoid the risk that any kind of failure may result in some resource managers successfully committing while others roll back the transaction. This is achieved by managing the commit process in two phases: the prepare phase and the commit phase. Together, the prepare phase and the commit phase are known as a two-phase commit (2PC) protocol.

Best Practices

This section provides some best practice guidance and resources for more information. (Note that the full URLs for the hyperlinked text are provided in the Appendix at the end of this document.)

Using Distributed Transactions

  • Do not use distributed transactions unless you need to. The use of distributed transactions can adversely impact performance and response time, because of the additional work that Microsoft DTC (MSDTC) needs to do to execute the two-phase commit protocol to ensure the ACID properties. These can also be difficult to debug. While the article Two-Phase Commit1 is not targeted directly at SQL Server, it provides solid background on the protocol. For development against and management of MS DTC, see the DTC Developers Guide2 and the DTC Administration Guide.3

  • If a clustered instance of SQL Server is involved in distributed transactions, specific attention has to be given to MSDTC installation and configuration to avoid issues. In the BeforeInstalling FailoverClustering4 section of SQL Server 2008 R2 Books Online, you can find a detailed checklist that can help prevent additional problems in this configuration. The white paper SQL Server 2008 Failover Clustering5 has additional useful information.

Performance and Troubleshooting

  • Use the classes in the System.Transactions namespace of the Microsoft .NET Framework to manage the transactions. These classes automatically promote transactions to distributed transactions when they detect two or more resource managers (RMs) participating in the transaction (a single RM will not use MSDTC). See the following "Advanced Topics" section in Introducing System.Transactions in the .NET Framework 2.06 for general information and the article System.Transactions Namespace7 for specifics of using the classes with .NET Framework 4.

  • Configure the application servers to use a remote DTC coordinator that resides on the computer that is running SQL Server instead of using the local MSDTC coordinator. In our case, this provides significant benefit. Refer to the post ResolvingDTC Related Waits and Tuning Scalability ofDTC8 on the SQL Server customer advisory team (CAT) blog for specific guidance on how to optimize MSDTC usage in a distributed application.

  • Debugging distributed transactions can be complex and in a distributed environment, networking related issues can create additional complexity and generate performance and availability problems. Some customers have found the MSDTC Troubleshooting9 section of the SQLWebpedia website useful for troubleshooting.

Case Studies and References

The following case studies provide examples of enterprises using MSDTC.

Questions and Considerations

This section provides questions and issues to consider when working with your customers.

  • Use declarative, distributed transactions when you need transactions to span multiple resource managers or when you need to flow transaction context between distributed components. Also, consider Enterprise Services transaction support for compensating transactions that permit you to enlist non-transactional resources in a transaction. For example, you can use the Compensating Resource Manager to combine a file system update and a database update into a single atomic transaction.

  • Applications written using Object Linking and Embedding, Database (OLE DB), Open Database Connectivity (ODBC), ActiveX Data Objects (ADO), ADO.NET, or DB-Library can use Transact-SQL distributed transactions by issuing Transact-SQL statements to start and stop distributed transactions. OLE DB and ODBC also contain support at the API level for managing distributed transactions. OLE DB and ODBC applications can use these API functions to manage distributed transactions that include other Component Object Model (COM) resource managers that support MSDTC transactions other than the SQL Server Database Engine. The article MS DTC Distributed Transactions12 describes how to control distributed transactions from ODBC and OLE DB.

  • Two-phase commit protocols are not suitable for long-lived transactions such as a transaction that needs to interact with several external systems that might take a relatively long time to respond or which cannot be guaranteed to always be available. For such long-lived transactions, consider using a queuing technology such as SQL Server Service Broker and by handling rollback operations via compensating transactions wherever possible.

Appendix

Following are the full URLs for the hyperlinked text.

1 Two-Phase Commithttps://msdn.microsoft.com/en-us/library/aa754091(BTS.10).aspx

2 DTC Developers Guidehttps://msdn.microsoft.com/en-us/library/ms679938(VS.85).aspx

3 DTC Administration Guidehttps://msdn.microsoft.com/en-us/library/ms681291(v=VS.85).aspx

4 Before Installing Failover Clusteringhttps://msdn.microsoft.com/en-us/library/ms189910.aspx

5 SQL Server 2008 Failover Clusteringhttp://sqlcat.com/whitepapers/archive/2009/07/08/sql-server-2008-failover-clustering.aspx

6 Introducing System.Transactions in the .NET Framework 2.0https://msdn.microsoft.com/en-us/library/ms973865.aspx#introsystemtransact_topic5

7 System.Transactions Namespacehttps://msdn.microsoft.com/en-us/library/system.transactions.aspx

8 Resolving DTC Related Waits and Tuning Scalability of DTChttps://blogs.msdn.com/b/sqlcat/archive/2010/05/11/resolving-dtc-related-waits-and-tuning-scalability-of-dtc.aspx

9 MSDTC Troubleshootinghttp://www.sqlwebpedia.com/content/msdtc-troubleshooting

10 Mediterranean Shipping Company Managing 22 Terabytes of data with SQL Server 2008https://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=4000003470

11 Progressive Prepares for Future Growth, Gains Agility with SQL Server 2005https://www.microsoft.com/casestudies/Microsoft-Windows-Server-2003-Enterprise-x64-Edition/Progressive-Group-The/Progressive-Prepares-for-Future-Growth-Gains-Agility-with-SQL-Server-2005/4000002133

12 MS DTC Distributed Transactionshttps://msdn.microsoft.com/en-us/library/ms190799.aspx