What's New in Edge Rails: Pessimistic Locking

Posted by ryan
at 9:03 AM on Tuesday, June 27, 2006

Tim Lucas over at SitePoint already talked about this feature and has added good detailed explanation in the comments.

Rails has long provided optimistic locking through the use of its lock_version magic column. In a natural compliment to that functionality, edge rails now also provides pessimistic locking support:

http://dev.rubyonrails.org/changeset/4460 http://dev.rubyonrails.org/changeset/4461 http://dev.rubyonrails.org/changeset/4462

Pessimistic locking is specified with the lock option in your standard find method:

Person.transaction do
  p = Person.find(1, :lock=> true)
  p.username = 'new'
  p.save!
end

This will lock the person row during the course of the specified transaction and prevent updates from occuring while the object is manipulated.

You can also lock an already loaded model with the new lock! method:

person.lock!

which is equivalent to:

person.reload(:lock => true)

Optimistic vs. Pessimistic Locking


With optimistic locking a check is done on the lock_version column by the Rails framework when a row is updated to make sure that it has the same value as when the model was first read from the database. If the lock_version has changed while the object has been in memory we assume that it’s been updated underneath us and a ActiveRecord::StaleObjectError is thrown. This is easily implemented by appending a WHERE lock_version = X clause to the update SQL. If no rows are updated it means the lock_version has changed. The important thing to note here is that this protection is provided at the application level.

Pessimistic locking is functionality provided by the database that happens at the database level. When invoked it actually locks the row(s) in question so that other update requests are blocked. This is usually done with the FOR UPDATE SQL clause. The benefit of this is that you have an exclusive lock on that row so no other operations can occur and you are guaranteed the data will not change. However, this is also the big downside – everybody else is blocked! You would never want to obtain an exclusive lock on a row and then either forget about it or have it locked for an extended amount of time. When another part of your application wants to update that data no errors will be raised, it will just hang. Nasty. Basically, don’t use pessimistic locking unless you know what you’re doing and have a reliable way of releasing the lock.

tags: rails, rubyonrails, locking, optimistic locking, pessimistic locking

Comments

Leave a response

  1. BobJune 18, 2006 @ 02:17 PM
    OK, I thought you were implying more than a warning about deadlocks.
  2. Ryan DaigleJune 18, 2006 @ 02:19 PM
    Bob, yes, that one code snippet might seem to be reliable, but they're often used in more complex scenarios with more than one pathway. Reliably ensuring all outcomes are properly handled is essential. Plus, this one block may be fine, but if you've got more than one such lock going on in your system you have a potential deadlock situation.

    Basically, be careful, and be thourough.

  3. BobJune 18, 2006 @ 02:19 PM
    You say, "Basically, don’t use pessimistic locking unless you know what you’re doing and have a reliable way of releasing the lock." But earlier you tie the lock to the transaction when you say, "This will lock the person row during the course of the specified transaction and prevent updates from occuring while the object is manipulated." Isn't the implication that when you hit the end of the transaction block that the locks are released? And isn't this *reliable*?
  4. cesiumOctober 29, 2006 @ 07:59 AM
    The way that people think about optimistic locking seems odd. Optimistic locking handles the double click problem. That is, optimistic locking is what you would use when you are grabbing data from the database, moving it a long way away to someone's browser, updating the data over there, and then bringing all the data back to the database. Pesimistic locking is what you would use in the application server or stored procedure where you are close to the database and won't need to lock the row for a significantly long time. It seems to me that Rails should provide an update_with_locking(id, version, fields) routine that handles the double-click problem. This would look something like: object.transaction.do o = object.find(id, :lock=>true) if o.lock_version != lock_version raise ActiveRecord::StaleObjectError end o.attributes = fields o.save end return o
  5. Ian WhiteDecember 11, 2006 @ 02:00 AM
    Thanks for this nice scrap Ryan. I've written up some notes on testing concurrency issues on rails apps over here.