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