Conflicting updates

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