Database isolation level quick note

• Reference: http://en.wikipedia.org/wiki/Isolation_%28database_systems%29

Isolation Levels, Read Phenomena and Locks

[edit] Isolation Levels vs Read Phenomena

Isolation level Dirty reads Non-repeatable reads Phantoms Read Uncommitted may occur may occur may occur Read Committed - may occur may occur Repeatable Read - - may occur Serializable - - -

"may occur" means that the isolation level suffers that phenomenon, while "-" means that it does not suffer it.

[edit] Isolation Levels vs Locks

Isolation level	Write Lock	Read Lock	Range Lock
Read Uncommitted	-	-	-
Read Committed	V	-	-
Repeatable Read	V	V	-
Serializable	V	V	V

"V" indicates that the method locks for that operation, keeping that lock till the end of the transaction containing that operation.
Note: Read (i.e. SELECT) operations can acquire read (shared) locks in the Read Committed isolation level, but they are released immediately after the read operation is performed.

• Reference: http://msdn.microsoft.com/en-us/library/ms173763.aspx 
• Sql server 2008 isolation level:

  SET TRANSACTION ISOLATION LEVEL
      { READ UNCOMMITTED
      | READ COMMITTED
      | REPEATABLE READ
      | SNAPSHOT
      | SERIALIZABLE
      }
  [ ; ]

  SERIALIZABLE

  Specifies the following:
  

  • (READ COMMITTED) no dirty read
    Statements cannot read data that has been modified but not yet committed by other transactions.
    
  • (REPEATABLE READ)
    No other transactions can modify data that has been read by the current transaction until the current transaction completes.
    
  • (No Phantom Read) Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.
    

  Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction.

ACID (atomicity, consistency, isolation, durability)