Database Transaction Isolation

The isolation property is the most often relaxed ACID property because the DBMS must acquire locks on data which may result in a loss of concurrency. Relaxation of isolation can cause difficult-to-find bugs, but higher isolation levels increase the possibility of deadlocks.

The isolation levels defined by the ANSI/ ISO SQL standard are:

Serializable

• Specifies that all transactions occur in a completely isolated fashion
• May execute two or more transactions at the same time only if the illusion of serial execution can be maintained
• Requires that range locks are acquired when a query uses a ranged WHERE clause

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 occur

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

Read Uncommited

• Dirty reads are allowed therefore one transaction may see uncommitted changes made by some other transaction

See: Isolation