ActiveRecord receives pessimistic locking

Jeremy Kemper just added pessimistic locking to ActiveRecord:


Person.transaction do 
  person = Person.find(1, :lock => true) 
  person.visits += 1 
  person.save! 
end 

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • Tex

    Wow ground breaking.

  • http://www.toolmantim.com timlucas

    I know I sorely missed this feature. Not sure about others… but thanks for the input Tex ;)

  • http://rtasks.com SRTech

    Tim,

    I am having trouble figuring out what this does. Can you explain for those of us new to this, and give us an example of what we could use it for?

  • http://www.toolmantim.com timlucas

    Sorry SRTech, a full explanation would have been much more helpful.

    Read locks such as this ensure that no other queries can read that table row until the transaction has completed. Because you want to be able to execute your code simultaneously, and from different servers, database locks can be used to make sure your is consistent.

    For example, say you want to implement something that tracks the visits for your user accounts.

    A simple implementation could be:

    
    user = User.find(session[:user_id])
    user.update_attribute(:visits, user.visits+1)
    

    If the user makes simultaneous requests you can get what’s called a race condition.

    For example, the following could be the order of execution if the the same user made 2 simultaneous requests:

    
    user_request_1 = User.find(session[:user_id])
    user_request_2 = User.find(session[:user_id])
    user_request_1.update_attribute(:visits, user_request_1.visits+1)
    user_request_2.update_attribute(:visits, user_request_2.visits+1)
    

    The problem here is that visits should really be 3, not 2.

    With web applications you need multiple application servers to be able to execute the code concurrently. The database is the perfect fit to handle this case.

    Changing the code to:

    
    User.transaction do
      user = User.find(session[:user_id], :lock => true)
      user.update_attribute(:visits, user.visits+1)
    end
    

    produces the following SQL code:

    
    START TRANSACTION;
    SELECT * FROM users WHERE id = 1 FOR UPDATE;
    UPDATE users SET visits = 2, (...) WHERE id = 1;
    COMMIT;
    

    Does that make more sense?

    (updated to remove inaccurate SQL comments)

  • http://www.lastfield.net/?location=birmingham Luke Redpath

    Tim, there is a major error in your code above. Calling update_attribute will NOT update a single attribute – it will update the single attribute in memory then save the ENTIRE record.

  • http://www.toolmantim.com timlucas

    Thanks Luke, you’re totally right!

    Not that it really matters in the above example, because we are locking the entire row, but I’ll update the article for correctness.

  • ritukamthan

    hey Tim dats a gud article for quick reference. thnx :)

  • ian white

    Thanks for the writeup!

    I’ve written up some notes on testing rails code with concurrency issues over here