Month: April 2004

To allow for concurrent access to a database and at the same time maintain ‘isolation’ between different concurrent transactions, most databases offer varying levels of Transaction Isolation.

The common isolation problems that can occur are:

• The Dirty Read:

  This occurs when Transaction A reads data from Transaction B that has not yet been committed. Transaction A performs some action based on this uncommitted data and then commits the new processed values. Tranaction B now rolls back it’s data. Transaction A’s processing although now complete used data that was uncommitted and now is in an undetermined state.

• The Unrepeatable Read:

  Transaction A reads data from the database to perform some processing. Transaction B at the same time is modifying the same data, but decides to rollback. Transaction A now re-reads the same data and gets different results from when it first read the data.

• The Phantom Read:

  Transaction A reads a set of data from the database to perform some processing. Transaction B at the same time has inserted some new rows (which appear in Transaction A’s results), but decides to rollback. Transaction A now re-reads the same data and gets a different results set, which now no longer contains row that were there previously.

Isolation Levels

To ensure that the problems discussed above are not encountered, most databases offer levels of locking which may include the following (these are the same levels used by Java JDBC and EJB Transaction Isolation on Entity Beans):

• READ UNCOMMITTED – offers no isolation, and Dirty Reads, Unrepeatable Reads, and Phantom Reads will still occur.
• READ COMMITTED – ensures that only committed data is read, and avoids Dirty Reads. Unrepeatable Reads and Phantom Reads will still occur
• REPEATABLE READ – ensures that subsequent queries of the same data return the same results. Avoids Dirt Reads, Unrepeatable Reads, but Phantom Reads still occur.
• READ SERIALIZABLE – ensures that Dirty, Unrepeatable or Phantom Reads do not occur.