|The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 454 pages
RRP £14.95 ($19.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
11.2 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.
The four transaction isolation levels and the corresponding behaviors are described in Table 11-1.
|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 PostgreSQL, you can request any of the four standard transaction isolation levels. But 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 really get Read Committed, and when you select Repeatable Read you really get Serializable, so the actual isolation level might 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 PostgreSQL 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. The behavior of the available isolation levels is detailed in the following subsections.
To set the transaction isolation level of a transaction, use the
|ISBN 9781906966041||The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference||See the print edition|