Re: Comments from a Firebird user via Borland

Hi Tony,

As the referenced documentation states, the PostgreSQL SERIALIZABLE
transaction isolation level complies with the ANSI/ISO requirements, but
not with a mathematically pure interpretation of the term.  (The only
quibble I have with that documentation is that you have to be averting
your eyes to not find several commercial products which do enforce the
stricter interpretation.)

As far as I can see, the difference is only significant if you need to
have two concurrent transactions where one transaction is selecting
from a set of data A to modify something within a set of data B at the
same time that another transaction is selecting from B to modify
something within A -- without any overlap between the rows updated
by the transactions.  In practice, this seems unlikely to be meaningful
outside of some theoretical science; you don't normally want recursive
redundancies in your database.

So to address the original concern -- PostgreSQL absolutely gives
you a stable view of the data during a SERIALIZABLE transaction.
The only thing it doesn't give you is a guarantee that some other
transaction hasn't made modifications which would change what the
same SELECTs would show if you were to start a NEW transaction.

-Kevin


>>> Tony Caduto <tony_caduto@amsoftwaredesign.com>  >>>
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
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 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
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