Re: 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(at)gmail(dot)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] 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