Conflicting updates

From ClassDBI

You have multiple processes that might be changing the same rows in the database simultaneously. How can you make sure that one doesn't clobber the changes from another?

Here's a recipe (tested only in Postgres). It uses a serial column and a SELECT FOR UPDATE lock on the DB row. If conflicting updates happen, the second one _croaks.

Add a column to your table like:

serial INT4

Add this code to your table class:

__PACKAGE__->set_sql('lock', <<"");
   SELECT __ESSENTIAL__
     FROM __TABLE__
    WHERE __IDENTIFIER__
      FOR UPDATE

This can go in your table class (you can remove the $self->can tests), or in your base class:

sub update {
   my ($self) = @_;

   if ($self->can('search_lock') && $self->can('serial')) {
       # Start transaction
       local $self->db_Main->{ AutoCommit };

       # allow duplicates in memory
       $self->remove_from_object_index;

       my $latest = $self->search_lock($self->id)->first;

       if ($latest->serial != $self->serial) {
           # possible conflict

           my @conflicts;
           foreach my $column ($self->columns) {
               next if $column eq 'serial';
               if ($self->get($column) ne $latest->get($column)) {
                   push(@conflicts, $column);
               }
           }

           if (@conflicts) {
               # this is the serial we most recently compared against
               # if you call update again on this object, it will update,
               # since we've considered the changes in $latest
               $self->serial($latest->serial);
               $self->_croak("Possible conflict. Object updated by another process. Please compare values and resubmit.",
                             latest => $latest,
                             conflicts => \@conflicts
                            );
           }
       }

       # advance serial number
       $self->serial($latest->serial + 1);
   }

   $self->SUPER::update(@_);
}

-- AneelNazareth