Item 35: Consider lower isolation levels for better transactional throughput

Item 35: Consider lower isolation levels for better transactional throughput

Consider for a moment the Isolation property of an ACID transaction. It puts up artificial "walls" between transactions, screening out any changes made to the data set until the transaction either completes or rolls back. It does so for good reason: imagine, if you will, a world in which transactions weren't isolated and could "leak" their effects to other transactions currently executing. Two parties, let's call them Bob and Jane, both access the database at the same time; let's assume they're a married couple, each trying to do some work against their bank account from different locations. Based on what each is trying to do and when, several kinds of results can take place.

• Lost update: Bob reads their checking account row from the checking account table at the bank. As soon as Bob's read completes, Jane then also reads the same row. Jane's next action, an update of $100 to the account (a deposit) is added to the total she holds and written back to the database. Then Bob gets to go, adding $100 to the total he holds, writing that back to the database. Each then commits. Note that Jane's deposit is now effectively lost—if there was $500 in the account to begin with, it now reads $600.

• Dirty read: Bob reads their checking account row from the checking account table. He then adds $100 to the total and writes it back. Jane then reads the row (perhaps she simply wants an account balance update). Unfortunately, Bob made a mistake and now rolls back his work. Jane is left believing the account has $100 more than it actually does since she got to see the uncommitted work.

• Nonrepeatable read: Jane reads the account balance. Bob reads the account balance. Jane then writes a data change—again, the $100 deposit—to the account balance row. Bob decides to play it safe and reread the data that, to him, he just read a few seconds ago, and suddenly he gets a different value than before.

• Phantom read: This one is harder to understand from the perspective of Bob and Jane, since we're now dealing with multiple rows. In essence, assume Bob looks at not just the checking account but the complete assets for the two of them. Jane, immediately after Bob's request completes, decides to open a new retirement account with the bank. When Bob decides to do a refresh of that exact same view, he suddenly sees the retirement account that wasn't there just a second ago.

The absence of any of these effects, the default scenario when transactions are "fully enabled," is called serializable access and essentially mimics the environment a given transaction would see if it were the only open transaction in the entire database—as if each and every transaction were lined up (serialized) and allowed to execute one right after the other. This is the safest mode to execute in but also requires the most locks and thus represents the highest contention.

Normally, most developers look at the list or possible problems and wonder precisely why I bring any of this up—after all, even the suggestion that a given database query might not return "correct" data is usually enough to drive most developers away from the idea. Don't be too quick to toss it entirely, though.

Imagine that I have a database of all the people currently living in Sacramento, California, a city of over a quarter of a million people—this is a fairly sizable database. I want to run a query that finds the average age of those who list their occupation as computer programmer. This query will not be fast, and during that time, the data will be changing—people getting hired, people getting fired, and so forth. Assume that I hold off updating the data until the query finishes—is the query still any good once the query returns? It's not likely that the average age will change much with the new changes to the data; in fact, it's entirely likely that nobody would even notice the difference. This is what the statisticians call the "margin of error" within a given set of data.

Now consider the cost of having held locks on the data during the query: everybody accessing that data was locked out during the query's execution, effectively increasing the latency and reducing the scalability of our application. Was it worth it for a query that's going to be off by a few fractions of a percentage point as soon as the query completes?

Certainly, not all data transactions will fit this looser model: for example, financial information systems involving debits, credits, and transfers will not be successful for very long if a debit from one account isn't carefully balanced by a credit to a different account. These, certainly, justify the cost of a lock. But a large category of situations comes up during application development where a given system can afford to be a bit out of sync with the data, particularly if it means that we reduce contention of the system overall.

As a result, most database systems support the idea of reducing transaction isolation, either through a straight SQL call or through the call-level interface, which in Java's case is JDBC. The following standard isolation levels, and the effects that can happen as a result, are available in standard SQL.

• SERIALIZABLE: None of the effects described above occur.

• REPEATABLE READ: This isolation level guarantees to maintain the same view of the database to the client—what data is there will remain there. It protects against dirty reads and nonrepeatable reads but allows phantom reads to take place.

• READ COMMITTED: This level allows transactions for this client to see the actions of other committed transactions and so protects against dirty reads, allowing nonrepeatable reads and phantom reads.

• READ UNCOMMITTED: This level protects against chaos, but that's it. It permits this user to see actions taken by other transactions that haven't committed (or possibly won't commit). It allows dirty reads, non repeatable reads, and phantom reads; in short, it allows pretty much anything short of outright corruption.

Note that the other three properties of transactions are still fully in force: a READ UNCOMMITTED isolation-level transaction is still atomic, still consistent, and still durable—it will either all succeed or all fail, it will always have the same effects on the database, and its actions will be stored when the transaction is complete. The only thing that has changed here is the transactions' visibility to the rest of the system and vice versa.

Remember that the benefit here is the reduced contention that lowered isolation levels permit. When executing update logic against the database directly, we will likely want that update to run in SERIALIZABLE isolation—that way, the update is sure that it works with the "right" set of data and can't be accidentally co-opted by a change to the underlying data. However, for queries, pick lists, and the like, turning down to just REPEATABLE READ isolation can help reduce locks against the system. For processes that need to pull vast quantities of data from the database at a predetermined time (batch processes that pull from this database to push into a different one, perhaps a data warehouse), running at READ COMMITTED keeps contention much lower, allowing users to continue to work against the database even as the batch is running. For the one-page executive summary report ("How much money did we make this quarter?") that involves a lot of database work but not a high degree of precision, use READ UNCOMMITTED. In fact, most reports can run at READ UNCOMMITTED without notice. (Try it—run one without it and one with, and see if you can spot the changes.)

Setting isolation levels at the SQL level involves using the SQL syntax:

SET TRANSACTION ISOLATION LEVEL <level>



<level> ::=

  READ UNCOMMITTED |

  READ COMMITTED |

  REPEATABLE READ |

  SERIALIZABLE

Be careful to do this outside of a transaction however (i.e., before executing the BEGIN TRANSACTION statement), as most database vendors promise "undefined behavior" if you do, a euphemism for "it'll blow in a big way, probably right in front of your boss during the big demo."

To do the same at the JDBC level, use the Connection method setTransactionIsolation, like this:

import java.sql.*;



public void run(Connection conn)

  throws SQLException

{

  conn.setTransactionIsolation(

    Connection.TRANSACTION_READ_UNCOMMITTED);

  // or TRANSACTION_READ_COMMITTED,

  // or TRANSACTION_REPEATABLE_READ,

  // or the default, TRANSACTION_SERIALIZABLE



  Statement stmt = conn.createStatement();



  // This statement will run at the isolation level

  // established in the lines above

}

Note that the transaction isolation is a Connection-level property, meaning that this is the setting for the entire database Connection, not just a Statement. And, as with the SQL syntax above, calling setTransaction Isolation in the middle of a transaction is "implementation defined," to quote the JDBC documentation, which again is a euphemism for "is almost guaranteed to embarrass you and/or your company at the big demo."

Unfortunately, having now just convinced you of the wonders of reduced transaction isolation, we come to the bad news: the EJB Specification, for the most part, punts entirely on the whole idea of supporting transaction isolation within the EJB container. Section 17.3.2 of the EJB 2.1 Specification states that:

The API for managing an isolation level is resource-manager specific. (Therefore, the EJB architecture does not define an API for managing isolation level.)...

For session beans and message-driven beans with bean-managed transaction demarcation, the Bean Provider can specify the desirable transaction isolation level programmatically in the enterprise bean's methods, using the resource-manager specific API.

For entity beans with container-managed persistence, transaction isolation is managed by the data access classes that are generated by the container provider's tools. The tools must ensure that the management of the isolation levels performed by the data access classes will not result in conflicting isolation level requests for a resource manager within a transaction.

Loosely translated, this boils down to a couple of simple points.

First, if you want to take advantage of the scalability benefits of lowered isolation levels, you're stuck with bean-managed transactions. This also means that you cannot use transaction isolation on entity beans, period, because the EJB Specification very clearly states that entity beans, regardless of whether you're using container-managed or bean-managed persistence, always run under container-managed transactions (Section 17.3.1). If your container doesn't provide for the local transaction optimization (see Item 32), then you're looking at a full, two-phase commit on each and every single entity bean access. You might try to work around this by setting your entity bean's transactional declaration to NotSupport, Supports, or Never; doing so, however, immediately renders your bean nonportable. Section 17.4.1, with emphasis added, states, "Containers may optionally support the user of the NotSupported, Supports, and Never transaction attributes for the methods of entity beans with container-managed persistence. However, entity beans with container-managed persistence that use these transaction attributes will not be portable." Ouch. Yet another reason to decide whether portability is important to you (see Item 11).

Second, if you want to take advantage of lowered isolation levels, you need to go to the resource manager's API to do so; for relational databases, this means you have to get at the JDBC Connection object and call setIsolationLevel on the Connection itself to turn it down. This isn't a real hardship; however, make sure you do this before you open the bean-managed transaction because, as we've already noted, changing isolation levels within a transaction is a fast way to a long debugging session.

Finally, however, be very, very careful if you do start turning down isolation levels in your bean-managed transaction beans because that last sentence in the specification is a killer—in essence, the container must make sure that differing isolation levels don't show up within a single transaction. However, the specification offers no hints as to how this rule should be enforced, so what happens if a bean-managed transaction session bean with lowered isolation level calls into an entity bean? There's no way to know without trying it for your particular container. The danger is that your container could lull you into thinking that you're safe because your container is silently handling the issue. Another container might not, and, of course, it's not until you port the application to that other container that the exceptions start to fly.