Transactions Locks «Prev  Next»

Lesson 10Distributed transactions
Objective Create and manage distributed transactions.

Create and manage Distributed Transactions in SQL-Server

The Distributed Transaction Coordinator (DTC) still exists in SQL Server 2022. It's a Windows service that coordinates transactions across multiple databases, message queues, or other transactional resources, even if they reside on different servers.
However, the use of DTC is generally discouraged for new development due to several reasons:
  • Complexity: Setting up and managing DTC can be complex, requiring configuration on all participating servers and potential network security considerations.
  • Performance Overhead: DTC can introduce performance overhead compared to transactions within a single database instance.
  • Alternatives: In many scenarios, alternative approaches like database mirroring, replication, or transactional functionality within tools like Azure Service Bus can achieve similar results without the complexity of DTC.

Here's a breakdown of the current status of DTC in SQL Server 2022:
  • Functionality: DTC remains available and functional.
  • Microsoft's Stance: Microsoft recommends considering alternatives for new development and only using DTC when necessary.

When Might You Still Use DTC?
  • Legacy Applications: If you have existing applications that rely on DTC, there's no need to rewrite them immediately. However, for new features or functionalities, consider alternative approaches.
  • Specific Scenarios: In some specific scenarios, DTC might still be the best solution, such as coordinating transactions with non-SQL Server resources that require XA (eXtended Architecture) compliance.

Transaction Coordinator

A distributed transaction[1] is simply a transaction distributed across multiple SQL Servers. SQL Server actually does most of the work through the use of the "Distributed Transaction Coordinator (aka) DTC"
  • The DTC is a service that must be running for distributed transactions to function.
  • The DTC is started through the Service Manager, as shown in the following illustration:

Distributed Transaction Coordinator (DTC) Service
Distributed Transaction Coordinator (DTC) Service

Although distributed transactions are handled through your Transact-SQL statements much the same way as local transactions, they are not handled the same way by the server. The server must consider the possibility of network failure even if there are no locking conflicts or Transact-SQL problems.

Two-phase Commit

To solve the potential network issue, SQL Server employs a commit of the distributed transaction in two separate phases, called a two-phase commit (abbreviated 2PC).
  1. The first phase in 2PC is the prepare phase. This basically alerts all SQL Servers involved in the distributed transaction that a commit request has been issued. The individual SQL Severs then prepare by writing all buffers to disk and returning a status to the DTC.
  2. The second phase in 2PC is to actually process the commit. All SQL Servers involved in the distributed transaction actually perform a commit and report back a status. If any of the transactions do not commit successfully, the DTC rolls back the transaction.

Implicit or explicit Transactions

Distributed transactions can be either implicit or explicit. An implicit distributed transaction is one that is automatically escalated from a local transaction. This can happen if:
  1. A remote store procedure is called from within the local transaction
  2. A distributed query is issued from within the local transaction

An explicit distributed transaction is started in much the same way as a local transaction by using the BEGIN DISTRIBUTED TRANSACTION Transact-SQL statement. It follows this general syntax:



1) transaction_name is the name of your transaction. This is only used in the case of nested transactions so that they can be distinguished from each other, but is only used for the outer-most transaction.
1) transaction_name is the name of your transaction. This is only used in the case of nested transactions so that they can be distinguished from each other, but is only used for the outer-most transaction.

2) @transaction_variable is the name of your transaction as stored in a variable.
2) @transaction_variable is the name of your transaction as stored in a variable.

Specifies the start of a Transact-SQL distributed transaction managed by Microsoft Distributed Transaction Coordinator (MS DTC).
Syntax 
BEGIN DISTRIBUTED { TRAN | TRANSACTION } 
[ transaction_name | @tran_name_variable ] 
[ ; ]

Arguments for the Transact-SQL Distributed Transaction
  1. 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.
  2. @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.

BEGIN DISTRIBUTED TRAN
INSERT INTO LocalTimesheets (EmployeeID, ClientID, 
 TaskID, WeekEndingDate, Hours)
VALUES (1002, 102, 11, "02/02/99", 40)
INSERT INTO RemoteTimesheets (EmployeeID, ClientID, 
 TaskID, WeekEndingDate, Hours)
VALUES (1002, 102, 11, "02/02/99", 40)
COMMIT TRAN

To commit or rollback a distributed transaction, use the same keywords as those used for a local transaction.
The next lesson shows you how to handle the errors that can occur within your transactions.
[1]Distributed Transaction :A transaction distributed across multiple SQL Servers.

SEMrush Software