Strange Behavior with Serializable Transcations

From: Brad Nicholson <bnichols(at)ca(dot)afilias(dot)info>
To: pgsql-general(at)postgresql(dot)org
Subject: Strange Behavior with Serializable Transcations
Date: 2006-06-28 18:48:01
Message-ID: 44A2CEE1.3080900@ca.afilias.info
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)ca(dot)afilias(dot)info
Database Administrator, Afilias Canada Corp.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Karen Hill 2006-06-28 18:59:36 Is it possible to disable insert/update/delete triggers for one transaction and not another?
Previous Message Jim C. Nasby 2006-06-28 18:12:17 Re: Fixed length datatypes. WAS [GENERAL] UUID's as