BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)

Article
12/29/2022

Applies to: SQL Server Azure SQL Managed Instance

Specifies the start of a Transact-SQL distributed transaction. When using SQL Server the distributed transaction is managed by Microsoft Distributed Transaction Coordinator (MS DTC).

In case of Azure SQL Managed Instance, the distributed transaction is managed by the service itself and not MS DTC. For information on distributed transactions in Azure SQL Database and Azure SQL Managed Instance, see Distributed transactions across cloud databases.

Transact-SQL syntax conventions

Syntax

BEGIN DISTRIBUTED { TRAN | TRANSACTION }
     [ transaction_name | @tran_name_variable ]
[ ; ]

Arguments

transaction_name

Is a user-defined transaction name used to track the distributed transaction within MS DTC utilities. transaction_name must conform to the rules for identifiers and must be <= 32 characters.

@tran_name_variable

Is the name of a user-defined variable containing a transaction name used to track the distributed transaction within MS DTC utilities. The variable must be declared with a char, varchar, nchar, or nvarchar data type.

Remarks

The instance of the SQL Server Database Engine executing the BEGIN DISTRIBUTED TRANSACTION statement is the transaction originator and controls the completion of the transaction. When a subsequent COMMIT TRANSACTION or ROLLBACK TRANSACTION statement is issued for the session, the controlling instance requests that MS DTC manage the completion of the distributed transaction across all of the instances involved.

Transaction-level snapshot isolation does not support distributed transactions.

The primary way remote instances of the Database Engine are enlisted in a distributed transaction is when a session already enlisted in the distributed transaction executes a distributed query referencing a linked server.

For example, if BEGIN DISTRIBUTED TRANSACTION is issued on ServerA, the session calls a stored procedure on ServerB and another stored procedure on ServerC. The stored procedure on ServerC executes a distributed query against ServerD, and then all four computers are involved in the distributed transaction. The instance of the Database Engine on ServerA is the originating controlling instance for the transaction.

The sessions involved in Transact-SQL distributed transactions do not get a transaction object they can pass to another session for it to explicitly enlist in the distributed transaction. The only way for a remote server to enlist in the transaction is to be the target of a distributed query or a remote stored procedure call.

When a distributed query is executed in a local transaction, the transaction is automatically promoted to a distributed transaction if the target OLE DB data source supports ITransactionLocal. If the target OLE DB data source does not support ITransactionLocal, only read-only operations are allowed in the distributed query.

A session already enlisted in the distributed transaction performs a remote stored procedure call referencing a remote server.

The sp_configure remote proc trans option controls whether calls to remote stored procedures in a local transaction automatically cause the local transaction to be promoted to a distributed transaction managed by MS DTC. The connection-level SET option REMOTE_PROC_TRANSACTIONS can be used to override the instance default established by sp_configure remote proc trans. With this option set on, a remote stored procedure call causes a local transaction to be promoted to a distributed transaction. The connection that creates the MS DTC transaction becomes the originator for the transaction. COMMIT TRANSACTION initiates an MS DTC coordinated commit. If the sp_configure remote proc trans option is ON, remote stored procedure calls in local transactions are automatically protected as part of distributed transactions without having to rewrite applications to specifically issue BEGIN DISTRIBUTED TRANSACTION instead of BEGIN TRANSACTION.

For more information about the distributed transaction environment and process, see the Microsoft Distributed Transaction Coordinator documentation.

Permissions

Requires membership in the public role.

Examples

This example deletes a candidate from the AdventureWorks2022 database on both the local instance of the Database Engine and an instance on a remote server. Both the local and remote databases will either commit or roll back the transaction.

Note

Unless MS DTC is currently installed on the computer running the instance of the Database Engine, this example produces an error message. For more information about installing MS DTC, see the Microsoft Distributed Transaction Coordinator documentation.

USE AdventureWorks2022;
GO
BEGIN DISTRIBUTED TRANSACTION;
-- Delete candidate from local instance.
DELETE AdventureWorks2022.HumanResources.JobCandidate
    WHERE JobCandidateID = 13;
-- Delete candidate from remote instance.
DELETE RemoteServer.AdventureWorks2022.HumanResources.JobCandidate
    WHERE JobCandidateID = 13;
COMMIT TRANSACTION;
GO

Share via