Obtain pessimistic row locks on ActiveRecord objects
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.
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
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
- ActiveRecord also has optimistic locking. This does not require you to start a transaction, but raises an exception when trying to save a record that has been changed in the database since it was looked up.
- If you want to implement database-wide mutex on something that is not a row, check out Simple database mutex (MySQL lock).