[Dbix-class] emulating (or using) MySQL's INSERT...ON DUPLICATE KEY UPDATE...

[Mark Hedges]

On Fri, 23 Jun 2006, Daniel Westermann-Clark wrote:
> 
> On 2006-06-22 21:22:51 -0700, Mark Hedges wrote:
> > I see from ResultSet.pm that it does try to find the row first, and
> > then it decides whether to update or create.
> > 
> > So in a multi-connection (possibly multi-server) environment,
> > update_or_create cannot be used reliably.
> 
> Would a transaction help here?

MyISAM engine doesn't support transactions, although its fast 
table lock mechanism could be used in a sequence like:

    rows affected = UPDATE
    if !rows affected {
        LOCK
        rows affected = UPDATE (in case of other attempts)
        INSERT if !rows affected
        UNLOCK
    }

Usually 1 query, sometimes 3, and risks stale locks that need
to be cleaned up in some other way.

I think it's usually better to use the real thing:

    rows affected = UPDATE
    if !rows affected {
        INSERT...ON DUP UPDATE 
    }

Usually 1 query, sometimes 2, and it always works.
    
> > This is just food for though I guess, whether it's worth it or not
> > to attempt to extend vendor-specific statements to that level of the
> > DBIC code.
> 
> My understanding from Matt was that Storage-specific hooks for
> update_or_create are a good idea, but I don't think anyone has done
> the work yet.

Cool, if I win the lottery or find a company that lets me work
30 hours a week I would love to work on that.  :-)

Mark