3.3 Multiple-Resource Transactions

The next degree of complexity enters when updates span more than one transactionally aware resource. Going back to the checking account example, imagine that the Logging table is in Database A and the rest of the tables are in Database B. Neither database can now guarantee that all of the updates can be done en masse. Database A can guarantee that its updates are done (or not done), but it has no idea what Database B's opinion is on the subject. Database B is equally ignorant about how Database A feels about the situation.

When we have updates spanning two different transactionally aware resources, we have what I call a multiple-resource transaction . There are two types of multiple-resource transactions: tightly coupled and loosely coupled .

3.3.1 Loosely Coupled Multiple-Resource Transactions

In a loosely coupled multiple-resource transaction , we are willing to give the transactionally aware resources (databases) a bit of slack and "loosen up" on our expectations of the transaction guarantee.

Here is our compromise: as long as all of the updates to Database A are tightly coupled and all of the updates to Database B are tightly coupled, and as long as we can figure out a protocol for the software systems using Databases A and B to let each other know whether or not their respective transactions succeeded, then, and only then , we are willing to live with a decoupling (or loosening) of Database A's collection of updates from Database B's collection of updates.

In other words, we are willing to allow the possibility that Database A's collection of updates will succeed and Database B's collection will fail, or vice versa, as long as the failing systems using those databases let the other systems know of their failures (or even better, of their successes).

Figure 3.2 illustrates the flow in a loosely coupled multiple-resource transaction.

Figure 3.2. Flow in a Loosely Coupled Multiple-Resource Transaction

3.3.2 Tightly Coupled Multiple-Resource Transactions

There are two problems with loosely coupled multiple-resource transactions.

First, they have no standard protocol for the software systems to brag back and forth about their successes or commiserate about their failures (at least, as of press time). So guess who gets to design this protocol? You!

Second, imagine you are the system using Database A. Your transaction succeeds. Your buddy system, the one using Database B, is not so lucky. Its transaction fails. Your buddy sends you the bad news. Now what? Given that you have already completed your updates, what are you supposed to do about it? Good question!

Both of these problems can be solved if instead of using a loosely coupled multiple-resource transaction, we go with a tightly coupled multiple-resource transaction.

In a tightly coupled multiple-resource transaction , even though we know we are sending updates to two different resources, we still want the updates coordinated. We expect the underlying infrastructure to come up with some way of doing so.

The transaction is tightly coupled, meaning that all the updates must be treated as a single tightly coupled collection, with all being done or none being done (exactly the same semantics as we expected from the single-resource transaction). The updates to Database A are thus tightly coupled to those of Database B, even though Databases A and B are (obviously) different databases.

To achieve this tight transactional coordination across multiple resources, we need to involve a third party. This third party is usually referred to as the distributed transaction coordinator , affectionately known as DTC .

DTC's algorithms were first introduced by the technologies we know as transaction processing monitors (TPMs). In the IBM space, this functionality is contained within WebSphere. Within the Microsoft space, this functionality is part of MS DTC (you can guess what this acronym stands for).