Re: Pg isolation levels: 3 or 2?

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.
>
> [1] http://en.wikipedia.org/wiki/Snapshot_isolation

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