Re: Revisited: Transactions, insert unique.

Hiroshi Inoue wrote:
>
> 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.

Even if I call SET after BEGIN, it is not consistent with docs or the
standard (see pghackers discussion under same subject), as the two scripts
below seem to demonstrate.

[Rex:  There is a subtle difference between your sequence and mine.  Insert
from B first, and don't do any select from A before you attempt the insert,
as below.]

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

Interesting, thanks.  I certainly don't have any big gripes about PG
concurrency, as it has worked flawlessly for me so far with READ COMMITTED
level.  All this has been strictly in the interest of clarifying a
discrepancy between the docs/std and 7.0.  I could see it mattering to
some, but not for my app.

Regards,
Ed Loehr
===

This sequence, AFAICT, appears to fail the SERIALIZABLE standard, allowing
A to see effects from B that permute the serializability...

--------------------- START -----------------------------
-- Within transaction A --------------------------
DROP TABLE foo;
CREATE TABLE foo (id INTEGER, msg VARCHAR);
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Within transaction B --------------------------
BEGIN;
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO foo (id, msg)
        SELECT 1, 'From B'
        WHERE NOT EXISTS (SELECT * FROM foo WHERE id = 1);
COMMIT;
SELECT * FROM foo;

-- Within transaction A --------------------------
SELECT * FROM foo;
INSERT INTO foo (id, msg)
        SELECT 1, 'From A'
        WHERE NOT EXISTS (SELECT * FROM foo WHERE id = 1);
SELECT * FROM foo;
COMMIT;
--------------------- END -----------------------------


...while this sequence, which I would've thought to be functionally
identical, is compliant...


--------------------- START -----------------------------
-- Within transaction A ----------------------------
DROP TABLE foo;
CREATE TABLE foo (id INTEGER, msg VARCHAR);
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Within transaction B ----------------------------
BEGIN;
INSERT INTO foo (id, msg)
        SELECT 1, 'From B'
        WHERE NOT EXISTS (SELECT * FROM foo WHERE id = 1);

-- Within transaction A ----------------------------
SELECT * FROM foo;  -- The magic query.

-- Within transaction B ----------------------------
COMMIT;
SELECT * FROM foo;

-- Within transaction A ----------------------------
SELECT * FROM foo;
INSERT INTO foo (id, msg)
        SELECT 1, 'From A'
        WHERE NOT EXISTS (SELECT * FROM foo WHERE id = 1);
SELECT * FROM foo;
COMMIT;