Transaction isolation level

Imagine a multi-user system where user A is performing an some updates in a table in a transactional context while another user B is trying to read from it. Some problems arise, for example: should B be able to read uncommitted updates by A? This is an important issue because B could get an inconsistent view on the tables.

These types of inconsistensies could occur:

  • dirty-read: A has changed a row in the table, but hasn’t committed yet. B reads it, but the data could in fact be non-existent, since A may still roll back later.
  • non-repeatable read: B performs a read, but A modifies or deletes it during its transaction. If B reads the same row again, it will get no or different results.
  • phantoms: A does a query on a set of rows to perform an operation. B modifies the table such that the query of A would have given a different result. The table may be left inconsistent.

The transaction isolation level is used to solve these issues. The default isolation level is TRANSACTION_SERIALIZABLE, where all of the aforementioned inconsistensies will not occur. This is excellent where data integrity is crucial. However, because of locking, it has low performance.

The other options are documented in the API, interface java.sql.Connection:

static int TRANSACTION_READ_COMMITTED 
          Dirty reads are prevented; non-repeatable reads and phantom reads can occur. 
static int TRANSACTION_READ_UNCOMMITTED 
          Dirty reads, non-repeatable reads and phantom reads can occur. 
static int TRANSACTION_REPEATABLE_READ 
          Dirty reads and non-repeatable reads are prevented; phantom reads can occur. 
static int TRANSACTION_SERIALIZABLE 
          Dirty reads, non-repeatable reads and phantom reads are prevented. 

You can set the transaction isolation level with the method setTransactionIsolation on a particular connection:

   conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);