8

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

pangpang
  • 8,581
  • 11
  • 60
  • 96

2 Answers2

20

My guess is that you are using InnoDB and probably doing concurrent insertions.

To trace and understand the cause, check out these articles:

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.

ib.
  • 27,830
  • 11
  • 80
  • 100
Siva
  • 7,780
  • 6
  • 47
  • 54
  • I have a question: save and create methods are wrapped inside a transaction, is it not necessary to add transaction to 'add' method? – pangpang Dec 03 '14 at 02:38
  • 1
    Transaction is necessary if you have multiple insertions to make sure all are successful or abort. Depends totally on your implementation, but just by looking at the code you provided I'd say not necessary. – Siva Dec 03 '14 at 03:34
4

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.

fguillen
  • 36,125
  • 23
  • 149
  • 210
  • 1
    sorry, I know you have a high reputation on this site, but... this answer is sort of link-only and not spam. If you could throw some more light on how this would help the question, it would be appreciated – Igbanam Mar 16 '16 at 09:32
  • @Yasky it is a gem that deals exactly with the kind of problems the question is asking for. In a concurrence environment sometimes deadlocks are inevitable, the only way is just retry the transaction. This gem that this job automatically. – fguillen Mar 16 '16 at 09:51
  • anyone have an idea that I can create tests for this case? Like, I want to simulate a deadlock and make sure this gem implementation fixes it. – Baran Yeni Mar 02 '23 at 23:44