Comments from a Firebird user via Borland Newsgroups.

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

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