Transact-SQL Distributed Transactions
The distributed transactions started in Transact-SQL have a relatively simple structure:
A Transact-SQL script or application connection executes a Transact-SQL statement that starts a distributed transaction.
The instance of the SQL Server Database Engine executing the statement becomes the controlling server in the transaction.
The script or application then executes either distributed queries against linked servers or remote stored procedures against remote servers.
As distributed queries and remote procedure calls are made, the controlling server automatically calls Microsoft Distributed Transaction Coordinator (MS DTC) to enlist the linked and remote servers in the distributed transaction.
When the script or application issues either a COMMIT or ROLLBACK statement, the controlling instance of SQL Server calls MS DTC to manage the two-phase commit process or to notify the linked and remote servers to roll back their transactions.
Required Transact-SQL Statements
The Transact-SQL statements controlling the distributed transactions are few because most of the work is done internally by the SQL Server Database Engine and MS DTC. The only Transact-SQL statements required in the Transact-SQL script or application are those required to:
Start a distributed transaction.
Perform distributed queries against linked servers or execute remote procedure calls against remote servers.
Call the standard Transact-SQL COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, or ROLLBACK WORK statements to complete the transaction.
For any Transact-SQL distributed transaction, the instance of the Database Engine processing the Transact-SQL script or connection automatically calls MS DTC to coordinate the commitment or rollback of the transaction.
Starting Distributed Transactions
You can start distributed transactions in Transact-SQL in the following ways:
Start an explicit distributed transaction using the BEGIN DISTRIBUTED TRANSACTION statement.
You can also execute a distributed query against a linked server. The instance of the Database Engine you have connected to calls MS DTC to manage the distributed transaction with the linked server. You can also call remote stored procedures on a remote instance of the Database Engine as part of the distributed transaction.
While in a local transaction, execute a distributed query.
If the OLE DB data source supports the ITransactionJoin interface, the transaction is promoted to a distributed transaction even if the query is a read-only query. If the data source does not support ITransactionJoin, only read-only statements are allowed.
If SET REMOTE_PROC_TRANSACTIONS ON has been executed and a local transaction calls a remote stored procedure on another instance of the Database Engine, the local transaction is promoted to a distributed transaction.
The Database Engine uses MS DTC to coordinate the transaction with the remote server. Calls to remote stored procedures execute outside the scope of a local transaction if REMOTE_PROC_TRANSACTIONS is set to OFF. The work done by the remote procedure is not rolled back if the local transaction is rolled back. The work done by the remote stored procedure is committed at the time the procedure completes, not when the local transaction is committed.
The REMOTE_PROC_TRANSACTIONS option is a compatibility option that affects only remote stored procedure calls made to remote servers defined using sp_addserver. The option does not apply to distributed queries that execute a stored procedure on a linked server defined using sp_addlinkedserver. For more information about distributed queries, see Distributed Queries.