Re: Revisited: Transactions, insert unique.

Sorry, mistake in my previous email -

> - only 1 row inserted

this was before the second commit. After both commits, 2 rows are
visible. Neither transactions can see effects of the other till both
are commited.

A: CREATE TABLE foo (id INTEGER);
===> CREATE
BEGIN;
===> BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
===> SET VARIABLE
INSERT INTO foo (id) SELECT 1 WHERE NOT EXISTS (SELECT * FROM foo WHERE id = 1);
===> INSERT 959179 1
B: BEGIN;
===> BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
===> SET VARIABLE
SELECT * FROM foo;
===> 0 rows
A: SELECT * FROM foo;
===> 1 rows <== the row inserted in A:
B: INSERT INTO foo (id) SELECT 1 WHERE NOT EXISTS (SELECT * FROM foo WHERE id = 1);
===> INSERT 959155 1
SELECT * FROM foo;
===> 1 rows <== the row inserted in B:
A: SELECT * FROM foo;
===> 1 rows
B: COMMIT;
===> END
SELECT * FROM foo;
===> 1 rows
A: SELECT * FROM foo;
===> 1 rows
COMMIT;
===> END
SELECT * FROM foo;
===> 2 rows
B: SELECT * FROM foo;
===> 2 rows

Ed Loehr writes:
> rmcm(at)compsoft(dot)com(dot)au wrote:
> >
> > Doesn't
> >
> > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> >
> > have to come within transaction - ie
> >
> > BEGIN;
> > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> >
> > In this order your test seems to behave correctly - only 1 row inserted.
>
> Yes, my initial ordering was in error. But even after reordering, the
> point is not that only 1 row was inserted, but rather that Transaction A
> was able to see the effects of transaction B when it clearly should not.
>
> Regards,
> Ed Loehr

--
Rex McMaster rex(at)mcmaster(dot)wattle(dot)id(dot)au
http://www.compsoft.com.au/~rmcm/pgp-pk