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:

http://www.postgresql.org/docs/8.1/static/transaction-iso.html

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.

Regards,
Thomas





Thomas,
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.

http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-set-transaction.html
> http://candle.pha.pa.us/main/writings/pgsql/sgml/transaction-iso.html

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. .



Relevant Pages

  • Re: Postgresql 8.1 released
    ... >>> Hi Thomas, ... because only one transaction can run at any time. ... This paper also describes transaction isolation levels for MVCC ... SNAPSHOT in Firebird isn't a SQL standard compliant REPEATBLE READ either. ...
    (borland.public.delphi.thirdpartytools.general)
  • Re: .Net COM+
    ... Yes it is fine to run middle tier objects outside of COM+. ... Declarative transaction management - you do not want to handle ... Sam Santiago ... "Thomas" wrote in message ...
    (microsoft.public.dotnet.distributed_apps)
  • Re: $200 isnt $100, liar.
    ... what did the source document or receipt of the transaction say? ... Paul A. Thomas, CPA ...
    (misc.taxes)
  • Re: determine existence of a transaction
    ... P. Thomas ... Net Asset Management ... > handling logic in my procedure that uses the rollback transaction function ... > if an error is found - however, this statement fails when the transaction> doesn't exist yet. ...
    (microsoft.public.sqlserver.programming)
  • Re: Getting transaction ID in trigger?
    ... your purpose. ... SQL Server MVP ... > is it possible to get the transaction id inside a trigger as a context ...
    (microsoft.public.sqlserver.programming)