2/5/2018 BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)

BEGIN DISTRIBUTED TRANSACTION (Transact-

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

Transact-SQL Syntax Conventions

Syntax

BEGIN DISTRIBUTED { TRAN | TRANSACTION }

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. For more information, see Using Row
Versioning-based Isolation Levels.

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.

https://technet.microsoft.com/en-us/library/ms188386(v=sql.105).aspx 1/3
2/5/2018 BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)

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 AdventureWorks2008R2 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 AdventureWorks2008R2;
GO
BEGIN DISTRIBUTED TRANSACTION;
-- Delete candidate from local instance.
https://technet.microsoft.com/en-us/library/ms188386(v=sql.105).aspx 2/3
2/5/2018 BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)

DELETE AdventureWorks2008R2.HumanResources.JobCandidate
WHERE JobCandidateID = 13;
-- Delete candidate from remote instance.
DELETE RemoteServer.AdventureWorks2008R2.HumanResources.JobCandidate
WHERE JobCandidateID = 13;
COMMIT TRANSACTION;
GO

See Also
Reference
BEGIN TRANSACTION (Transact-SQL)
COMMIT TRANSACTION (Transact-SQL)
COMMIT WORK (Transact-SQL)
ROLLBACK TRANSACTION (Transact-SQL)
ROLLBACK WORK (Transact-SQL)
SAVE TRANSACTION (Transact-SQL)
Concepts
Distributed Transactions (Database Engine)

Community Additions

© 2018 Microsoft

https://technet.microsoft.com/en-us/library/ms188386(v=sql.105).aspx 3/3