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

Craig Ringer wrote:
> In this SO question:
>
>
http://dba.stackexchange.com/questions/26905/how-do-i-implement-insert-i
f-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?

Why? They can be serialized. The outcome would be exactly the same
if session 2 completed before session 1 began.

You would have a serialization problem if each session tried
to read what the other tries to write:

SESSION 1 SESSION 2

BEGIN ISOLATION LEVEL SERIALIZABLE;

BEGIN ISOLATION LEVEL SERIALIZABLE;

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

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

SELECT * FROM artist WHERE name = 'Bob';

SELECT * FROM artist WHERE name =
'Bob';

COMMIT;

COMMIT; /* throws serialization
error */

Yours,
Laurenz Albe