On 13 Apr 2009, at 09:35, Tim Bunce wrote:
[...]
If you use SELECT FOR UPDATE then savepoints aren't required for a
safe
implementation, right?
No.
update_or_create() currently does the following: it does a SELECT to
retrieve any extant row; if the row exists it then does an UPDATE if
necessary to update any rows that have changed, otherwise it performs
an INSERT. There is a race between the SELECT and the subsequent
UPDATE or INSERT.
SELECT FOR UPDATE creates an exclusive lock on the rows that are
returned until they are UPDATEd. This neatly fixes the race where an
existing row is being updated. It's the intent of SELECT FOR UPDATE,
after all.
Critically though, if the SELECT does not return any rows, nothing is
locked and the race remains. It doesn't block a second process from
also doing a SELECT on the same non-existent row and getting no
results, and then subsequently two processes trying an INSERT and one
failing.
There's also a third case, where it turns out the row hasn't actually
changed, and DBIC skips the UPDATE in that case. However, skpping the
UPDATE causes the lock to remain.
So it looks like this still needs savepoints to do correctly. Of
course, one could always instead do a heroically complex bodge job
that looks really impressive but doesn't actually work.
(Which is handy, as SELECT FOR UPDATE is more widely supported than
savepoints.)
The elephant in the room that is MySQL can attack this problem in a
different way, using REPLACE INTO or INSERT ... ON DUPLICATE KEY UPDATE.
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
