isolation (computer science)

In database systems, isolation is a property that the changes made by an operation are not visible to other simultaneous operations on the system until its completion. This is one of the ACID properties.

Isolation Levels

The Isolation property is the most often relaxed ACID property in a DBMS. This is because to maintain isolation a DBMS must acquire locks on returned data, which can involve a lot of overhead for the DBMS and thread contention for the locks. Most DBMSs offer a number of transaction isolation levels which control the degree of locking which occurs when selecting data. For many database applications the majority of database transactions can be constructed in such a way as to not require high isolation levels, thus reducing the locking overhead for the system. The programmer must carefully analyse database access code to ensure that any relaxation of isolation does not cause difficult-to-find software bugs. Conversely, at higher isolation levels the possibility of deadlock is increased, which also requires careful analysis and programming techniques to avoid. The isolation levels available include:

SERIALIZABLE: This isolation level specifies that all transactions occur in an completely isolated fashion. This isolation level requires that range locks are acquired when a query uses a ranged WHERE clause, and prevents phantom reads.
REPEATABLE READ: All data records retrieved by a SELECT statement cannot be changed, however if the SELECT statement contains any ranged where clauses, phantom reads may occour. In this isolation level the transaction acquires read locks on all retrieved data, but does not acquire range locks.
READ COMMITTED: Data records retrieved by a query are not prevented from modification by some other transaction. Non-repeatable reads may occur, meaning data retrieved in a SELECT statement may be modified by some other transaction when it commits. In this isolation level, read locks are not acquired on selected data.
READ UNCOMMITTED: In this isolation level, dirty reads are allowed. One transaction may see uncommitted changes made by some other transaction.

The default isolation level of different DBMSs varies quite widely. Most databases which feature transactions allow the user to set any isolation level. Some DBMSs also require additional syntax when performing a SELECT statement which is to acquire locks.

Example Queries

In these examples two transactions take place. In the first transaction, Query 1 is performed, then Query 2 is performed in the second transaction and the transaction committed, followed by Query 1 is being performed again in the first transaction. The queries use the following data table.

id !! name !! age
1	Joe	20
2	Jill	25

Phantom Reads

The phantom reads phenomenon occurs when range locks are not acquired on performing a SELECT.

  /* Query 1 */  SELECT * FROM users WHERE age BETWEEN 10 AND 30;

  /* Query 2 */  INSERT INTO users VALUES ( 3, Bob, 27 );

In the SERIALIZABLE isolation mode, Query 1 would result in all records with age in the range 10 to 30 being locked, thus Query 2 would block until the first transaction was committed. In REPEATABLE READ mode, the range would not be locked, allowing the record to be inserted and the second execution of Query 1 to return the new row in its results.

Non-repeatable Reads

Non-repeatable reads occur when read locks are not acquired on performing a SELECT.

  /* Query 1 */  SELECT * FROM users WHERE id = 1;

  /* Query 2 */  UPDATE users SET age = 21 WHERE id = 1;

In the REPEATABLE READ isolation mode, the row with ID = 1 would be locked, thus blocking Query 2 until the first transaction was committed. In READ COMMITTED mode the second time Query 1 was executed the age would have changed.

Dirty Reads

Dirty reads works similarly to non-repeatable reads, however the second transaction would not need to be committed for the first query to return a different result. The only thing prevented in the READ UNCOMMITTED mode is that updates will not appear in the results out of order; that is, earlier updates will always appear in a result set before later updates. (nb: this needs to be checked)