Clever Geek Handbook

How to ensure MySQL lock registration order in Rails

We are using Rails 4.1 and a has_many model.

Model X has_many Y

Y can be updated and then X can be updated via callbacks (increase the version field, modified_at, etc.)

X can also be updated by itself.

This causes deadlocks inside MySQL as one transaction will lock Y1 and then lock X1, while another transaction will lock X1 and then lock Y1.

This is easy to fix by requiring a minimum of a SELECT ... FOR UPDATE

to store X before updating Y.

How do I do this in Rails? Or is there an even better solution?

I'm considering trying to ensure that I have a transaction in the before_update callback on Y and then acquiring the lock on X, but I'm not sure if this will work well with Rails.

+3


source to share


2 answers


Have you tried using block transaction

?

So, for example, you can define your own method save_with_transaction

like this:



class X < ActiveRecord::Base
  def save_with_transaction
    transaction do 
      # multiple save, update, delete calls here ...
    end
  end
end

More documentation on transactions in ActiveRecord here

0


source


How about wrapping your Y and X update model inside a transaction.

X.transaction do 
 #Do Update on Y 
 #Update X
end


I have not tested the theory in your specific case. But I think this should do the trick. Let me know if it works.

0


source






More articles:


All Articles