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

Question

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!!!

Answer 1

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.

Answer 2

This has saved me a lot of headaches: transaction_retry ruby gem.

From the README of the gem:

Retries database transaction on deadlock and transaction serialization errors. Supports MySQL, PostgreSQL, and SQLite.