Distributed Transactions


In Chapters 1 and 3, I mentioned that SQL Server actually supports a form of transaction management that is not necessarily local to the server on which a session is established. This form of transaction is known as the distributed transaction (DT). Instead of processing on just the local server, the transaction is able to span multiple servers that engage in the distributed processing as resource managers, while to the originating session it appears that the transaction is executing locally. The transaction manager on each server coordinates the transaction processing according to standards of operation defined by either the Microsoft Distributed Transaction Coordinator (MS DTC) or the X/Open Specification for Distributed Transaction Processing.

Distributed transactions are mainly used to update multiple instances of SQL Server (and any other DBMS that supports either of the DT protocols just mentioned). This is how it works:

At the originating server, the session or client manages the transaction exactly as it does a regular transaction. However, when it comes time to either roll back or commit, a coordination effort is required between all the participating servers to ensure that either all or none of the servers can commit or roll back successfully. In a distributed transaction, some of the servers in the “mob” might be on the ends of unreliable network connections (like the Internet) and may be at risk of being lost in the middle of a transaction.

A coordination effort is required to ensure that all servers were able to run the transactions through to their logical conclusions. The coordination is carried out by what is known as a two-phase commit process (2PC), encompassing a prepare phase and a commit phase. The phases operate as follows:

  1. Prepare phase   When the transaction manager receives a commit request, it sends a prepare command to all the resource managers involved in the distributed transaction scenario. Each participant then goes about ensuring that it has what it takes to ensure the durability of the transaction. For example, all transaction logs are flushed to disk. As each member succeeds or fails in the prepare phase, it updates the transaction coordinator or manager with a success or failure flag.

  2. Commit phase   If the transaction coordinator receives the okay from all servers, it sends out commit commands to the participants. The resource managers on each server complete their respective commits and notify the transaction coordinator of success or failure. If any resource manager reports a failure to prepare, or does not respond after the commit, the coordinator sends a rollback message to the other servers.

There are two options to managing distributed transactions. You can go through a database API, or you can use T-SQL, the preferred method for SQL Server developers. Going through T-SQL enlists the services of the Microsoft Distributed Transaction Coordinator (MS DTC), so there is really not much work that you have to do, and issuing a distributed transaction is not much more work than issuing a local transaction.

The transaction originates either from a stored procedure called by a client application, or a statement executed against the server, or in a script executed either from a command line application or from one of the GUI interfaces that can transmit a T-SQL batch to the server.

There are a number of ways to start a distributed transaction in T-SQL:

  • Explicit   You can start an explicit distributed transaction using the BEGIN DISTRIBUTED TRANSACTION statement.

  • Distributed Query   While in a local transaction, you can execute a distributed query against a linked server.

  • REMOTE_PROC_TRANSACTIONS   If this statement is issued ON and the local transaction calls a remote stored procedure, the local transaction is automatically promoted to a distributed transaction. If REMOTE_PROC_TRANSACTIONS is off, calls to the remote stored procedures execute outside of the scope of the local transaction. A rollback in the local transaction does not affect the remote transaction, and vice versa. Similarly, the transaction processing of the remote transaction is unaffected by the local transaction processing. They commit their work independent of each other.

Note 

The REMOTE_PROC_TRANSACTIONS statement is issued if you plan to make remote stored procedure calls to the remote server defined with sp_addserver. See Chapter 14 for more information on stored procedures.




Microsoft SQL Server 2005. The Complete Reference
Microsoft SQL Server 2005: The Complete Reference: Full Coverage of all New and Improved Features
ISBN: 0072261528
EAN: 2147483647
Year: 2006
Pages: 239

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net