Mysql2 ::错误：尝试锁定时发现死锁;尝试重启事务：INSERT INTO

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

我找不到根本原因，有人能给我一些建议吗？提前致谢！！！

2 个解决方案

#1

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

我的猜测是你正在使用InnoDB并且可能正在进行并发插入。

To trace and understand the cause checkout these articles

跟踪并了解这些文章的原因

mysql deadlocks with concurrent inserts

带有并发插入的mysql死锁
dealing deadlock

处理僵局

One way to fix the issue is to retry like the below code

解决此问题的一种方法是重试，如下面的代码

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_query to handle MySQL deadlocks.

或者有一些像transaction_query这样的宝石来处理MySQL死锁。

#2

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

这给我带来了很多麻烦：transaction_retry ruby gem。

From the README of the gem:

从gem的README：

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

在死锁和事务序列化错误上重试数据库事务。支持MySQL，PostgreSQL和SQLite。

#1