Re: BUG #9301: INSERT WHERE NOT EXISTS on table with UNIQUE constraint in concurrent SERIALIZABLE transactions

From: David Johnston <polobo(at)yahoo(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #9301: INSERT WHERE NOT EXISTS on table with UNIQUE constraint in concurrent SERIALIZABLE transactions
Date: 2014-02-21 06:58:01
Message-ID: 1392965881288-5793000.post@n5.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Tom Lane-2 wrote
> mscott@

> writes:
>> -- The following seems to violate the fundamental guarantee of ISOLATION
>> LEVEL SERIALIZABLE in that the two
>> -- transactions below do not behave the same as if they were run
>> serially.
>
> I must be missing something ... what about the duplicate-key error is not
> what you would expect if the two transactions had been run serially?
>
> BTW, the fundamental guarantee is not what you said above. It is that
> you will *either* get the same results as if the transactions had been
> run in some unspecified serial order, *or* you will get a serialization
> error. There is certainly no guarantee about which of these cases
> ensues.
>
> regards, tom lane

My reading is that if run serially the second function call results in a
select returning zero records (exists returns true, then not-ed to a
constant false in the where clause) and thus no insert is attempted and thus
no duplicate key error - the record from the first transaction remains as-is
and the second is effectively a no-op.

So the fact the serializable versions fails is correct but it fails
"incorrectly" since a serial execution could never fail with a duplicate key
error. Thus neither a possible unspecified serial order result (dup being
an impossible one) nor a serialization error occurred which is contrary to
the guarantee that you state.

Now, the fact is the serialization case correctly fails but from an API
perspective immediate retry makes no sense for duplicate key failure while
it does for serialization failure.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-9301-INSERT-WHERE-NOT-EXISTS-on-table-with-UNIQUE-constraint-in-concurrent-SERIALIZABLE-transacts-tp5792985p5793000.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Sandro Santilli 2014-02-21 09:17:59 Uninterruptable regexp_replace in 9.3.1 ?
Previous Message Scott Marcy 2014-02-21 05:09:06 Re: BUG #9301: INSERT WHERE NOT EXISTS on table with UNIQUE constraint in concurrent SERIALIZABLE transactions