Distributed transactions are transactions that update data on two or more network-connected computer systems and are available as part of Microsoft Distributed Transaction Coordinator (MS DTC). Distributed transactions provide an interface to applications that update distributed data. MS DTC provides this by employing the "ACID" properties: atomicity, consistency, isolation, and durability.
Atomicity provides an all or nothing property to a transaction. At any point before the transaction is committed, either the application or the system can terminate the transaction. If the transaction is terminated, the system ensures that all effects of the transaction are undone. If the transaction is committed, the system ensures that all targets of the transaction are committed.
Consistency is preserved if a transaction transforms the data from one correct state to another. The application programmer can write procedures that ensures that the work done within a transaction preserves consistency.
Isolation prevents concurrent updates from interfering with one another. Generally, data read for a transaction cannot be updated by other concurrent transactions. Also, data written by a transaction cannot be read or updated by other concurrent transactions until the transaction is committed.
Durability ensures that committed transactions remain permanent even in the event of hardware, software, or network failures.
Distributed transactions are an essential tool for building robust, scalable, distributed applications. Distributed applications are vulnerable to hardware, software, and network failure. In the absence of distributed transactions, the application must detect and recover from these failures. Distributed transactions, however, permit distributed data to be updated while providing simple failure recovery semantics to the application.
For example, consider an order-entry system for compact discs. When an order is placed at headquarters, it is entered into the local (headquarter) database. The order details are transmitted to the database at the warehouse where the compact disc distribution actually occurs. All orders committed locally must have a parallel order committed remotely at the warehouse. Distributed transactions ensure that the order transaction is committed or rolled back in both databases. To ensure consistency in the headquarters database and the warehouse database, the server-to-server procedure calls will be in a single atomic transaction.
To participate in a transaction that is distributed by MS DTC in an MS DTC distributed transaction, the server must be instructed to participate in a distributed transaction and to inform MS DTC of that participation. SQL Server's participation in an MS DTC transaction can be started by any of the following:
Important Connected servers need a time-out period to shut down an inactive connection. Otherwise, the servers remain connected until the originating user's session is terminated.
To set a time-out period before you begin a transaction, use the sp_configure stored procedure with the 'remote conn timeout' option.
A BEGIN DISTRIBUTED TRANSACTION affects subsequent Transact-SQL statements.
These are the statements that are affected by MS DTC.
| Statement | Description |
|---|---|
| BEGIN TRANSACTION | The statements included between BEGIN DISTRIBUTED TRANSACTION and COMMIT TRANSACTION can include nested pairs of BEGIN TRANSACTION and COMMIT TRANSACTION statements. The transactions are not committed until the outermost transaction is committed. The nesting level of transactions is stored in the @@TRANCOUNT global variable. Each call to BEGIN TRANSACTION increases the outstanding transaction count by one. |
| BEGIN DISTRIBUTED TRANSACTION | BEGIN DISTRIBUTED TRANSACTION statements cannot be nested. SQL Server will detect such calls, reject them, and report an error. |
| COMMIT TRANSACTION | If the outstanding transaction count is one and this SQL Server session initiated the distributed transaction, then SQL Server calls MS DTC and commits the distributed transaction. Each call to COMMIT TRANSACTION decreases the outstanding transaction count by one. |
| PREPARE TRANSACTION | The PREPARE TRANSACTION statement is not permitted in a distributed transaction and will return an error. |
| ROLLBACK TRANSACTION | A distributed transaction can be rolled back when the ROLLBACK TRANSACTION statement is executed before the COMMIT TRANSACTION. MS DTC aborts the entire distributed transaction when the outstanding transaction count is one. |
| SAVE TRANSACTION | MS DTC does not support distributed savepoints. If an MS DTC transaction is aborted or rolled back, the entire transaction is rolled back to the beginning of the distributed transaction, regardless of any savepoints. |
Any user involved in a distributed transaction can roll back the transaction. However, only the user who started the transaction can commit the transaction.
For more information about distributed transactions, see the Guide to Microsoft Distributed Transaction Coordinator.
This example updates the author's last name on the local and remote databases. Both the local and remote databases will either commit or roll back the transaction.
BEGIN DISTRIBUTED TRANSACTION
UPDATE authors
SET au_lname = 'McDonald' WHERE au_id = '409-56-7008'
EXECUTE remote.pubs.dbo.changeauth_lname '409-56-7008','McDonald'
COMMIT TRAN