Re: Working around spurious unique constraint errors due to SERIALIZABLE bug

* Craig Ringer:

> The test program, attached, demonstrates what I should've known in the
> first place. In SERIALIZABLE isolation, the above is *guaranteed* to
> fail every time there's conflict, because concurrent transactions cannot
> see changes committed by the others. So is a SELECT test then separate
> INSERT, by the way.

Yes, I forgot to mention that you can't use SERIALIZABLE if you use
this approach.

> Given that, it seems to me you'll have to rely on Pg's internal
> lower-level synchonization around unique indexes. Try the insert and see
> if it fails, then ROLLBACK TO SAVEPOINT (or use a PL/PgSQL exception
> block). As you noted, this does mean that certain side-effects may
> occur, including:
>
> - advancement of sequences due to nextval(...) calls
>
> - triggers that've done work that can't be rolled back, eg
> dblink calls, external file writes, inter-process communication etc

It's also the cost of producing the input data for the INSERT.

> (You might want to use the two-argument form of the advisory locking
> calls if your IDs are INTEGER size not INT8, and use the table oid for
> the first argument.)

Locking on a hash value could also be an option (it's how concurrent
hash tables are sometimes implemented).

> Also: Is this really a phantom read? Your issue is not that you read a
> record that then vanishes or no longer matches your filter criteria;
> rather, it's that a record is created that matches your criteria after
> you tested for it.

It's the INSERT which performs the phantom read.

And is SQL's definition of serializability really different from the
textbook one?

> Certainly that wouldn't be possible if the concurrent transactions were
> actually executed serially, but does the standard actually require that
> this be the case? If it does, then compliant implementations would have
> to do predicate locking. Ouch. Does anybody do that?

You don't need predicate locking here. You just have to lock on the
gap in the index you touched. I think some implementations do this
(InnoDB calls it "next-key locking").

--
Florian Weimer <fweimer(at)bfk(dot)de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99