Microsoft Distributed Transaction Coordinator

3 4

As mentioned, MS DTC is a part of Windows 2000 Component Services. (The last incarnation of MS DTC was included with SQL Server 7.) Component Services also includes COM+. COM+ is used when nontransactional communication is needed, and MS DTC is used when transactional communication is needed.

MS DTC is used mainly to manage distributed transactions. A distributed transaction is a transaction that uses data from two or more databases. These databases can be on separate computer systems, or they can be on the same system. Transactions between tables in the same database are not considered distributed transactions. Transactions between databases on the same system can be initiated as standard (nondistributed) transactions; however, SQL Server executes them as distributed transactions.

Overview of MS DTC

Many applications require the coordination of transactions that involve multiple data sources. Coordination is necessary when multiple data sources are involved in a transaction because it guarantees that the transaction is executed in an atomic fashion. In other words, coordination ensures that the individual transactions (occurring in the various data sources) that compose the larger transaction all succeed or all fail. If one part of a transaction succeeds and another fails, inconsistency problems and data loss can result. The MS DTC service provides this coordination for you. (Without this service, developers would be responsible for coming up with some sort of distributed transaction scheme for their applications.) MS DTC does this by performing a two-phase commit.

The two-phase commit is not a new technique, nor is it specific to SQL Server. Two-phase commit technology has been around for many years, but its reliability and performance have improved. In addition, the flexibility of the two-phase commit has been enhanced. In fact, a distributed transaction using a two-phase commit can be performed between a SQL Server database and an Oracle database. Because the two databases both support two-phase commits and access protocols such as ODBC, the distributed transaction is possible.

A two-phase commit is a commit operation split into two parts: the first part is the prepare phase, which is followed by the commit phase. These phases are initiated by a COMMIT command from the application, which signals MS DTC to perform the two-phase commit. MS DTC coordinates the operation with the systems involved in the distributed transaction by communicating with SQL Server on its own system and with MS DTC on the other systems. MS DTC components that handle two-phase commits are known as resource managers.

Once the COMMIT command is issued, MS DTC signals the resource managers to execute the prepare phase of the two-phase commit. In the prepare phase, all of the functions necessary for the commit to occur are performed, including flushing out buffers and writing transaction log records. The operations performed in this phase are similar to the operations involved in a standard commit operation. The only difference is that during the prepare phase, SQL Server does not mark the transaction as committed, nor does it release all of the resources and locks that the transaction used. After all of the actions necessary for the transaction to commit on a data source are completed, the resource manager for that data source returns a success signal to the transaction manager. When success signals have been returned from all the data sources, the commit phase can be initiated.

In the commit phase, the commit actually takes place on the distributed systems. If all of the resource managers signal a successful commit phase, a notification is sent to the application. If any of the resource managers fail, a notification is sent to the other resource managers to perform a rollback operation. This failure is then reported to the application. In the event that communication between the systems is interrupted, the transaction eventually times out, and a rollback is initiated. If any system involved in the distributed transaction fails, all systems involved in that distributed transaction perform a rollback.

Examples of Using MS DTC

In this section, we'll look at two Real World examples that demonstrate how the coordination services of MS DTC are critical when distributed transactions are used. These examples show what can happen if some transactions in a distributed transaction succeed and others fail.

REAL WORLD   Bank Transactions

---

Suppose you are choosing a way in which computer systems at two banks perform transactions when money is transferred between the banks. Two basic transactions must be performed by these systems when a customer requests an electronic transfer: money must be removed from account 1 at bank A and added to account 2 at bank B.

How does this electronic transfer happen? If both accounts were at the same bank, this transfer could be performed by simply updating data in the database on one system. But because the accounts in our example are at different banks, the transfer involves initiating a remote transaction. The transaction, which comprises two transactions, can be initiated by either of the two systems involved in the transaction, or it can be initiated by a third system (a client system).

These two transactions can be executed by a teller initiating a program that runs a series of SQL statements on the third (client) computer system. These SQL statements perform the following operations:

1. Update account 1 on system A by removing n dollars
2. Commit transaction
3. Update account 2 on system B by adding n dollars
4. Commit transaction

This technique will work as long as no problems occur on any of the systems, but it is not a safe way to transfer money. What would happen if one of the systems fails during these operations? The following results could occur:

• The first transaction fails because of an error on either system A or the client system. The second transaction is thus not performed, so no money is transferred.
• The first transaction succeeds, but the second fails because of an error on either system B or the client system. The money is lost.

The second result is certainly not an acceptable option. Here's a better way of conducting this transaction:

1. Update account 1 on system A by removing n dollars.
2. Update account 2 on system B by adding n dollars.
3. Commit both transactions as a set. Either both commit or neither commits.

MS DTC enables you to perform these operations. By using the MS DTC service, you can specify which distributed transactions are committed as a set. These transactions will either all commit or all be rolled back.

REAL WORLD   E-Commerce Applications

---

Many e-commerce applications are designed to handle thousands of online users and numerous concurrent transactions. Companies that use these applications often run them on multiple systems. Using a single computer system can be unsuitable for many reasons, including the following:

• The capacity of one system is insufficient. Any computer system has limitations. The number of transactions performed by an e-commerce application might exceed the capacity of a single system.
• Business logic. It might make sense from a business perspective to put different functions on different systems. For example, a company might want user information on one system and product information on another system.
• Outsourcing is used. E-commerce functions are sometimes outsourced. For example, a company could hire another company to perform billing.

When multiple systems (or multiple databases on the same system) are used, distributed transactions are initiated to access information in the systems (or databases). If MS DTC is not used, atomicity might be impossible to guarantee.

Let's look at an e-commerce transaction that uses distributed transactions. In this example, the customer wants to use her credit card to purchase a product, a new water bowl for her dog, from an imaginary company named Piercetronics. Let's deconstruct this transaction into its major components.

NOTE

---

There are many ways to perform e-commerce transactions. The method described in the following list is not necessarily the best way of performing e-commerce transactions but is rather a simple method that was chosen to make this example easier to understand.

1. The customer establishes a connection to Piercetronic's Web server. At this point, she is most likely looking at static Web pages and is not actually connecting to the database.
2. She browses around for a while and finally finds the water bowl that she wants to purchase. She then puts it into her shopping cart.
3. Placing the item into the shopping cart will require two database operations to be carried out:
   1. A lookup must be performed to find out whether the customer is a return customer or a new customer. If she is a return customer, her account ID is retrieved. (Some applications do this at purchase time.)
   2. The item is inserted into a shopping-cart table.
4. When the customer indicates she is ready to check out, a database transaction or set of transactions begins in order to perform the following operations:
   1. The shopping-cart table is read in order to find the item that the customer is purchasing.
   2. The order table has a new row inserted that contains the order.
   3. The customer database is accessed to update the customer's account so that she can be billed for this order. Because in this example customer account information is stored in a database separate from the database containing the order table, the transaction is escalated to a distributed transaction.
   4. The record in the shopping-cart table is deleted at this point. (However, some applications purge the shopping-cart table at a later time, in a batch operation.)
   5. Once both the account table and the order table are updated, the transaction can be committed. Thus, the distributed transaction is finished.
5. At a later time, employees at Piercetronic's warehouse will access the database and put together the order for shipment. More specifically, the following transactions occur:
   1. The order table is queried and the order is retrieved.
   2. The item is pulled off a shelf and put in a box for shipment.
   3. Piercetronic's system connects to a credit card processor to charge the customer's card. This transaction is a distributed transaction because the charge is recorded locally as well. (Some applications bill the customer's card when he or she checks out, but this is not the typical procedure, as getting credit card approval usually takes longer than the transaction can be held open.)
   4. In a distributed transaction, the order table is updated and the database containing account information is updated to reflect the shipment.
6. The package arrives and the customer's dog has a new water dish to drink from.

If any of the components had failed during these distributed transactions and if Piercetronics had not been using MS DTC, which enables two-phase commits, several problems could have occurred, including the following:

• The customer's credit card could have been charged for an item that she never received.
• Her order could have been placed and shipped without a charge to her credit card.
• The order table could have been updated to indicate the product was shipped, when the shipping never occurred.

So you can see the necessity of making sure that either all systems are updated or no systems are updated in a distributed transaction. Without the coordination of these transactions, the databases could become inconsistent and several problems could occur.

MS DTC Properties

As mentioned, the MS DTC service runs within SQL Server to coordinate transactions. It performs complex handshaking and error checking to ensure that the proper sequences of events occur. Without MS DTC, you will find that coordinating updates on server systems and guaranteeing consistency can be complicated.

You can invoke MS DTC by using one of the following methods:

• Invoking a remote procedure that is a distributed transaction
• Using Transact-SQL (T-SQL) commands to promote your SQL statements to a distributed transaction
• Updating data on multiple OLE DB data sources
• Embedding MS DTC commands in your application

If you use one of the first three methods, the distributed transaction is initiated from within SQL Server on the same system on which your transaction is initiated, as shown in Figure 25-7. The SQL Server instance running on the server on which the transaction is initiated will perform all of the operations necessary to invoke MS DTC to handle the distributed transaction—no user intervention is necessary. SQL Server will handle all of the details for you.

Figure 25-7. MS DTC communication in a distributed transaction initiated from within SQL Server.

If you use the fourth method, embedding MS DTC commands within your application, the client application and the SQL Server network interface will communicate with MS DTC as well as with SQL Server. The SQL Server client will help coordinate the distributed transaction. The architecture of this distributed transaction is shown in Figure 25-8.

Figure 25-8. MS DTC communication in a distributed transaction initiated by embedding MS DTC commands within an application.

Programming MS DTC

Because this book is not geared toward developers, you will not find here all the specifics concerning how to initiate and program distributed transactions. This section simply lists ways in which you can initiate distributed transactions and shows you how to test MS DTC by running a simple transaction.

You can initiate a distributed transaction by performing one of the following actions:

• Accessing a remote data source from within a transaction If you do this, the transaction will be escalated to a distributed transaction. Any distributed query within a transaction will escalate that transaction.
• Explicitly issuing a BEGIN DISTRIBUTED TRANSACTION command This will explicitly create a distributed transaction.
• Using the REMOTE_PROC_TRANSACTIONS SQL Server configuration option This will immediately escalate transactions to distributed transactions if a remote stored procedure is called.
• Calling OLE DB or ODBC functions OLE DB and SQL Server include syntax for initiating distributed transactions.

You can test MS DTC by initiating a distributed transaction via T-SQL. You initiate the distributed transaction by using the T-SQL command BEGIN DISTRIBUTED TRANSACTION, and you complete it by using the COMMIT command, as shown in the following example:

 BEGIN DISTRIBUTED TRANSACTION SELECT EmployeeID FROM Northwind.dbo.Employees SELECT emp_id FROM pubs.dbo.employee GO COMMIT GO 

Enter just the first four lines of the preceding code. By delaying the COMMIT command and the final GO command, you will be able to view the transaction on the Transaction List folder of the Distributed Transaction Coordinator folder, which is in the Component Services MMC administration console. To view this folder expand Component Services, expand Computers and then My Computer, and finally expand Distributed Transactions Coordinator in the Component Services MMC administration console. After you view the transaction, enter the final two lines of the T-SQL code. Notice that the transaction, now committed, no longer appears on the Transaction List tab. Of course, most distributed transactions are more complex than this one, involving updates and inserts, but this example is easy to run and does not alter any database tables.

Distributed transactions will typically be invoked from within a program by using ODBC or DB-LIB API calls to start and terminate each transaction. Distributed transactions are programmed in much the same way as other transactions, except that the connection must be opened with Autocommit turned off so that each SQL statement does not commit automatically. MS DTC will handle the two-phase commit whenever the application ends the transaction with either a COMMIT or a ROLLBACK option.

MORE INFO

---

For more information about SQL Server application development, see Inside Microsoft SQL Server 7.0 (Microsoft Press, 1999), by Ron Soukup and Kalen Delaney. If you are using Microsoft Visual Basic, see Hitchhiker's Guide to Visual Basic and SQL Server (Microsoft Press, 1998), by William Vaughn.

Administering MS DTC

By default, MS DTC is installed along with Windows 2000. All you have to do is enable the technology. You can start MS DTC in two ways: by using SQL Server Service Manager or by using the system services component of Component Services, which was described earlier in this chapter. You invoke the SQL Server Service Manager utility by clicking Start, pointing to Programs, pointing to Microsoft SQL Server, and then choosing Service Manager. This utility is shown in Figure 25-9.

Figure 25-9. Using SQL Server Service Manager to start MS DTC.

If Distributed Transaction Coordinator is not shown in the Services box, select it from the drop-down list. Click Start/Continue to start the service, and click Stop to stop it. You should also select the check box that indicates whether MS DTC will start when the operating system starts.

Monitoring MS DTC

In order to monitor MS DTC, you must use the Component Services administration console. Start up the console (as described earlier in this chapter), and expand Component Services, Computers, My Computer, and Distributed Transaction Coordinator, as shown in Figure 25-10.

Figure 25-10. The Distributed Transaction Coordinator folder in the Component Services administration console.

You will see two options in this folder: Transaction List and Transaction Statistics.

Transaction List

The Transaction List view, shown in Figure 25-11, allows you to view a list of distributed transactions that are currently running on your system. You can view the properties of a transaction, and you can actually resolve a transaction by forcing it to commit or abort. You do this by right-clicking the transaction and choosing the appropriate option from the shortcut menu that appears.

Transaction Statistics

The Transaction Statistics view, shown in Figure 25-12, enables you to view distributed-transaction information such as the number of transactions in progress and the maximum number of active transactions. This information gives you an overview of the distributed-transaction activity occurring on your system. It can also be helpful for planning future capacity.

As you have seen, monitoring distributed transactions is not difficult. And, as you have seen earlier in the chapter, creating and running distributed transactions is easy as well.

Figure 25-11. The Transaction List view showing MS DTC transactions.

Figure 25-12. The Transaction Statistics view showing MS DTC transactions.