Transactions Locks «Prev  Next»

Lesson 10Distributed transactions
Objective Create and manage distributed transactions.

Create and manage Distributed Transactions in SQL-Server

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

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).
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.
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:


Distributing SlideShow
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.