On Wed, Sep 19, 2012 at 3:34 AM, Peter Rabbitson <rabbit+d...@rabbit.us>wrote:
> > UPDATE ordered_list SET position = position - 1 WHERE ( ( ( position
> > BETWEEN ? AND ? ) AND owner = ? ) ): '13', '18', '1163299'
> >
> > The problem, of course, is I have a UNIQUE( position, owner ) and that
> last
> > update isn't guaranteed to work in order (from 13 to18 -- which would
> > prevent duplicate key errors).
>
> The assumption was that databases are able to handle this correctly. Please
> revert the patch[1] removing this functionality (as per its commit message)
> and let us know if this solves your problem. Also I'd recommend filing an
> RT
> bugreport to keep track of this.
>
I've discussed this Ordered code a bit here on the list. The problem is
I'm not sure what is the correct fix. I'm not convinced that the old code
is better. I like the idea of doing the move in a single query.
I can make the UNIQUE( position, group ) constraint DEFERRABLE INITIALLY
DEFERRED but there's still race conditions. There's room for other updates
to sneak in between the select and update. It's the race conditions that
have seemed to been the most problem in production. As much as I avoid
locking, seems like locking is what is needed.
Years back I added this code for inserts -- where historically we were
seeing the most duplicate key errors. After discussion on the Postgresql
list I ended up doing a select for update *on the related grouping row*.
sub insert {
# shifting self off @_ so other args can be passed to
$self->next::method
my $self = shift;
# If a position value is provided then we just continue without locking
# as the caller is explicitly setting the number.
# (Might be good to throw and exception, instead, if wish to enforce.)
return $self->next::method( @_ )
if defined $self->get_column( $self->position_column );
# We must have a grouping column defined to do the locking
# And Ordered won't throw that helpful of a message.
my $group_column = $self->grouping_column
|| die 'Ordered needs a grouping_column defined in your class: ' .
ref $self;
my $new_row;
my $next_insert = $self->next::can;
$self->result_source->schema->txn_do( sub {
# Lock with the SELECT FOR UPDATE
$self->select_related_for_update( $group_column );
# Now do the Ordered insert while locked.
$new_row = $self->$next_insert( @_ );
} );
return $new_row;
}
sub select_related_for_update {
my ( $self, $grouping_column ) = @_;
my ( $table, $pk ) = $self->find_table_for_related_column(
$grouping_column );
# The dbh method selects the master, not the replicants.
my $dbh = $self->result_source->storage->dbh;
my $sth = $dbh->prepare( "SELECT 1 FROM $table WHERE $pk = ? FOR
UPDATE" );
$sth->execute( $self->get_column( $self->grouping_column ) );
$sth->finish;
return;
}
>
> Cheers
>
> [1]
> http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=commitdiff;h=5e6fde33e5a4
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>
--
Bill Moseley
mose...@hank.org
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
