Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction: INSERT INTO
mysql, ruby, ruby-on-rails, sql
Solution
My guess is that you are using InnoDB and probably doing concurrent insertions.
To trace and understand the cause, check out these articles:
- MySQL deadlocks with concurrent inserts
- How can I configure MySQL Innodb to handle 1000s of inserts per hour?
One way to fix the issue is to retry like it is shown in the code below:
def add_products
retries = 0
begin
Product.add(self, "product_name", 10)
rescue ActiveRecord::StatementInvalid => ex
if ex.message =~ /Deadlock found when trying to get lock/ #ex not e!!
retries += 1
raise ex if retries > 3 ## max 3 retries
sleep 10
retry
else
raise ex
end
end
end
Or, there are some gems like transaction_retry to handle MySQL deadlocks.
Problem
Recently, I found lots of deadlock errors in my application. ``` Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction: INSERT INTO `products`.... ``` the code as below: After user has been created, I will add some products to user. I don't understand why deadlock happened. ``` class User < ActiveRecord::Base after_create :add_products has_many :products, :dependent => :destroy def self.create_user User.create!(.......) end def add_products Product.add(self, "product_name", 10) end ..... end class Product < ActiveRecord::Base belongs_to :user def self.add(user, product_name, amount) transaction do product = user.products.find_by_product_name(product_name) if product product.increment :amount, amount product.save! else product = self.create! user_id: user.id, product_name: product_name, amount: amount end end product end end ``` I didn't find the root cause, can anyone give me some advice? Thanks in advance!!!