Distributed Transactions

Distributed Transactions (Across Multiple SQL Server Instances or Linked Servers)

Definition:

A Distributed Transaction is a transaction that spans multiple SQL Server instances (or other database systems), often involving Linked Servers or remote servers.

Key Characteristics:

Aspect	Description
Scope	Spans multiple servers or instances (e.g., across network, cloud, linked servers).
Transaction Control	Requires MSDTC (Microsoft Distributed Transaction Coordinator) to coordinate the 2-Phase Commit (2PC).
Two-Phase Commit (2PC)	Required to ensure atomicity across multiple instances.
Performance Impact	Higher impact due to network latency and MSDTC overhead.
DTC (Distributed Transaction Coordinator)	Required – MSDTC must be enabled on all participating servers.
Typical Use Case	Financial systems, inter-branch transactions, data sync across different servers/locations.
Rollback Consistency	Ensured via 2PC and MSDTC but slower compared to local transactions.
Example (Distributed Transaction with Linked Server)

 

BEGIN DISTRIBUTED TRANSACTION;

UPDATE LocalDB.dbo.Accounts SET Balance = Balance - 100 WHERE AccountID = 1;

UPDATE RemoteServer.FinanceDB.dbo.Accounts SET Balance = Balance + 100 WHERE AccountID = 2;

COMMIT;