SQLSTATE[40001]：序列化失败：并发访问时插入触发器导致1213死锁问题

Question

我发现了一个SQL死锁问题，当函数由两个用户并发执行时会发生该问题。我有一个PHP函数，它执行几个包含在事务中的数据库插入查询。其中一个插件也触发了一个触发器。请参阅下面的我的表模式和代码示例。

主表

CREATE TABLE `main_table` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `action_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

历史表格

CREATE TABLE `history_table` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `action_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `audit_id` INT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

审计表

CREATE TABLE `audit_table` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `action_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我在main_table上有一个触发器，定义如下。它所做的是从audit_table中选择最大id并将记录插入到history_table。

CREATE TRIGGER watch_insert_main_table
AFTER INSERT ON main_table
FOR EACH ROW BEGIN
    DECLARE max_id INT;
    SET max_id = (SELECT MAX(`id`) FROM `audit_table`) + 1;
    INSERT INTO `history_table` SET audit_id=max_id;
END;

下面是由两个用户同时执行的函数。插入记录功能简单地将一条记录插入到给定的表中。

try {
    $dbConnection->beginTransaction();
    $this->insertRecord('main_table');    
    $this->insertRecord('audit_table');
    $dbConnection->commit();    
} catch (Exception $e) {
    $dbConnection->rollback();    
}

当第二次（并发）调用该函数时，我得到以下死锁错误。

40001 - SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

如果我执行以下任何一项更改，则不会发生此问题。

• 删除PHP代码中包含的事务

我想知道这个问题的根本原因。触发MySQL触发器时是否有另一个事务正在启动？触发器中的事务和锁是如何工作的？

我发现以下两个问题也与类似的问题有关。

• MySQL死锁与一个插入引发触发器
• 由INSERT触发器引起的死锁-线程正在寻找它已经拥有的锁？

Answer 1

看看这种简化是否有帮助：

CREATE TRIGGER watch_insert_main_table
AFTER INSERT ON main_table
FOR EACH ROW BEGIN
    INSERT INTO `history_table` (audit_id)
        SELECT MAX(`id`)+1 FROM `audit_table`;
END;

注意它是如何将两条语句组合成一条语句的。这样可以避免让另一个连接进入并获取相同的id（或类似的内容）。

以下内容可能与此相关（由OP提供）：这是由于MySQL中的一个已知问题造成的。使用读取未提交隔离级别时，从select设置变量会获得锁。此线程包含更多信息。

您是否使用隔离模式读取未提交？若然，原因为何？