As you've seen, a single DBMS can enforce the ACID rules when it holds all the data involved in a transaction. For example, SQL Server supplies its own internal transaction manager and can provide commit and rollback behavior as well as recover committed data in the event of a system failure. However, OLTP systems that require distributed transactions are far more challenging. Commit and abort behavior as well as recovery must be coordinated across several data sources for each transaction.
Quite a few products use a similar high-level architecture to solve the problems of coordinating distributed transactions. For example, CICS from IBM, TUXEDO from BEA Systems, and MTS from Microsoft all use a high-level abstraction called a transaction manager (TM) and a special protocol called two-phase commit. Figure 10-1 shows how the TM is used in this distributed architecture. The application that controls the transaction serves as a transactional application. Each data source is known as a resource manager (RM). Each node (computer) in the system typically runs its own session of a TM. Let's examine the usual sequence of actions that occur when an application runs a distributed transaction.
Figure 10-1. A transactional application and transaction managers work together to monitor a distributed transaction and to execute the two-phase commit protocol across multiple resource managers.
A transactional application calls to its local TM to begin a transaction. This TM is known as the coordinating transaction manager (CTM). The CTM creates a unique ID for the transaction and initializes various state variables to manage the transaction. As the application establishes connections with various RMs, it must enlist these connections in the transaction. When the connection is enlisted, the RM establishes a connection with its local TM. There are two common enlistment cases that you should understand.
When the RM is running on the same node as the transactional application, the RM enlists with the CTM. When the RM is running on a different node, the RM enlists with its local TM, which is known as a participating transaction manager (PTM). During the enlistment of a remote RM, the CTM must also establish a connection with the PTM unless a connection has already been established. Once each connection has been enlisted, the lines of communication are set up as shown in Figure 10-1. The CTM can now coordinate the transaction across one group of participants. The participants group is made up of the local RMs and PTMs.
The CTM enforces the ACID rules in a distributed transaction by running the two-phase commit protocol. After the transactional application indicates that it wants to commit the transaction, the CTM runs phase 1 to prepare the transaction. In this phase, the CTM sends an "are you prepared" message to each participant. If everything goes according to plan, each participant responds with an "I'm prepared" message. When every participant has responded with an "I'm prepared" message, the CTM knows that the changes can be committed. You should note that at this point the locks that have been acquired by the transaction are still being held.
After receiving an "I'm prepared" message from each participant, the CTM begins phase 2. The CTM writes to its log that the transaction has been committed and sends a "time to commit" message to each participant. When a local RM receives this message, the RM commits its changes and releases its locks. When a PTM receives this message, it tells its enlisted RM to commit its changes and release its locks. After a participant has committed its changes, it sends an "I'm done" message back to the CTM. Once the CTM receives this message from every participant, it assumes that all of the work has been successfully completed.
I have just given you a brief overview of the two-phase protocol. As a programmer, you don't have to be overly concerned with its inner workings. The two-phase commit protocol is really just an implementation detail. When you commit a distributed transaction in MTS, you should assume that the system will enforce the ACID rules. There is nothing in the MTS programming model that requires you to think about the two-phase commit protocol. However, if you want to read more on the subject, pick up a copy of Principles of Transaction Processing, by Bernstein and Newcomer. This book covers many aspects of the two-phase commit protocol that are important to system implementers and administrators.
Microsoft's transaction manager is the Distributed Transaction Coordinator (DTC). This product initially shipped with SQL Server 6.5, but it's now heavily used by other products such as MTS and Microsoft Message Queue (MSMQ). The DTC runs on Windows NT as a system service. Figure 10-2 shows that the DTC can act as a coordinating TM and also as a participating TM.
The DTC uses a protocol called OLE Transactions, which defines a set of COM interfaces through which applications and RMs communicate with the DTC. X/Open is another popular standard used by products such as TUXEDO, Encina, and TOP END. X/Open, like OLE Transactions, standardizes the way in which applications and RMs communicate with the transaction manager. These standards have a few differences. OLE Transactions is object based, while X/Open is not; OLE Transactions supports multithreaded applications, while X/Open supports a single thread of control. For details about interoperability between these standards, see the MTS SDK.
Figure 10-2. The DTC, Microsoft's transaction manager, is used by products such as MTS, MSMQ, and SQL Server.
If you want to execute distributed transactions using the DTC, you have two choices. The easy way is to use MTS and let the MTS run time make all the complicated calls to the DTC for you. Another option is to create an application that communicates directly with the DTC. If you choose the second option, you should write your code in C or C++.
Let's look at what it takes to write code directly against the DTC. As a Visual Basic programmer, you will never have to code at this level, but if you look behind the scenes you'll gain an appreciation for what MTS does on your behalf.
The transactional application must first establish a connection with the DTC by calling DtcGetTransactionManager to obtain an ITransactionDispenser reference to the DTC Proxy Core Object. The ITransactionDispenser interface contains a method named BeginTransaction. When you invoke this method, the DTC creates a new transaction object and returns a reference to your application. This transaction object exposes a Commit method and an Abort method through the ITransaction interface. When you create a transaction, you can specify both the isolation level and a timeout interval.
Next you must establish a connection to one or more resource managers. For example, you can connect to a DBMS such as SQL Server or Oracle using an ODBC driver. The ODBC driver acts as the RM proxy. After establishing an ODBC connection, you must propagate the transaction to the resource manager using the ODBC SQLSetConnectAttr function. When you make this call, the RM proxy tells the resource manager to enlist with its local DTC so that the resource manager can participate in the two-phase commit. At this point, you can use the ODBC connection to execute SQL operations such as INSERT, UPDATE, and DELETE. All of your operations will be charged against your transaction.
If the transactional application calls Abort, the local DTC tells all the participants to roll back their changes and release their locks. If the transactional application successfully completes its work on all RMs, it calls Commit to tell the local DTC to start executing the two-phase commit protocol. The local DTC calls in parallel to each participant, telling it to prepare its work. The fact that these calls are made in parallel means that the local DTC doesn't have to wait for the first participant to respond before sending the prepare request to the second participant. This parallelism improves performance significantly.
After the local DTC receives an "I'm prepared" response from each enlisted participant, it begins phase 2. First the local DTC writes to its log to record the fact that the transaction has been committed. Next it sends a commit message to each participant in parallel. Each participant commits its changes, releases its locks, and sends an "I'm done" message back to the coordinating DTC. Once the coordinating DTC receives all the "I'm done" messages, it can forget about the transaction because there's no longer any need to recover the transaction in case of a system failure.
Note that the DTC can perform an optimization if the two-phase protocol isn't required. Commit coordinator delegation occurs when the coordinating DTC detects that only one participating DTC is enlisted in the transaction. This is the case if you create a DTC-based transaction using only one RM. The coordinating DTC sends a "delegated commit" message instead of an "are you prepared" message. The "delegated commit" message transfers commit coordinator responsibility from the coordinating DTC to the participating DTC. This simply reduces the number of round-trips required to commit a transaction. This is useful to know because many transactions involve only a single SQL Server database, and those transactions must be optimized to run as fast as possible.
As you can see, the requirements for writing an application directly against the DTC are pretty strict. Fortunately, MTS provides a much easier programming model. Your MTS application is just another client to the DTC. Behind the scenes in your MTS application, things happen as described here but MTS hides the details of interacting with the DTC and enlisting RMs.