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.
This entry was posted in General. Bookmark the permalink.

5 Responses to ActiveRecord: Atomic check-and-update through optimistic locking

  1. Tilo says:

    this does not work for older versions of ActiveRecord, e.g. 3.0.8 , because of a bug in update_all()
    which does not allow the use of :limit or :order

  2. jrochkind says:

    Interesting, thanks Tilo. But in my example source code above, for my use case, I wasn’t actually using limit or order anywhere, I don’t think?

    But you’re saying if you do need to use it with limit or order, it won’t work in old version of AR? Have you confirmed it’ll work in newer ones? I’m not sure if I’ve ever tried it with limit/order, and am not sure why/when I’d want to!

  3. Tilo says:

    yes, correct. e.g. if you want to use multiple worker threads, each processing only up to a given number of jobs. Newer versions of Rails use Arel, and supposedly work.

  4. Tilo says:

    Specifically I was using:

    ModelClass.where( _condition_ ).limit( N ).update_all({ :service_status => “in_progress” })

  5. jrochkind says:

    Interesting, I’ve never had need to try an update with a limit/offset. I suspect whether it works depends on the particular rdbms you are using; I am not sure every rdbms even supports that, and I think some of those that do use different SQL syntaxes to do so.

    So I wouldn’t be surprised if not all (or even none) of the AR adapters for certain databases supported it. I would consider it a bug if they silently ignored it when they don’t support it though; if you ask for something an adapter can’t actually do, it should raise, not do something different than what you asked for.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s