RE: Revisited: Transactions, insert unique.

> -----Original Message-----
> From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
> Behalf Of Ed Loehr
>
> >
> > Have to read up on the documentation, which I thought was claiming
> > serializability.
>
> Hmmm.  This surprised me, too.  The serialization doesn't work as I
> expected it to.  Maybe someone can illuminate.  Here's what confused me...
>
> The documentation says:
>
>     Serializable provides the highest transaction isolation.
>     When a transaction is on the serializable level, a query
>     sees only data committed before the transaction began and
>     never see either dirty data or concurrent transaction
>     changes committed during transaction execution. So, this
>     level emulates serial transaction execution, as if
>     transactions would be executed one after another, serially,
>     rather than concurrently.
>     - http://www.postgresql.org/docs/postgres/mvcc4508.htm (4/25/2000)
>
> But the following sequence seems to contradict this:
>
> -- Transaction A
> DROP TABLE foo;
> CREATE TABLE foo (id INTEGER);
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> BEGIN;
> -- Transaction B
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> BEGIN;
> INSERT INTO foo (id) SELECT 1 WHERE NOT EXISTS (SELECT * FROM foo WHERE id
> = 1);
> COMMIT;
> SELECT * FROM foo;
> -- Transaction A
> SELECT * FROM foo;
> INSERT INTO foo (id) SELECT 1 WHERE NOT EXISTS (SELECT * FROM foo WHERE id
> = 1);
> SELECT * FROM foo;
> COMMIT;
>
> This sequence allows B's inserts, AFTER A began, to be seen by A
> (at least,
> in pgsql 7.0beta3).
>
> Anyone understand what's going on here?  Bug?
>

You should call SET TRANSACTION immediately after BEGIN.
Note that SET TRANSACTION .. is per transaction command.

PostgreSQL's SERIALIZABLE isolation level would allow both inserts.
READ COMMITED isolation level wouldn't allow A's inserts.

As I mentioned in another posting,PostgreSQL's SERIALIZABLE
isolation level isn't completely serializable and it's same as Oracle.
Probably Vadim refers to this incomplete serializability somewhere
in documentation.
It seems almost impossible to implement complete serializability
under MVCC without using table level locking. I love MVCC much
more than theoretically beautiful complete serializability.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp