Thread: Comments from a Firebird user via Borland Newsgroups.

Comments from a Firebird user via Borland Newsgroups.

From
Tony Caduto
Date:
<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


Re: Comments from a Firebird user via Borland Newsgroups.

From
Rod Taylor
Date:
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)

-- 



Re: Comments from a Firebird user via Borland Newsgroups.

From
Bruno Wolff III
Date:
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


Re: Comments from a Firebird user via Borland Newsgroups.

From
Tom Lane
Date:
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


Re: Comments from a Firebird user via Borland Newsgroups.

From
Tony Caduto
Date:
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


Re: Comments from a Firebird user via Borland Newsgroups.

From
"Marc G. Fournier"
Date:
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


Re: Comments from a Firebird user via Borland Newsgroups.

From
Tom Lane
Date:
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