插入时触发的存储过程会导致事务中的死锁。

Question

我正在尝试调试在事务中触发存储过程时发生的死锁。表和触发器定义如下：

CREATE TABLE a (
  id int(11) NOT NULL AUTO INCREMENT,
  b varchar(10) NOT NULL,
  d date NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1; //

CREATE PROCEDURE CheckNoDuplicates (new_id int(11), new_b varchar(10), new_d date)
BEGIN
  DECLARE existingId INT(11);

  SELECT id INTO existingId
  FROM a
  WHERE new_b = b
      AND d IS NOT NULL AND new_d IS NOT NULL AND YEAR(d) = YEAR(new_d)
        OR d IS NULL AND new_d IS NULL
       AND id <> new_id;

  IF existingId IS NOT NULL THEN
    SIGNAL SQLSTATE '23000';
  END IF;
END //

CREATE TRIGGER NoDuplicateOnInsert BEFORE INSERT ON a
FOR EACH ROW
  BEGIN
    CALL CheckNoDuplicates(NEW.id, NEW.b, NEW.d);
  END //

CREATE TRIGGER NoDuplicateOnInsert BEFORE UPDATE ON a
FOR EACH ROW
  BEGIN
    CALL CheckNoDuplicates(NEW.id, NEW.b, NEW.d);
  END //

当两个插入同时发生在事务中时，有时会成功，有时会给我返回一个死锁(在尝试锁定时发现死锁；尝试重新启动事务)。

如果我摆脱了存储过程，我就没有死锁了。

如何在事务中执行存储过程？是否有更好的方法来保持我想要得到的约束(b和日期d的唯一性)？

Answer 1

是否有更好的方法来保持我想要得到的约束(b和日期d的唯一性)？

创建虚拟生成列，该列计算您希望成为唯一的值，并在其上创建唯一的辅助索引。

欲了解更多信息，请阅读MySQL 5.7参考手册/次级索引和生成的列

Answer 2

INDEX(b)

会有很大帮助的。这样做可能会更好：

INDEX(b, d, id)