Pg isolation levels: 3 or 2?
Hi all.
From the current docs (Ch.13 on Concurreny Control):
"In PostgreSQL, you can request any of the four standard transaction
isolation levels. But internally, there are only three distinct isolation
levels, which correspond to the levels Read Committed, Repeatable Read, and
Serializable. When you select the level Read Uncommitted you really get
Read Committed, and phantom reads are not possible in the
PostgreSQLimplementation of Repeatable Read, so the actual isolation
level might be
stricter than what you select."
How is that three levels and not two? Read Uncommitted and Read Commited
are the same. And Repeatable Reads don't allow phantom reads thus making
them effectively the same as Serializable. No?
TIA,
Thalis K.
On 3 November 2012 23:10, Thalis Kalfigkopoulos <tkalfigo@gmail.com> wrote:
How is that three levels and not two? Read Uncommitted and Read Commited are
the same. And Repeatable Reads don't allow phantom reads thus making them
effectively the same as Serializable. No?
They're only equivalent to the extent that the SQL standard describes
the isolation levels (in terms of various anomalies that can or cannot
occur, including phantom reads). However, the SQL standard has nothing
to say about write-skew anomalies, which can introduce errors that are
not possible with actually serially executing transactions. The SQL
standard and every implementation other than Postgres don't completely
"plug the leaks" in the illusion of serial behaviour with snapshot
isolation/Postgres repeatable reads. The Wikipedia article on Snapshot
Isolation [1]http://en.wikipedia.org/wiki/Snapshot_isolation may be informative here.
[1]: http://en.wikipedia.org/wiki/Snapshot_isolation
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
Peter Geoghegan wrote:
Thalis Kalfigkopoulos <tkalfigo@gmail.com> wrote:
How is that three levels and not two? Read Uncommitted and Read
Commited are the same. And Repeatable Reads don't allow phantom
reads thus making them effectively the same as Serializable. No?
No.
They're only equivalent to the extent that the SQL standard
describes the isolation levels (in terms of various anomalies that
can or cannot occur, including phantom reads).
That is a commonly-held belief, but section 4.28 ("SQL-transactions")
of the SQL-92 standard says:
The execution of concurrent SQL-transactions at isolation level
SERIALIZABLE is guaranteed to be serializable. A serializable exe-
cution is defined to be an execution of the operations of concur-
rently executing SQL-transactions that produces the same effect as
some serial execution of those same SQL-transactions. A serial exe-
cution is one in which each SQL-transaction executes to completion
before the next SQL-transaction begins.
The table of which phenomena are possible at which transaction
isolation levels shows that none of them are possible for
serializable transactions, which is what people seem to focus on to
come to the conclusion that lack of the phenomena is sufficient to
meet the requirements, even though directly below the table is this:
Note: The exclusion of these phenomena for SQL-transactions ex-
ecuting at isolation level SERIALIZABLE is a consequence of the
requirement that such transactions be serializable.
In other words, they are emphasizing that table is *not* the
*definition* of the serializable transaction isolation level. Similar
language has been in every subsequent version of the standard.
There's no need to take anyone else's word for it -- look at any of
the drafts of the standard pointed at by the PostgreSQL Developer FAQ
and read it for yourself:
http://wiki.postgresql.org/wiki/Developer_FAQ#Where_can_I_get_a_copy_of_the_SQL_standards.3F
Better yet, check an official copy of the standard if you have
access.
However, the SQL standard has nothing to say about write-skew
anomalies, which can introduce errors that are not possible with
actually serially executing transactions.
Since the standard says that any set of serializable transactions
must produce the same effect as some one-at-a-time execution of those
transactions, and write skew is not something which can happen if
transactions are run one at a time, it really does disqualify
snapshot isolation as a conforming implementation.
The SQL standard and every implementation other than Postgres don't
completely "plug the leaks" in the illusion of serial behaviour
with snapshot isolation/Postgres repeatable reads. The Wikipedia
article on Snapshot Isolation [1] may be informative here.
Further down the page the OP quoted, there are descriptions of the
levels, which should help clear things up. Additional practical
examples can be found in the Wiki:
http://wiki.postgresql.org/wiki/SSI
-Kevin
Import Notes
Resolved by subject fallback