6

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.

oalders
  • 5,239
  • 2
  • 23
  • 34
  • I assume you meant `$row->{bar}` and not `$row->bar()` – TLP Nov 07 '14 at 19:04
  • Increments by 1 is usually for counters. What kinds of race conditions are you experiencing at this field? – Timothy Ha Nov 07 '14 at 19:32
  • @TLP I did mean $row->bar() where $row is a DBIC result object. – oalders Nov 07 '14 at 20:21
  • 2
    @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). – oalders Nov 07 '14 at 20:25
  • 1
    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' )` – ThisSuitIsBlackNot Nov 07 '14 at 23:19
  • @oalders thank you, so your question is valid for any kind of increments, not just by 1. They're all subject to race conditions – Timothy Ha Nov 08 '14 at 06:56
  • @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. – oalders Nov 10 '14 at 15:23
  • @oalders 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. – ThisSuitIsBlackNot Nov 10 '14 at 16:14
  • @ThisSuitIsBlackNot 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. – oalders Nov 10 '14 at 17:36

2 Answers2

3

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.)

nwellnhof
  • 32,319
  • 7
  • 89
  • 113
0

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;
Timothy Ha
  • 399
  • 3
  • 7
  • 1
    In this case it doesn't fully solve the problem occurs at read time as well as write time. I think the comment by @ThisSuitIsBlackNot is probably the cleanest way to do this. – oalders Nov 10 '14 at 15:22