ActiveRecord: Atomic check-and-update through optimistic locking

In Umlaut, there is a database column that basically corresponds to “service_status”.  This gets set, in sequence, to “queued”, “in_progress”, and then “complete”. (Also some possibility of error statuses etc).

There is a point in Umlaut logic where it first checks to make sure a row is “queued”, then only if it is sets it to “in_progress” and executes the service.

The idea is that if it was already set to “in_progress” by someone else (another thread or another process entirely), we leave it alone, we don’t execute the logic, it’s already in progress by someone else.

And the problem with the original implementation was the race condition. First we fetch the model instance (an SQL query), and check it’s service_status. Then, only if it’s service_status is ‘queued’, do we set it’s service_status to `in_progress` and proceed to execute. But the race condition is clear here — in between the first SQL query to fetch, and the second to update, some other thread or process may have already updated it to `in_progress` and began execution, and now we have double execution.

The solution? Some form of “optimistic locking” using the atomic facilities of any rdbms.  Now, I’m not actually talking about the ‘optimistic locking’ feature built into ActiveRecord. You probably could use that feature here, but it requiers adding a special column to your db, rescuing `ActiveRecord::StaleObjectError` etc. The optimistic locking feature built into AR is potentially a powerful general purpose tool when you need to avoid concurrent updates to any column at all in many different scenarios.

But for this particular use case, there’s a simpler way to do it ourselves. We basically want to have a single SQL line that updates the column to `in_progress` if and only if it was already `queued`, in a single atomic rdbms operation, and then lets us know if the update happened or not. We can generate such an SQL using ActiveRecord 3 update_all. 

my_active_record_model = ModelClass.where( however_we_fetched_it )

num_updated =
  ModelClass.where(:id             => my_active_record_model.id,
                   :service_status => "queued").
             update_all(:service_status => "in_progress")

if num_updated > 0
  # we updated, execute
else
  # it did not have a service_status of queued, someoen
  # else beat us to it
end

• Haven’t actually updated Umlaut yet, there are some annoying legacy issues in Umlaut that make this a bit harder to fix.
• Note that update_all does not automatically update ActiveRecord `updated_at` columns, you can include those yourself if you want them in the hash of columns to update. `:updated_at => Time.now`.
• This is also an example of why you need to know and understand SQL and rdbms even if you are using a good ORM like ActiveRecord. And I love ORM’s, but if you didn’t know SQL/rdbms, you wouldn’t be able to come up with a clear way to solve this race condition in terms of SQL, and then figure out the cleanest way to do that with AR.)
• Some answers on the web to analagous problems to this one suggest using db transactions here. I suspect that transactions may not even be able to solve this problem, but even if there’s a way to do it somehow with transactions, it’s going to be messier. Transactions aren’t in fact the right tool here. A simple optimistic locking `update…where` is.