I'm seeing something fairly unintuitive about serializable transactions.
Taking the following test case:
CREATE TABLE foo (id integer);
t1 t2
-- BEGIN;
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO foo (id) --
VALUES (1); --
-- SELECT * from foo;
The select in t2 sees the row inserted from t1, which it shouldn't.
http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html
"When a transaction is on the serializable level, a SELECT query sees
only data committed before the transaction began; it never sees either
uncommitted data or changes committed during transaction execution by
concurrent transactions."
Now, if I modify the case as such:
t1 t2
-- BEGIN;
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- SELECT * from foo;
INSERT INTO foo (id) --
VALUES (1); --
-- SELECT * from foo;
The select in t2 (the last one, obviously) does not see the insert from t1.
What's up?
--
Brad Nicholson 416-673-4106 bnichols@ca.afilias.info
Database Administrator, Afilias Canada Corp.
