Transaction isolation levels

Database anomalies

Database anomalies are generated results that seem incorrect when looked at from the scope of a single transaction, but are correct when looked at from the scope of all transactions. The different types of database anomalies are described as follows:

• Dirty reads occur when:
  1. Transaction A inserts a row into a table.
  2. Transaction B reads the new row.
  3. Transaction A rolls back.

  Transaction B may have done work to the system based on the row inserted by transaction A, but that row never became a permanent part of the database.

• Nonrepeatable reads occur when:
  1. Transaction A reads a row.
  2. Transaction B changes the row.
  3. Transaction A reads the same row a second time and gets the new results.
• Phantom reads occur when:
  1. Transaction A reads all rows that satisfy a WHERE clause on an SQL query.
  2. Transaction B inserts an additional row that satisfies the WHERE clause.
  3. Transaction A re-evaluates the WHERE condition and picks up the additional row.

JDBC transaction isolation levels

There are five levels of transaction isolation in the IBM® Developer Kit for Java™ JDBC API. Listed from least to most restrictive, they are as follows:

JDBC_TRANSACTION_NONE

This is a special constant indicating that the JDBC driver does not support transactions.

JDBC_TRANSACTION_READ_UNCOMMITTED

This level allows transactions to see uncommitted changes to the data. All database anomalies are possible at this level.

JDBC_TRANSACTION_READ_COMMITTED

This level means that any changes made inside a transaction are not visible outside it until the transaction is committed. This prevents dirty reads from being possible.

JDBC_TRANSACTION_REPEATABLE_READ

This level means that rows that are read retain locks so that another transaction cannot change them when the transaction is not completed. This disallows dirty reads and nonrepeatable reads. Phantom read are still possible.

JDBC_TRANSACTION_SERIALIZABLE

Tables are locked for the transaction so that WHERE conditions cannot be changed by other transactions that add values to or remove values from a table. This prevents all types of database anomalies.

The setTransactionIsolation method can be used to change the transaction isolation level for a connection.

Considerations

A common misinterpretation is that the JDBC specification defines the five transactional levels previously mentioned. It is commonly thought that the TRANSACTION_NONE value represents the concept of running without commitment control. The JDBC specification does not define TRANSACTION_NONE in the same manner. TRANSACTION_NONE is defined in the JDBC specification as a level where the driver does not support transactions and is not a JDBC-compliant driver. The NONE level is never reported when the getTransactionIsolation method is called.

The issue is marginally complicated by the fact that a JDBC driver's default transaction isolation level is defined by the implementation. The default level of transaction isolation for the native JDBC driver default transaction isolation level is NONE. This allows the driver to work with files that do not have journals and you are not required to make any specifications such as files in the QGPL library.

The native JDBC driver allows you to pass JDBC_TRANSACTION_NONE to the setTransactionIsolation method or specify none as a connection property. However, the getTransactionIsolation method always reports JDBC_TRANSACTION_READ_UNCOMMITTED when the value is none. It is your application's responsibility to keep track of what level you are running if it is a requirement in your application.

In past releases, the JDBC driver would handle your specifying true for auto-commit by changing the transaction isolation level to none because the system did not have a concept of a true auto-commit mode. This was a close approximation of the functionality, but did not provide the correct results for all scenarios. This is not done anymore; the database decouples the concept of auto-commit from the concept of a transaction isolation level. Therefore, it is completely valid to run at the JDBC_TRANSACTION_SERIALIZABLE level with auto-commit being enabled. The only scenario that is not valid is to run at the JDBC_TRANSACTION_NONE level and not be in auto-commit mode. Your application cannot take control over commit boundaries when the system is not running with a transaction isolation level.

Transaction isolation levels between the JDBC specification and the IBM i platform

The IBM i platform has common names for its transaction isolation levels that do not match those names provided by the JDBC specification. The following table matches the names used by the IBM i platform, but are not equivalent to those used by the JDBC specification:

* In this table, the JDBC_TRANSACTION_NONE value is lined up with the IBM i levels *NONE and *NC for clarity. This is not a direct specification-to-IBM i level match.