Microsoft® SQL Server® 2012 Bible

382

Part III: Advanced T-SQL Data Types and Querying Techniques

Distributed Transaction Coordinator

The responsibility of handling distributed transactions falls on the shoulders of the

Microsoft Distributed Transaction Coordinator (DTC). SQL Server uses the DTC to handle

server transactions, commits, and rollbacks. The DTC uses a two-phase commit for multiple

server transactions. This two-phase commit ensures that every server is available and han-

dling the transactions by performing the following, and highly necessary, steps:

1. Each server is sent a “prepare to commit” message.


2. Each server performs the fi rst phase of the commit, ensuring it can commit the
   transaction.


3. Each server replies when it fi nishes preparing for the commit.


4. Only after every participating server positively responds to the “prepare to commit”
   message is the actual commit message sent to each server.

It should go without saying that the DTC is needed only when DML operations are required.

If the unit of work involves reading only, you do not need the DTC.

Developing Distributed Transactions

Distributed transactions are not that different from normal local SQL Server transactions,

except for a few minor extensions to the syntax:

SET xact_abort ON;

BEGIN DISTRIBUTED TRANSACTION

In case of an error, the xact_abort connection option can cause the current transaction,

not the current T-SQL statement, to roll back. You need the xact_abort ON option for

any distributed transactions accessing a remote SQL Server, including most other

OLE DB connections.

The BEGIN DISTRIBUTED TRANSACTION command fi rst determines if the DTC service is

available, but the command is not strictly required. If a transaction begins with

BEGIN TRAN, the transaction escalates to a distributed transaction, and DTC is checked as

soon as the distributed query executes.

That doesn’t mean you shouldn’t include the BEGIN DISTRIBUTED TRANSACTION com-

mand. It is a good practice to include the command so that DTC is checked at the beginning

of the transaction.

How does it work? The following example illustrates distributed transactions between two

different instances: a local instance and a linked server instance:

SET xact_abort ON

BEGIN DISTRIBUTED TRANSACTION

UPDATE Person.Contact

c15.indd 382c15.indd 382 7/30/2012 4:50:40 PM7/30/2012 4:50:40 PM

http://www.it-ebooks.info