MDAC 2.5 SDK - OLE DB Programmer's Reference
Chapter 15: Transactions

Isolation Levels

Transaction isolation levels are a measure of the extent to which changes made outside a transaction are visible to that transaction. In particular, transaction isolation levels are defined by the presence or absence of the following phenomena:

• Dirty read—A dirty read occurs when a transaction reads data that has not yet been committed. For example, suppose transaction 1 changes a row. Transaction 2 reads the changed row before transaction 1 commits the change. If transaction 1 aborts the change, transaction 2 will have read data that is considered to have never existed.
  
  
• Nonrepeatable read—A nonrepeatable read occurs when a transaction reads the same row twice but gets different data each time. For example, suppose transaction 1 reads a row. Transaction 2 changes or deletes that row and commits this change or deletion. If transaction 1 attempts to reread the row, it retrieves different row values or discovers that the row has been deleted.
  
  
• Phantom—A phantom is a row that matches the search criteria but is not initially seen. For example, suppose transaction 1 reads a set of rows that satisfy some search criteria. Transaction 2 inserts a new row that matches the search criteria for transaction 1. If transaction 1 reexecutes the statement that read the rows, it gets a different set of rows.

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

• Read Uncommitted (also called 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 (also called 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 (also called Isolated)—A transaction operating at the Serializable level guarantees that all concurrent transactions 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.