9.1/9.2 SERIALIZABLE: expected serialization failure between INSERT and SELECT not encountered

From: Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: 9.1/9.2 SERIALIZABLE: expected serialization failure between INSERT and SELECT not encountered
Date: 2012-10-17 05:09:00
Message-ID: 507E3D6C.6040004@ringerc.id.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all

In this SO question:

http://dba.stackexchange.com/questions/26905/how-do-i-implement-insert-if-not-found-for-transactions-at-serializable-isolatio/26909#26909

the author is running a series of queries that I'd expect to abort on
commit with a serialisation failure. No such failure occurs, and I'm
wondering why.

SETUP

create table artist (id serial primary key, name text);

SESSION 1 SESSION 2

BEGIN ISOLATION LEVEL SERIALIZABLE;

BEGIN ISOLATION LEVEL
SERIALIZABLE;

SELECT id FROM artist
WHERE name = 'Bob';

INSERT INTO artist (name)
VALUES ('Bob')

INSERT INTO artist (name)
VALUES ('Bob')

COMMIT; COMMIT;

I'd expect one of these two to abort with a serialization failure and
I'm not sure I understand why they don't in 9.1/9.2's new serializable
mode. Shouldn't the SELECT for "Bob" cause the insertion of "Bob" in the
other transaction to violate serializability?

--
Craig Ringer

Responses

Browse pgsql-general by date

  From Date Subject
Next Message chinnaobi 2012-10-17 05:51:07 Re: How to avoid base backup in automated failover
Previous Message Florent Guillaume 2012-10-17 04:38:01 Re: WebSphere Application Server support for postgres