How to increment a column using DBIx::Class?

Question

I'm trying to convert some raw DBI calls to DBIx::Class. I occasionally run across something like:

UPDATE 'foo' SET bar = bar + 1 WHERE ...

Is there way to do have DBIx::Class execute exactly this kind of query? I don't want to do something like:

$row->update({ bar => $row->bar() + 1 });

because there's a race condition there if multiple processes are trying to do the same thing.

I could get around that with some kind of locking at the database level, but that seems worse to me than just using the original query. Basically I just want to know if there's a clean way of using DBIC to do this or if I should just continue to use raw DBI calls here.

Urian

I assume you meant $row->{bar} and not $row->bar() – Turtleback

Increments by 1 is usually for counters. What kinds of race conditions are you experiencing at this field? – Passacaglia

@Turtleback I did mean $row->bar() where $row is a DBIC result object. – Urian

@TimothyHa I have a couple of use cases, but to take the counter example, let's say I have several sessions which all want to increment the counter at around the same time. If 2 processes SELECT bar at the same time, they'll both have the value X for that column in the DBIx::Class object. If they then both increment by one and save the row, after 2 updates the number will only be X + 1, rather X + 2, since both DBIx::Class objects will be incrementing from the same base value (X). – Urian

The closest thing I could find was $schema->resultset('Widget')->search({ name => 'foo' })->update_all({ count => \'count + 1' }) to add 1 to count where name is foo. This generates two SQL statements, but they're automatically wrapped in a transaction: SELECT me.id, me.name, me.count FROM test me WHERE ( name = 'foo' ) followed by UPDATE test SET count = count + 1 WHERE ( id = '1' ) – Phosphoprotein

@Urian thank you, so your question is valid for any kind of increments, not just by 1. They're all subject to race conditions – Passacaglia

@ThisSuitIsBlackNot, that sounds like a reasonable way to do this. If you want to add that as an answer, I'm happy to accept it. – Urian

@Urian I'm not sure it's the best approach. A separate UPDATE is generated for each row in the result set. Depending on your database engine, this may or may not be acceptable. With MySQL's InnoDB, for example, transactions are supported and the default transaction isolation level is repeatable read; all reads after the first use the same snapshot, so they won't be affected by later transactions. MySQL's MyISAM, on the other hand, doesn't support transactions, so you would still have a nasty race condition. – Phosphoprotein

@Phosphoprotein in this case I think it's fine for me. I'm using Postgres and I only need to update a single row, since the WHERE clause uses a primary key. – Urian

Answer 1

Use the solution from @ThisSuitIsBlackNot's comment but replace update_all with update:

$rs->search(...)->update({
    bar => \'bar + 1',
});

This will result in a single UPDATE statement. (Explanation: update_all works by calling update on every Row in the ResultSet including things like DBIC triggers, so it has to fetch the rows first. update on a ResultSet executes a barebones SQL UPDATE.)

Aliquot

Answer 2

Will using DBIx::Class::Storage::TxnScopeGuard help? You can wrap your block of code in a transaction like this

my $guard = $schema->txn_scope_guard;
# your increment
$guard->commit;

Passacaglia

In this case it doesn't fully solve the problem occurs at read time as well as write time. I think the comment by @Phosphoprotein is probably the cleanest way to do this. – Urian