Re: Possible transaction bug with isolation level serialisable?

On 04/24/2014 12:54 PM, Claudio Nieder wrote:
> Two concurrent transaction first check if a certain value is listed
> in a table and if not try to insert it. As the value inserted hasto
> be unique and both try to enter the same one, one transaction fails
> reporting that the value is already part of the table. As both are
> executed within a transaction with isolation level SERIALIZABLE I
> would have expected that one of them would return with a
> serialization failure, which my program would have been ready to
> handle and re-execute the transaction.

If I understood correctly, you're getting a "duplicate key value
violates unique constraint" error, but you were expecting to get a
"serialization failure" error instead, because you're using SERIALIZABLE
isolation level.

> The reason I think something is fishy is, because I find the
> behaviour is somehow similar to a nonrepeatable read which should not
> occur with this transaction level. The
>
> SELECT "userRef" FROM "VerifyQueue" WHERE lower("email")=lower($1)
>
> reports, that the value is NOT in the table, thus the program then
> tries to insert it, and at that point the value is shown as being in
> the table.
>
> So is this a bug, or did I misunderstand something?

It's not a bug. Serializable isolation level guarantees that the COMMIT
fails (or some earlier statement) with a serialization error, if
serializability would otherwise be violated, but it does not guarantee
that you won't hit some other constraint first. In this scenario, I
think you would indeed get a serialization failure if there was no
unique constraint on the table. (I would not recommend removing the
constraint, however!)

I think you'll just need to teach your application to also retry
duplicate key errors.

- Heikki