Mysql2 :: Error: a deadlock was detected while trying to get a lock; try restarting the transaction: INSERT INTO

Question

Recently, I have found many bugs in the deadlock in my application.

Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction: INSERT INTO `products`.... 

code as below:

After the user has been created, I will add some products to the user. I do not understand why there was a dead end.

 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 did not find the root cause, can someone give me advice? Thanks in advance.

Answer 1

I assume that you are using InnoDB and possibly doing parallel inserts.

Track and understand the reason for checking these articles

• mysql concurrency stubs

• deadlock

One way to fix the problem is to try again, as shown 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, such as transaction_query, for handling MySQL deadlocks.

Answer 2

It saved me a lot of headaches: transaction_retry ruby stone.

From the README gem:

Repeats the database transaction deadlock and transaction serialization errors. Supports MySQL, PostgreSQL and SQLite.