Submitting Hierarchical Changes

Working with Distributed Transactions

In Chapter 4, I talked about the ADO.NET Transaction object. You can use a Transaction object to group the results of multiple queries on a connection as a single unit of work.

Let's say your database contains banking information. You can transfer money from a savings account to a checking account by executing the following two queries:

UPDATE Savings     SET BalanceDue = BalanceDue - 100     WHERE AccountID = 17 UPDATE Checking     SET BalanceDue = BalanceDue + 100     WHERE AccountID = 17

To make sure you can group the two changes into a single unit of work that you can commit or roll back, you can create a new Transaction object before executing the queries. If an error occurs or one of the queries doesn't have the desired row, you can roll back the transaction. Otherwise, you can commit the changes you made in the transaction. Figure 11-16 shows how to wrap both changes in a single transaction. (You know all this already.)

Figure 11-16

Wrapping multiple changes to a database in a transaction

But what do you do if the checking and savings accounts aren't in the same database?

You can start a transaction on each connection. Then, if you determine that the withdrawal from the savings account or the deposit into the checking account failed, you can roll back both transactions. Otherwise, you can commit them both. That sounds simple enough. Figure 11-17 depicts such an application.

Figure 11-17

Wrapping changes to separate databases in separate transactions

Let's say you commit the withdrawal from the savings account, but just before you commit the deposit into the checking account, you lose your network connection. The database will detect the lost connection and roll back the transaction automatically. You will have withdrawn the money from the savings account but not deposited that money into the checking account.

Oops. Maybe using a transaction on each connection isn't a completely reliable solution to the problem. In order to make the system more reliable, your application needs to work more closely with both databases to coordinate the transactions and resolve problems like the one I've just described. You need to use a transaction that enlists multiple database connections. A transaction that can span multiple resources is generally known as a distributed transaction.

	Entire books are dedicated to transaction processing or to COM+. Obviously, I can't cover either topic nearly as thoroughly over the next few pages. I will simply provide an introduction to the basics of transaction processing and working with COM+ components. For more information on transaction processing, I strongly recommend Principles of Transaction Processing by Philip A. Bernstein and Eric Newcomer (Morgan Kaufmann, 1997).

Transaction Coordinators and Resource Managers

Two main components are involved in a distributed transaction: the resource manager and the transaction coordinator. A resource manager performs the desired work, whether it's modifying the contents of a database or reading a message from a queue. It then reports whether it was able to complete the work.

The transaction coordinator communicates with the resource managers participating in the transaction and manages the current state of the transaction. If one resource manager indicates that an error occurred, the transaction coordinator will receive this message and inform the other resource managers to cancel the work performed in the transaction. If all resource managers indicate that they successfully completed their tasks, the transaction coordinator will tell all the resource managers to commit the results of those tasks.

Two-Phase Commits

Each resource manager implements what's known as a "two-phase commit." The transaction coordinator tells each resource manager to prepare the changes performed during the lifetime of the transaction. This is just the first phase of the process. The resource managers have not actually committed the changes yet. They're simply preparing to commit the changes.

Once all the resource managers indicate that they are ready to commit the changes, the transaction coordinator will tell each resource manager to do so. If, however, one or more resource managers indicate that they could not prepare the changes, the transaction coordinator will tell each resource manager to cancel the work performed in the transaction.

Let's apply this to the problem scenario. When the transaction coordinator asks the resource managers to prepare to commit the changes, they both indicate that they are ready, willing, and able to commit the changes. The transaction coordinator sends a message to the resource manager for the savings account to commit the changes, but a fatal error (such as a power outage) occurs before it can communicate with the resource manager for the checking account. What happens now?

It's up to the transaction coordinator and the resource manager for the checking account to resolve the transaction. Each component is responsible for maintaining information about the status of the transaction. The transaction coordinator must be able to recover from the failure, determine that the transaction is still pending, and contact the appropriate resource managers to resolve the transaction.

The resource manager must be able to commit all changes that it prepared in the first phase of the commit process. Let's say the power outage that threw the resolution of the transaction into doubt occurred on the machine where the database that maintains the checking account is located. The database system needs to recover from the failure, determine that the transaction is still pending, provide the ability to commit those changes, and resolve the transaction with the coordinator.

As you can already tell, it takes a lot of work to develop a transaction coordinator or a resource manager.

Distributed Transactions in the .NET Framework

Microsoft initially introduced its transaction coordinator and supporting technologies for the Windows operating system as an add-on to Windows NT 4. This functionality is now integrated into the Windows operating system as part of Component Services.

The beauty of this architecture is that you have to write only a small amount of code to take advantage of the transactional features in Component Services. You write your code just like you would normally. You then tell Component Services whether to commit or abort the transaction, and it will take care of the grunt work necessary to manage a distributed transaction. Figure 11-18 shows an example of working with multiple databases in a distributed transaction using Component Services.

Figure 11-18

Using Component Services to wrap changes to multiple databases in a distributed transaction

Database Support for Distributed Transactions

In order to use distributed transactions with your database, your database system needs to have a resource manager that can communicate with the transaction coordinator that's built into Component Services.

Some database systems (such as SQL Server and Oracle) have resource managers that support such functionality, but many others (such as Access and dBASE) do not. Before you start planning an application that relies on distributed transactions, make sure you're using a database that has a resource manager that implements two-phase commits and can communicate with Component Services.

Actually, distributed transactions aren't just for databases. Microsoft Message Queuing services, for example, lets you send and receive messages as part of a distributed transaction.

Building Your Components

Building support for distributed transactions into your .NET component is relatively simple. First, be sure your project is a class library. Component Services is designed to run without any user interface. You wouldn't want your transactions to time out because they're displaying modal dialog boxes on a server that your users won't see. Second, be sure your project contains a reference to the System.EnterpriseServices namespace and that your class inherits from the ServicedComponent class. Now you're ready to write some transactional code.

To register your libraries in Component Services, you must use two command-line tools, Sn.exe and RegSvcs.exe. You use Sn.exe, which is located in the Framework SDK's Bin directory, to generate a strong name for your library. You then use RegSvcs.exe, which is located in the Framework's directory, to register your library with Component Services. In your code, you use the application name for your library and reference the strong name file, as shown in the following code snippet:

Visual Basic .NET

Imports System.Reflection ' Supply the COM+ application name.  <assembly: ApplicationName("MyServiceComponent")> ' Supply a strongly named assembly. <assembly: AssemblyKeyFileAttribute("MyServiceComponent.snk")>

Visual C# .NET

using System.Reflection; // Supply the COM+ application name. [assembly: ApplicationName("MyServiceComponent")] // Supply a strongly named assembly. [assembly: AssemblyKeyFileAttribute("MyServiceComponent.snk")]

For more information on these settings and using the command-line tools, see the MSDN documentation.

The TransactionOption Attribute

Not all objects that run in Component Services are designed to use distributed transactions. You can move business objects for your application into Component Services for other reasons, such as to better leverage connection pooling.

The TransactionOption attribute of the class controls whether instances of your class will participate in a transaction. In the following code snippet, instances of the class will always run in a transaction. If an instance of the class is created within the context of a transaction, the instance will participate in that transaction. Otherwise, the instance will receive its own transaction.

Visual Basic .NET

<Transaction(TransactionOption.Required)> _ Public Class clsDistributedTransaction     Inherits ServicedComponent           

Visual C# .NET

[Transaction(TransactionOption.Required)]  public class TxDemoSvr : ServicedComponent {            

You can set the TransactionOption attribute to any value in the TransactionOption enumeration, as shown in Table 11-2.

Table 11-2 Members of the TransactionOption Enumeration

Constant	Value	Description
Disabled	0	The component will not participate in a transaction. This is the default.
NotSupported	1	The component will run outside of the context of the transaction, if one exists.
Supported	2	The component will participate in a transaction if one exists, but it does not require a transaction.
Required	3	The component will participate in the current transaction if one exists. If no transaction exists, the component will be created in a new transaction.
RequiresNew	4	The component will always be created in a new transaction.

Enlisting Your ADO.NET Connection in the Transaction

Part of the beauty of the Component Services model is that you don't have to write code to enlist your ADO.NET connection in the Component Services transaction. You don't even have to use ADO.NET transactions. You simply let Component Services do the work for you. If your code is running in the context of a transaction, Component Services will automatically enlist your connection in the transaction.

Committing or Canceling Your Work

All that's left to do is add logic to your component to decide whether to commit or roll back the work you perform. If you find that your queries do not return the desired results or your code catches an unexpected exception from which you can't recover, you only need to execute a single line of code to roll back the work you've done on your connections. You simply call the SetAbort method of the ContextUtil object that's available to your class, as shown in the following code snippet. To commit the work performed in the transaction, you call the ContextUtil object's SetComplete method.

Visual Basic .NET

Public Sub MyTransactionalMethod()     Try         'Connect.         'Run queries.         'Disconnect.         If blnSuccess Then             ContextUtil.SetComplete()         Else             ContextUtil.SetAbort()         End If     Catch ex As Exception         ContextUtil.SetAbort()         Throw New Exception("Unexpected exception: " & ex.Message)     End Try End Sub

Visual C# .NET

public void MyTransactionalMethod()     try     {         //Connect.         //Run queries.         //Disconnect.         if (blnSuccess)              ContextUtil.SetComplete();         else             ContextUtil.SetAbort();     }     catch (Exception ex)     {.SetAbort();         throw         ContextUtil new Exception("Unexpected exception: " + ex.Message);     } }

	The ContextUtil object contains information about the COM+ context information. For more information on this object's features, see the MSDN documentation.

Remember that calling SetComplete at the end of your procedure does not necessarily mean that Component Services will commit the work performed on your transaction. This is simply the first phase of the two-phase commit. If any component that participates in the same transaction calls SetAbort, the transaction coordinator will tell the resource managers for all components to roll back the work performed in the transaction.

This behavior is analogous to a wedding ceremony. The marriage isn't official just because you say "I do." If the other person backs out at the last second, you're not married.

Distributed Transactions Made Simpler

Developers who have built components in previous incarnations of the Component Services technology (such as Microsoft Transaction Server) will remember the SetComplete and SetAbort methods. There's also a new way to tell the transaction coordinator whether you want to commit the changes made during the transaction.

Determining whether to commit or abort the changes made in the transaction is often very simple: you commit the changes unless an unexpected error occurs. To simplify the process, you can set the AutoComplete attribute on a procedure. When you set the AutoComplete attribute on a method, ComponentServices will assume that you want to commit the transaction unless the method throws an unhandled exception.

Just keep in mind that if you use the AutoComplete attribute and you trap for an exception in your code, you'll need to throw a new exception or rethrow the current one in order to indicate that you want to abort the transaction.

You can set the AutoComplete attribute on methods in your class, as shown in the following code snippet:

Visual Basic .NET

<AutoComplete()> _ Public Sub MyTransactionalMethod()            

Visual C# .NET

[AutoComplete()]  public void MyTransactionalMethod() {            

	If you're executing action queries or calling stored procedures to modify the contents of your database, be sure to check that the desired change or changes occurred. Remember that a query that modifies zero rows does not throw an error.

The DistributedTransaction Sample Application

The companion CD contains a working sample application (in Visual Basic .NET and in Visual C# .NET) that demonstrates the power of distributed transaction. The server component moves money between a checking account and a savings account, each of which is associated with a separate ADO.NET Connection object.

In fact, the server component uses separate child classes to change the balance due of each account. The server component also includes methods to let you abort the transaction even after both of the child classes have completed their work. The client application, shown in Figure 11-19, retrieves the current balance due for both accounts after each attempted transfer. You can use this client application to verify that the changes made in an aborted transaction were not committed to the database.

Figure 11-19

The client application in the DistributedTransaction sample

Other Benefits of Using Component Services

Other benefits go along with running your business objects in Component Services, such as connection pooling, object pooling, and having a central location for controlling your business logic. For more information on these features, see the MSDN and online documentation for Component Services. You'll also find samples in the Component Services subdirectory of the Framework SDK.

When Handling Advanced Updating Scenarios, Use ADO.NET

The CommandBuilder object and code-generation tools such as the Data Adapter Configuration Wizard make handling basic updating scenarios simple. Unfortunately, these tools do not generate the logic required to handle more advanced updating scenarios, such as detecting and resolving failed update attempts.

However, you can use various ADO.NET features to handle the more advanced updating scenarios. Armed with the knowledge you've gained from reading this chapter, you now have the ability to handle such scenarios using the ADO.NET features described in the chapter.