Re: Revisited: Transactions, insert unique.

Rex McMaster wrote in an email addressed to Ed Loehr and pgsql-general:
>
> After both commits, 2 rows are visible. Neither transactions can see
> effects of the other till both are commited.
>
> A:
> BEGIN;
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> INSERT INTO foo (id) SELECT 1 WHERE NOT EXISTS (SELECT * FROM foo WHERE id = 1);
> B:
> BEGIN;
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> INSERT INTO foo (id) SELECT 1 WHERE NOT EXISTS (SELECT * FROM foo WHERE id = 1);
> COMMIT;
> A:
> COMMIT;

While it is true that neither transaction sees the effect of the other,
the sequence of execution in postgresql depicted above is not
serializable. There is no serial execution of the two transactions,
neither A followed by B, nor B followed by A, that would produce the same
results as the interleaved execution of the two transactions.

The fact that NEITHER transaction sees ANY effect of the other is
precisely the problem! One SHOULD see the effects of the other. The
outcome should be equivalent to one occuring entirely before the other.

With a concurrancy control implementation based on locks one transaction
would see the effect of the other by being blocked at a suitable point, or
by being terminated with a deadlock exception.

Joachim

--
joachim(at)kraut(dot)bc(dot)ca (http://www.kraut.bc.ca)
joachim(at)mercury(dot)bc(dot)ca (http://www.mercury.bc.ca)