Isolation Levels

The following phenomena are commonly used to characterize isolation levels:

• Dirty Read—Transaction A changes a row. Transaction B reads the changed row before transaction A commits the change. If transaction A aborts the change, transaction B will have read a row that is considered to have never existed.
  
  
• Nonrepeatable read—Transaction A reads a row. Transaction B updates or deletes that row and commits this change. If transaction A attempts to reread the row, it will receive different row values or discover that the row has been deleted.
  
  
• Phantom—Transaction A reads a set of rows that satisfy some search criteria. Transaction B inserts a row that matches the search criteria. If transaction A re-executes the statement that read the rows, it receives a different set of rows.

According to these phenomena, the isolation levels defined by OLE DB are as follows:

• Read Uncommitted (Browse)—A transaction operating at the Read Uncommitted level can see uncommitted changes made by other transactions. At this level of isolation, dirty reads, nonrepeatable reads, and phantoms are all possible.
  
  
• Read Committed (Cursor Stability)—A transaction operating at the Read Committed level cannot see changes made by other transactions until those transactions are committed. At this level of isolation, dirty reads are not possible, but nonrepeatable reads and phantoms are possible.
  
  
• Repeatable Read—A transaction operating at the Repeatable Read level is guaranteed not to see any changes made by other transactions in values it has already read. At this level of isolation, dirty reads and nonrepeatable reads are not possible, but phantoms are possible.
  
  
• Serializable (Isolated)—A transaction operating at the Serializable level guarantees that all concurrent transactions will interact only in ways that produce the same effect as if each transaction were entirely executed one after the other. At this isolation level, dirty reads, nonrepeatable reads, and phantoms are not possible.