Thread: Comments from a Firebird user via Borland Newsgroups.
<We found PostgreSQL a mature product, but in two things Firebird was simply better than PostgreSQL: 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?> Just wondering what the PG take on this snapshot repeatable read stuff is. Tony
On Wed, 2005-11-09 at 19:35 -0600, Tony Caduto wrote: > <We found PostgreSQL a mature product, but in two things Firebird was > simply better than PostgreSQL: 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?> > > Just wondering what the PG take on this snapshot repeatable read stuff is. It has kinda been there for years and is what PostgreSQL uses to achieve a consistent snapshot with pg_dump. Of course, per spec the DB is allowed to upgrade the isolation level to SERIALIZABLE from what you specify you require as a minimum (REPEATABLE READ in this case). session1: begin isolation level repeatable read; session2: insert into junk values (1); session1: rbt=# select * from junk; col ----- 1 (1 row) session2: insert into junk values (2); session1: rbt=# select * from junk; col ----- 1 (1 row) --
On Wed, Nov 09, 2005 at 19:35:30 -0600, Tony Caduto <tony_caduto@amsoftwaredesign.com> wrote: > <We found PostgreSQL a mature product, but in two things Firebird was > simply better than PostgreSQL: 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?> > > Just wondering what the PG take on this snapshot repeatable read stuff is. 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
Bruno Wolff III <bruno@wolff.to> writes: > On Wed, Nov 09, 2005 at 19:35:30 -0600, > Tony Caduto <tony_caduto@amsoftwaredesign.com> wrote: >> <We found PostgreSQL a mature product, but in two things Firebird was >> simply better than PostgreSQL: 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?> >> >> Just wondering what the PG take on this snapshot repeatable read stuff is. > 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 It's a bit amusing that this person is dissing us for not having REPEATABLE READ, when 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. regards, tom lane
Tom Lane wrote: >> 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 >> > > It's a bit amusing that this person is dissing us for not having > REPEATABLE READ, when 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. > > regards, tom lane > > Tom, This is what the firebird guy said: > Serializable is stricter and somehwat unusable in a multi-user, loaded> database, because only one transaction can runat any time. Let's say you> would have one long running serializable transaction encapsulating a> reporting query, this will cause other transactionsto 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 transactionisolation levels for MVCC> databases. The paper is from 1995. http://www.cs.duke.edu/~junyang/courses/cps216-2003-spring/papers/berenson-etal-1995.pdf>> SNAPSHOT in Firebird isn't a SQLstandard compliant REPEATBLE READ either.> SNAPSHOT in Firebird is between REPEATABLE READ and SERIALIZABLE, but> without blocking other transactions. Is this true? will SERIALIZABLE block all transactions on the whole server, or just on that one connection? Thanks, Tony
On Thu, 10 Nov 2005, Tony Caduto wrote: >> 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 transactions 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. > http://www.cs.duke.edu/~junyang/courses/cps216-2003-spring/papers/berenson-etal-1995.pdf >> >> 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. > > Is this true? will SERIALIZABLE block all transactions on the whole server, > or just on that one connection? I don't believe so ... my understanding was that MVCC took care of any blocking issues, since we are looking at a 'snapshot' or 'layer' of data, based on the time you started the transaction ... other transactions can still work on data while the SERIALIZABLE transaction is going on ... The way I've thought about it is akin to going to a cash register to pay for groceries ... you don't want prices to change part way through the cashier ringing up your bill, but you also don't want to have the office shut everyone off while they update the price list ... so the cash register would be running the 'bill tally' in a SERIALIZABLE transaction, so that the prices are based on when (s)he started to ring things up ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:
> Tom,
> This is what the firebird guy said:
>>> Serializable is stricter and somehwat unusable in a multi-user, loaded
>>> database, because only one transaction can run at any time.
He's already demonstrated that he has no clue what he's talking about,
so I think you can discount the rest ;-)
Serializability means that the database has to *give the illusion* of
one-at-a-time execution, not that it must actually do things that way.
Certainly we don't do things that way. See the extensive discussion in
the MVCC chapter of our docs.
regards, tom lane