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

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

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

On 10/17/2012 04:16 PM, Albe Laurenz wrote:
> Why? They can be serialized. The outcome would be exactly the same
> if session 2 completed before session 1 began.

Hmm. Good point; so long as *either* ordering is valid it's fine, it's
only when *both* orderings are invalid that a serialization failure
would occur. For some reason I had myself thinking that if a conflict
could occur in either ordering the tx would fail, which wouldn't really
be desirable and isn't how it works.

BTW, the issue with the underlying question is that their "name" column
is unique. They expected to get a serialization failure on duplicate
insert into "name", not a unique constraint violation. The question
wasn't "why doesn't this fail" but "Why does this fail with a different
error than I expected". Not that the question made that particularly clear.

--
Craig Ringer

Craig Ringer wrote:
> > Why? They can be serialized. The outcome would be exactly the same
> > if session 2 completed before session 1 began.
>
> Hmm. Good point; so long as *either* ordering is valid it's fine, it's
> only when *both* orderings are invalid that a serialization failure
> would occur. For some reason I had myself thinking that if a conflict
> could occur in either ordering the tx would fail, which wouldn't
really
> be desirable and isn't how it works.
>
> BTW, the issue with the underlying question is that their "name"
column
> is unique. They expected to get a serialization failure on duplicate
> insert into "name", not a unique constraint violation. The question
> wasn't "why doesn't this fail" but "Why does this fail with a
different
> error than I expected". Not that the question made that particularly
clear.

But the unasked question is also answered, right?

Yours,
Laurenz Albe

On Thu, Oct 18, 2012 at 3:08 PM, Craig Ringer <ringerc@ringerc.id.au> wrote:
> BTW, the issue with the underlying question is that their "name" column is
> unique. They expected to get a serialization failure on duplicate insert
> into "name", not a unique constraint violation. The question wasn't "why
> doesn't this fail" but "Why does this fail with a different error than I
> expected". Not that the question made that particularly clear.

Sounds to me like it's giving a better error anyway - more helpful to
know _why_ the second transaction failed than to simply know _that_ it
failed.

I've actually never used serializable transaction isolation,
preferring more explicit constraints.

ChrisA