eWebProgrammer eweb


Serialization and isolation levels

An isolation level defines how concurrent transactions accessing a RDBMS are isolated from one another for read purposes. The following isolation levels are generally supported in any RDBMS. These levels are defined in the ANSI SQL92 standard in terms of three possible occurrences that must be prevented between concurrent transactions.
  1. Dirty reads: A transaction reads a row in a database table containing uncommitted changes from another transaction.
  2. Nonrepeatable reads: A transaction reads a row in a database table, a second transaction changes the same row and the first transaction re-reads the row and gets a different value.
  3. Phantom reads: A transaction re-executes a query, returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition.
The table below lists the 5 isolation levels followed with a corresponding description .

Transaction Isolation Levels

Isolation Level Transactions Dirty Reads Non-Repeatable Reads Phantom Reads
TRANSACTION_NONE Not supported Not applicable Not applicable Not applicable
TRANSACTION_READ_COMMITTED Supported Prevented Allowed Allowed
TRANSACTION_READ_UNCOMMITTED Supported Allowed Allowed Allowed
TRANSACTION_REPEATABLE_READ Supported Prevented Prevented Allowed
TRANSACTION_SERIALIZABLE Supported Prevented Prevented Prevented
  2. TRANSACTION_READ_COMMITTED: This level results in the prevention of a transaction from reading uncommitted changes in other concurrent transactions. This level ensures that dirty reads are not possible.
  3. TRANSACTION_READ_UNCOMMITTED:The transaction can read uncommitted data, i.e., data being changed by another transaction concurrently.
  4. TRANSACTION_REPEATABLE_READ: In addition to the prevention associated with TRANSACTION_READ_COMMITTED, this level ensures that reading the same data multiple times will receive the same value even if another transaction modifies the data. Methods with this isolation level, besides having the same behavior as TRANSACTION_READ_COMMITTED, can only execute repeatable reads.
  5. TRANSACTION_SERIALIZABLE: The transaction has exclusive read and update privileges to data by locking it; other transactions can neither write nor read the same data. It is the most restrictive transaction isolation level and it ensures that if a query retrieves a result set based on a predicate condition and another transaction inserts data that satisfy the predicate condition, re-execution of the query will return the same result set.