Transactions::Transaction Isolation Levels

Transaction Isolation Levels

Transaction isolation levels specify what data is “visible” to the statements within a
transaction. They directly impact the level of concurrent access by defining what
interaction, if any, is possible between transactions against the same target data
source. Possible interaction between concurrent transactions is categorized as
follows:

• dirty reads occur when transactions are allowed to see uncommitted changes to the data. In other words, changes made inside a transaction are visible outside the transaction before it is commited. If the changes are rolled back instead of being committed, it is possible for other transactions to have done work based on incorrect, transient data.
  

• nonrepeatable reads occur when:

a. Transaction A reads a row
b. Transaction B changes the row
c. Transaction A reads the same row a second time and gets different results

• phantom reads occur when:

a. Transaction A reads all rows that satisfy a WHERE condition
b. Transaction B inserts an additional row that satisfies the same condition
c. Transaction A reevaluates the WHERE condition and picks up the additional “phantom” row

JDBC augments the four levels of transaction isolation defined by SQL99, by adding
TRANSACTION_NONE. From least restrictive to most restrictive, the transaction
isolation levels are:

1. TRANSACTION_NONE — indicates that the driver does not support transactions,
which means that it is not a JDBC compliant driver.
2. TRANSACTION_READ_UNCOMMITTED — allows transactions to see uncommitted
changes to the data. This means that dirty reads, nonrepeatable reads, and
phantom reads are possible.
3. TRANSACTION_READ_COMMITTED — means that any changes made inside a
transaction are not visible outside the transaction until the transaction is
committed. This prevents dirty reads, but nonrepeatable reads and phantom
reads are still possible.
4. TRANSACTION_REPEATABLE_READ — disallows dirty reads and nonrepeatable
reads. Phantom read are still possible.
5. TRANSACTION_SERIALIZABLE — specifies that dirty reads, nonrepeatable reads,
and phantom reads are prevented.

Using the setTransactionIsolation Method

The default transaction level for a Connection object is determined by the driver
supplying the connection. Typically, it is the default transaction level supported by
the underlying data source.

The Connection method setTransactionIsolation is provided to allow JDBC clients to change the transaction isolation level for a given Connection object. The new isolation level remains in effect for the remainder of the session or until the next invocation of the setTransactionIsolation method. The result of invoking the method setTransactionIsolation in the middle of a transaction is implementation-defined. The return value of the method getTransactionIsolation should reflect the change in isolation level when it actually occurs. It is recommended that drivers implement the setTransactionIsolation method to change the isolation level
starting with the next transaction. Committing the current transaction to make the effect immediate is also a valid implementation. It is possible for a given JDBC driver to not support all four transaction isolation levels (not counting TRANSACTION_NONE). If a driver does not support the isolation level specified in an invocation of setTransactionIsolation, it is allowed to
substitute a higher, more restrictive transaction isolation level. If a driver is unable to substitute a higher transaction level, it throws an SQLException. The DatabaseMetaData method supportsTransactionIsolationLevel may be used to determine whether or not the driver supports a given level.

Performance Considerations

As the transaction isolation level increases, more locking and other DBMS overhead is required to ensure the correct semantics. This in turn lowers the degree of concurrent access that can be supported. As a result, applications may see decreased performance when they use a higher transaction isolation level. For this reason, the transaction manager, whether it is the application itself or part of the application server, should weigh the need for data consistency against the requirements for performance when determining which transaction isolation level is appropriate.