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@compsoft.com.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@mcmaster.wattle.id.au
                     http://www.compsoft.com.au/~rmcm/pgp-pk