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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
