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

Tim Bunce Tim.Bunce at pobox.com
Mon Apr 13 14:23:45 GMT 2009


On Mon, Apr 13, 2009 at 11:20:41AM +0100, Peter Corlett wrote:
> 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.

> Critically though, if the SELECT does not return any rows, nothing is 
> locked and the race remains.

Got it.

> So it looks like this still needs savepoints to do correctly.

Should everyone have to pay the overheads, though, even if they don't
need atomic behaviour? Perhaps add an update_or_create_atomicaly() method.

Alternately, enhance update_or_create() as you propose and someone else
could implement a ...::FastResultSet moose role that provides an
update_or_create_fast() method that avoids the initial SELECT by
attempting an UPDATE as the first step. (The initial SELECT has always
bugged me slightly, but then I'm overly performance concious :-)

>> (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.

(Trivia: I was nagging Monty to implement something like Oracle's MERGE
command for MySQL. We had a daemon that was doing billions of UPDATE
else INSERTs 24/7. When Monty looked into it he came up with the "ON
DUPLICATE KEY UPDATE" mechanism. Sped up the process dramatically.)

That would be a natural fit for an insert_or_update() method.

Tim.



More information about the DBIx-Class mailing list