Read more

How to use pessimistic row locks with ActiveRecord

Thomas Eisenbarth
May 23, 2012Software engineer at makandra GmbH

When requests arrive at the application servers simultaneously, weird things can happen. Sometimes, this can also happen if a user double-clicks on a button, for example.

Illustration UI/UX Design

UI/UX Design by makandra brand

We make sure that your target audience has the best possible experience with your digital product. You get:

  • Design tailored to your audience
  • Proven processes customized to your needs
  • An expert team of experienced designers
Read more Show archive.org snapshot

This often leads to problems, as two object instances are modified in parallel maybe by different code and one of the requests writes the results to the database.

In case you want to make sure that only one of the requests "wins", i.e. one of the requests is fully executed and completed while the other one at least has to wait for the first request to be completed, you have to obtain a explicit row lock ("pessimistic lock") within a transaction.

ActiveRecord::Base.transaction do
  obj = Model.find(23, :lock => true)
  ...
end

and ActiveRecord will ask the database to lock the row for you. in SQL this looks like

SELECT `models`.* FROM `models` WHERE `models`.`id` = 23 LIMIT 1 FOR UPDATE

You have to do this at the point where the object is loaded, obviously. \
In case you already have a Ruby object instance loaded within your code, use the lock! method:

obj.transaction do
  obj.lock!
  ...
end

This will reload the object using the lock as shown above.

Instead of doing the above you can also use with_lock which starts a transaction and acquires the lock in one go.

account = Account.first
account.with_lock do
  # This block is called within a transaction,
  # account is already locked.
  account.balance -= 100
  account.save!
end

More on row locking

Other types of locks

Posted by Thomas Eisenbarth to makandra dev (2012-05-23 14:01)