Isolation (computer science)


Isolation (computer science) - Definition

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.

Contents

1 Isolation Levels

2 Example Queries

2.1 Phantom Reads
2.2 Non-repeatable Reads
2.3 Dirty Reads

3 See also

Isolation Levels

The Isolation property is the most often relaxed ACID property in a DBMS. This is because to maintain isolation a DBMS must aquire locks on returned data, which can involve a lot of overhead for the DBMS and thread contention for the locks.

Most DBMS's offer a number of transation 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. Conversley, 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 completly isolated fasion. This isolation level requires that range locks are aquired when a query uses a ranged WHERE statement, 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 aquires read locks on all retrieved data, but does not aquire 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 aquired 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 DBMS systems varies quite widley. Most transactional databases which feature transactions allow the user to set any isolation level. Some DBMS systems also require additional syntax when performing a SELECT statement which is to aquire 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.

users
id	name	age
1	Joe	20
2	Jill	25

Phantom Reads

The phantom reads phenomenon occours when range locks are not aquired 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 it's results.

Non-repeatable Reads

Non-repeatable reads occur when read locks are not aquired 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)