Transaction Isolation

The SQL standard defines four levels of transaction isolation in terms of three phenomena that must be prevented between concurrent transactions. These undesirable phenomena are:



dirty read
A transaction reads data written by a concurrent uncommitted transaction.

nonrepeatable read
A transaction re-reads data it has previously read and finds that data has been modified by another transaction (that committed since the initial read).

phantom read
A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction.

Table 11-1. SQL Transaction Isolation Levels

Isolation Level Dirty Read Nonrepeatable Read Phantom Read
Read uncommitted Possible Possible Possible
Read committed Not possible Possible Possible
Repeatable read Not possible Not possible Possible
Serializable Not possible Not possible Not possible

In EnterpriseDB, you can use all four possible transaction isolation levels. Internally, there are only two distinct isolation levels, which correspond to the levels Read Committed and Serializable. When you select the level Read Uncommitted you actually get Read Committed, and when you select Repeatable Read you really get Serializable, so the actual isolation level may be stricter than what you select. This is permitted by the SQL standard: the four isolation levels only define which phenomena must not happen, they do not define which phenomena must happen. The reason that EnterpriseDB only provides two isolation levels is that this is the only sensible way to map the standard isolation levels to the multiversion concurrency control architecture.


http://www.enterprisedb.com/documentation/8.2/transaction-iso.html