Item 28: Differentiate user transactions from system transactions

Item 28: Differentiate user transactions from system transactions

A user is working with an online banking system. She wants to do a simple balance transfer from her savings account to her checking account (probably to cover the large check she just wrote earlier today). She selects the Balance Transfer option from the menu, selects her checking account from the list of accounts presented, types in the amount she wishes to transfer, selects her savings account from the second list of accounts presented, and clicks Go.

Exactly how many transactions are used here?

The question isn't as simple as it might seem. To the user, and any student of accounting, this will seem like one transaction, but to the system, it could be two database transactions, particularly if we're talking about working with multiple databases or other resources: for example, in order to help avoid keeping database locks open for a long period of time (see Item 29), we might first issue a query against the source account to make sure our user hasn't elected to transfer more money than is actually in the account, another query to make sure the target account still exists (the bank may have elected to close it because of some excessive insufficient funds problems, for example), and finally an update to take the money out of the source account and put it into the target account.

In some cases, we may even break the update statement into multiple steps, particularly in scenarios where the user's actions depend on the state of the data in the database at the time she begins. For example, our user may decide from which account to transfer money based on which one has more money at the moment, as shown in the list of her accounts.

Let's look at another situation, perhaps easier to recognize, in which the simple transaction model more or less fails. Assume we're speaking of a travel agency application. I want to travel from Sacramento, California, to Munich, Germany.^[2] Since there are (at last look) no direct flights from Sacramento to Munich, I have to take at least two flights to get there—one to an airline's central "hub" and the second to Munich. As a matter of fact, it's entirely possible that I'll travel on three flights—Sacramento to the U.S. hub to the European hub to Munich. Being a seasoned traveler, however, I have some preferences about how I travel. Specifically, I want to minimize the number of "hops," and I don't mind staying overnight between hops, as long as I don't have to take a train or taxi as part of the trip, and so on.

^[2] This example comes from Transaction Processing [Gray/Reuter, Section 4.2.4].

The travel agent, using a transacted system built to give him access to the travel systems of the major airlines, is going to take all this information and plug it against their databases to try to come up with a reasonable travel itinerary. He plugs my departure point into his system, which presents him with a list of possible destinations. He's more or less guessing about where to go next, so let's assume he starts by booking a flight from Sacramento to Chicago's O'Hare airport, since O'Hare is a major international airport and is likely to have a better choice of successive hops than, say, Rochester. He books that flight, then finds from there a flight to Heathrow in London. Unfortunately, at this point, there's no direct flight from Heathrow to Munich, but he can get me to Frankfurt and a taxi from Frankfurt to Munich, which I don't want, because I hate taxis. Time to start over.

So, again, how many transactions are we looking at?

Remember that the goal of a scalable system is to minimize locks (see Item 29), and the airlines are certainly interested in building the most scalable system possible. So let's consider the two possible ways the travel agent could use his system to try booking this flight.

While many programmers choose the first model, because in many respects it's the "cleanest" of the two, the first approach has some inherent problems.

Rolling back the distributed transaction essentially forces the travel agent to start over from scratch. Even if the Sacramento to Chicago leg is perfectly acceptable and has more options to explore, rolling back the entire thing forces the travel agent to rebook that leg. This, of course, assumes that the leg is still available—it's entirely possible that some other traveler, seeking to go from Sacramento to Chicago, has taken that window of opportunity between the completion of the rollback and the start of the second transaction to book that last remaining seat, leaving our travel agent with less to work with than he thought.

More importantly, however, the airlines are not going to allow the travel agent to hold locks against open seats for that length of time, for exactly the same reason: some other traveler may be interested in a seat that you're still "thinking about," and a "sure" sale is being denied in favor of a "possible" sale. This is not the way to keep revenues high. Instead, the airlines usually run with an optimistic concurrency model (see Item 33) that checks to see if the seat is still there only when I actually want to purchase it—yes, it's possible that a seat I had my eye on will suddenly disappear out from under me, but they're betting that the chances of that happening are far fewer than the number of seats that might not get sold if they allow people to "lock" seats without paying for them.

So, from an overall system perspective, the second approach is often better, despite the additional work it lays on the programmer. Astute readers will have already spotted a major flaw in this analysis: What happens if we need to roll back one of those earlier, completed transactions? If we've already committed to that Sacramento to Chicago flight, and we need to back out of it, it's not like we can just issue a ROLLBACK and undo the committed work—that would violate the durability part of the ACID transaction.

This is where the transactional community talks about a compensating transaction, a transaction that knows how to undo the effects of an earlier, committed transaction. In our trip planner example, then, the code has to know how to undo the committed transaction for both the Sacramento–Chicago and Chicago–Frankfurt legs, in order to allow the travel agent to undo the work done earlier. (Presumably the airlines are OK with travel agents being able to back out committed transactions in exchange for the locks against the database being held open for shorter periods of time, and in fact, this generally holds true for systems that have high scalability requirements.) Not to mention, any other travel agencies looking to book a flight from Sacramento to Chicago won't realize that the seat I was originally going to have is now free until that compensating transaction runs—in other words, we're also violating the atomicity aspect of the transaction.

Worse, you may start looking at the practical realities of writing compensating transactions. That's about when the blood starts to drain from your face: consider all the work that a system based on compensating transactions will require. "But it's so much simpler to just use the distributed transaction!" will be the rallying cry, and I, for one, won't disagree. But, as with many things, what's simpler for the programmer is not always better for the user. In this case, the tradeoff here is pretty straightforward: simplicity of the programming model against greater scalability of the system against multiple data sources.

Before you return this book to the bookstore in protest, let me add a couple of caveats, hopefully to assuage your sense of apprehension and keep the book in your hands.

First and foremost, this scenario only really applies when working against multiple databases. Yes, although we'll periodically want to control the lock granularity for transactions against a single database, there are other ways to do that, including trusting the underlying database plumbing to keep the locks as fine-grained as is reasonably possible. Database vendors spend a significant amount of their development lifecycle looking for ways to maximize the throughput of transactions conducted against their systems, far more time than you or I would (or even could), so at a certain level it doesn't make sense to try to second-guess their efforts. There are a few ways we can help, however, and much of the rest of this chapter discusses those.

Second, this compensating transaction model assumes that the actions performed against both databases must remain essentially atomic, but it willingly sacrifices isolation in exchange for scalability. This is much the same choice we make when deciding to turn down isolation levels on transactions (see Item 35), except here it's done at a multidatabase level. If this lowered isolation is unacceptable, we can't make use of the compensating transaction model, period. As with many things, this decision has to be made in conjunction with the users (and/or their business analyst representatives) and developers.

Finally, however, we already have an established pattern by which we can make this work much simpler from a programmatic standpoint: the Command pattern [GOF, 233], used to encapsulate "commands" to be carried out by wrapping the code in objects executed from a common interface. In Java, this interface is frequently the Runnable interface (which has the side benefit of making it easy to plug the Command object into a separate Thread of execution). One of the less-realized benefits of Command, however, is that it's also possible to bundle the "undo" operation of the Command right alongside the "do" operation:

public interface DatabaseCommand extends Runnable

{

  // We inherit public void run(); from Runnable

  //



  public void undo();

}



public class DatabaseWorker

{

  ArrayList commandList = new ArrayList();



  public void execute(DatabaseCommand dc)

  {

    commandList.add(dc);

    dc.run();

  }



  public void rollback()

  {

    ListIterator li =

      commandList.listIterator(commandList.size() + 1);

    while (li.hasPrevious())

      li.undo();

  }

}

Now it becomes trivial to create DatabaseCommand objects that implement not only the committed work but also the rollback work, and hand them one by one into the DatabaseWorker instance that is, in essence, now representing our user transaction.

And there we finally come to the crux of the situation: we need to keep the user's view of a transaction entirely separate from the system's view of a transaction, for the reasons cited earlier. In many cases, particularly when working with a single database, the two will align quite easily and naturally, and when that happens, feel free to simply fall back on the standard transaction mechanism without hesitation. Just be aware of transactional locks, and be willing to surrender the ease of the transactional model when necessary to achieve better scalability.