Distributed Transactions Architecture
Distributed transactions are transactions that involve resources from two or more sources. Microsoft® SQL Server™ 2000 supports distributed transactions, allowing users to create transactions that update multiple SQL Server databases and other sources of data.
A distributed transaction involves:
- Resource managers
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.
- Transaction manager
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.
- Two-phase commit (2PC)
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:
- Prepare
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.
- Commit
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.
- Prepare
There are several ways applications can include SQL Server 2000 in a distributed transaction:
- If an application has a local transaction and issues a distributed query, the local transaction is escalated to a distributed transaction.
- Issue a BEGIN DISTRIBUTED TRANSACTION statement.
- If an application has a local transaction and the option REMOTE_PROC_TRANSACTIONS is set ON, calling a remote stored procedure escalates the local transaction to a distributed transaction.
- Applications using the Microsoft OLE DB Provider for SQL Server or the SQL Server ODBC driver can use OLE DB methods or ODBC functions to have a SQL Server connection join a distributed transaction started by the application.