Saturday, July 12, 2008

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:

  • 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

No comments: