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

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.