* 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@bfk.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