Re: Postgresql 8.1 released

Thomas Steinmaurer wrote:
Hi Tony,

 >Two-Phase commit (ok, that is gone with PG 8.1),

but the second is a SNAPSHOT / REPEATABLE READ transaction isolation. I can't live without that when it comes having a stable view of data during one transaction, or did that change with 8.1? Is there now a SNAPHOST / REPEATBLE READ transaction isolation level available as well?

Hi Thomas,
Here is the docs on that for 8.1:

Not sure if serialized is the same as repeatable.

It isn't.

I think it is, see here for more info:

I don't think so. In your other message you've quoted something from the manual:

"...when you select Repeatable Read you really get Serializable, so the
actual isolation level may be stricter than what you select ..."

Serializable is stricter and somehwat unusable in a multi-user, loaded database, because only one transaction can run at any time. Let's say you would have one long running serializable transaction encapsulating a reporting query, this will cause other transaction to wait.

There is a pretty good paper on discussing why it was a somewhat bad idea to describe transaction isolation levels in terms of phenomena in the SQL standard. This paper also describes transaction isolation levels for MVCC databases. The paper is from 1995, but I can't find it right now.

SNAPSHOT in Firebird isn't a SQL standard compliant REPEATBLE READ either. SNAPSHOT in Firebird is between REPEATABLE READ and SERIALIZABLE, but without blocking other transactions.


I don't think that occurs in Postgresql because it is process based,
this might be true of a threaded solution, but in PG your connection
would be the only one prevented from starting another transaction, not
the whole server.

Below is a qoute from one of the PG developers.

what he actually seems to want is SERIALIZABLE (which we've had since 1999). Certainly REPEATABLE READ does *not* guarantee a "stable view of data during one transaction" --- see the discussion of phantom reads in the second link given above.

Maybe you should join the PG Hackers mailing list, they would be able to explain this whole thing a bit better than me. .