Rails transactions and locks… a failed story

In a Rails app I’m currently working on (an ERP software), I recently tracked a nasty bug that affected the way the software handled the increases and decreases of the quantities of items in stock.

ActiveRecord::Base.transaction do
  unprocessed_item_exits.each do |exit_item|
    stock = StockForItem.find_by!(item: exit_item)
    stock.quantity = (stock.quantity - exit_item.quantity)
    stock.save!
    exit_item.processed!
  end
end

The problem was caused for a lack of knowledge of the differences between Database Transactions and Locks and the way ActiveRecord could implement both to help us ensure data consistency.

Let’s start with the basics, Transactions according to the ActiveRecord documentation are:

protective blocks where SQL statements are only permanent if they can all succeed as one atomic action.

So basically if every database interaction succeeds inside the transaction block all the changes are applied or none are applied if just one fails, this could happen with data validation errors, foreign key constraints, etc.

We can implement it in Rails like this:

ActiveRecord::Base.transaction do
  model_a.save!
  model_b.save!
end

So going back to the original code, it appears to be safe, we make changes to the records of two models (UnprocessedItemExits and StockForItem) and if there’s is an error in any operation, the data won’t be changed.

ActiveRecord::Base.transaction do
  unprocessed_item_exits.each do |exit_item|
    stock = StockForItem.find_by!(item: exit_item)
    stock.quantity = (stock.quantity - exit_item.quantity)
    stock.save! #changes StockForItem record
    exit_item.processed! #changes ExitItem record
  end
end

The real problem lies in the missing logic to handle concurrent changes to the same record.

Let’s think a situation in that the same item has two different exits occurring at the same time, both read the same value of stock.quantity and because the exits aren’t aware of the existence of each other, the first change that gets to the database is going to be saved but never read, so the second change isn’t going to take into consideration the new stock.quantity saved in the database.

To fix this we can implement locks using the lock! method. According to ActiveRecord documentation this lock tells every other operation accessing a a locked record that probably there are going to be changes to the data so we don’t make any dirty reads or updates.

By default the lock! method passes an “FOR UPDATE” clause to the DB, but you can pass any database specific lock instruction to the method.

Here we can implement lock! in both models and also use the transaction block to guarantee all locked operations should succeed as one.

ActiveRecord::Base.transaction do
  unprocessed_item_exits.lock!.each do |exit_item|
    stock = StockForItem.lock!.find_by!(item: exit_item)
    stock.quantity = (stock.quantity - exit_item.quantity)
    stock.save!
    exit_item.processed!
  end
end

This code will raise errors and trigger a rollback when the records can’t be locked or the records are invalid in this way ensuring data integrity and consistency.

In conclusion, transactions are for an “all or nothing” use case and locks are for an “only me should change this and everybody else should wait for my changes”