[Dbix-class] Insert or Update (was ANNOUNCE: 0.08099_08)

[Peter Corlett]

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.