* 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@xxxxxx> BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
