[CDBI] Re: atomic find_or_create()

[prev] [thread] [next] [Date index for 2005/10/22]

Perrin asked:
> Has anyone ever tried to make an atomic version of find_or_create()?
> The existing version has a race condition in that someone else could
> create a record with the specified primary key in between the SELECT and
> INSERT.
>
> I need this on MySQL InnoDB and I was considering a couple of
> strategies.  The most obvious one is to lock the table (in shared mode)
> while doing the normal find_or_create().  Less obvious would be to use
> INSERT IGNORE followed by a SELECT.  This could cause strange results if
> you pass in search criteria that don't form a unique key, but that's
> true for the existing method anyway.
>
> Anyone else have thoughts on this?

Sounds like you're on the right track. INSERT IGNORE is MySQL-specific, I
believe. Personally, I would just do the INSERT (using create()/insert()) and
then check $@ for a DBMS-specific "attempt to insert duplicate key" error
message. If that's the error, I'd search and return. If $@ contained a
different error message, I'd dbi_rollback() and _croak(). If there's no
error, just search() and return.

This all assumes you have a unique index on your primary key column(s), or
some other table constraint that would ensure a unique primary key, but
everyone does that, right?

I'd start with the usual do_transaction() routine
(http://cdbi.dcmanaged.com/wiki/Using_transactions) and modify it to do the
different error handling.... Something like this (completely untested):

sub atomic_find_or_create {

     my($class,$hash) = @_;

     $class->_invalid_object_method('atomic_find_or_create()') if ref($class);

     my $dbh = $class->db_Main;

     # Localize AutoCommit database handle attribute
     # and turn off for this block.
     local $dbh->{AutoCommit};  # Note: Leaks memory with Perl 5.6.1. Upgrade!

     my $obj;
     eval {
         $obj = $class->create($hash);
         $class->dbi_commit;
     };
     if ($@) {
         my $error = $@;
         eval { $class->dbi_rollback; };
         if ($@) {    # Doubt this could ever happen, but might as well check.
             my $rollback_error = $@;
             $class->_croak("Transaction aborted: $error; "
                            . "Rollback failed: $rollback_error\n");
         } elsif ($error =~ /duplicate key/) { # DBMS-specific error message
             ($obj) = $class->search($hash);
         } else {
             $class->_croak("Transaction aborted (rollback "
                            . "successful): $error\n");
         }
     }
     return $obj;

 } #eosub--atomic_find_or_create

Another option is to DELETE and then INSERT, inside of a transaction. That
would work on any database tht supports transactions and you wouldn't need to
know the DBMS-specific duplicate key error message, but it would be less
efficient. I think you'd also have to temporarily disable cascading deletion
inside the transaction, if applicable.

MySQL also has the (MySQL-specific) REPLACE SQL statement which basically
does a DELETE/INSERT, which I think might work atomically even if you're not
using InnoDB....

Hope this helps,
Ed

P.S. Did Tony simply forget to rename find_or_create() to find_or_insert() in
     Class::DBI v3.0.9 or what??

_______________________________________________
ClassDBI mailing list
ClassDBI@xxxxx.xxxxxxxxxxxxxxxx.xxx
http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi