Distributed transactions are transactions that involve resources from two or more sources. Microsoft® SQL Server™ supports distributed transactions, allowing users to create transactions that update multiple SQL Server databases and other sources of data.
A distributed transaction involves:
The software controlling each resource involved in a distributed transaction is known as a resource manager. A distributed transaction is made up of local transactions in each individual resource manager. Each resource manager must be able to commit or roll back its local transaction in coordination with all the other resource managers in the distributed transaction. SQL Server can operate as a resource manager in a distributed transaction that complies with the X/Open XA specification for Distributed Transaction Processing.
Committing or rolling back a distributed transaction is controlled by a software component called a transaction manager. The transaction manager coordinates with each resource manager to ensure that all the local transactions making up the distributed transaction are committed or rolled back together. The Microsoft Distributed Transaction Coordinator (MS DTC) service operates as a transaction manager. MS DTC complies with the X/Open XA specification for Distributed Transaction Processing.
Special commit processing is required to prevent problems in managing transactions spanning multiple resource managers. A commit of a large transaction can take a relatively long time as log buffers are flushed freed. The commit process itself can also encounter errors that would force a rollback. If a transaction manager simply asked each resource manager to commit, it could get a success status back from some resource managers and then get an error from one resource manager. This creates a conflict because all of the distributed transaction should be rolled back, but parts are already committed. Two-phase commits address this problem by dividing a commit into two phases:
The transaction manager sends a prepare to commit request to each resource manager. Each resource manager then performs all resource-intensive actions needed to complete the commit process, such as flushing all log buffers. The resource manager only retains the minimum locks needed to maintain the integrity of the transaction, and then returns success to the transaction manager.
If all the resource managers return success to their prepare requests, the transaction manager then sends commit commands to each resource manager. Each resource manager then quickly records the transaction as completed and frees the last held resources. If any resource manager returns an error to the prepare request, the transaction manager then sends rollback commands to each resource manager.
There are several ways applications can include SQL Server in a distributed transaction:
| Distributed Transactions | MS DTC Service |