mysql - 添加主键时死锁增加。为什么？

Question

首先，一些必要的背景知识(请耐心等待)。我是使用 MySQL 实现持久性的 Web 应用程序的开发人员。我们通过为每个数据表创建审计跟踪表来实现审计日志记录。例如，对于客户实体，我们可能具有以下表定义:

-- Data table definition.
CREATE TABLE my_database.customers (
  CustomerId INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  FirstName VARCHAR(255) NOT NULL,
  LastName VARCHAR(255) NOT NULL,
  -- More data columns, removed for simplicity.
  ...
);

-- Audit table definition in separate schema.
CREATE TABLE my_database_audittrail.customers (
  CustomerId INT(11) DEFAULT NULL,
  FirstName VARCHAR(255) DEFAULT NULL,
  LastName VARCHAR(255) DEFAULT NULL,
  -- More data columns.
  ...
  -- Audit meta data columns.
  ChangeTime DATETIME NOT NULL,
  ChangeByUser VARCHAR(255) NOT NULL 
);

如您所见，审计表只是数据表的副本加上一些元数据。请注意，审计表没有任何键。例如，当我们更新客户时，我们的 ORM 会生成类似于以下的 SQL:

-- Insert a copy of the customer entity, before the update, into the audit table.
INSERT INTO my_database_audittrail.customers (
  CustomerId,
  FirstName,
  LastName,
  ...
  ChangeTime,
  ChangeByUser)
)
SELECT
  CustomerId,
  FirstName,
  LastName,
  ...
  NOW(),
  @ChangeByUser
FROM my_database.customers
WHERE CustomerId = @CustomerId;

-- Then update the data table.
UPDATE
  my_database.customers
SET
  FirstName = @FirstName,
  LastName = @LastName,
  ...
WHERE CustomerId = @CustomerId;

这已经很有效了。但是，最近由于各种原因，我们需要在审计表中添加一个主键列，将审计表定义更改为类似于以下内容:

CREATE TABLE my_database_audittrail.customers (
  __auditId INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  CustomerId INT(11) DEFAULT NULL,
  FirstName VARCHAR(255) DEFAULT NULL,
  LastName VARCHAR(255) DEFAULT NULL,
  ...
  ChangeTime DATETIME NOT NULL,
  ChangeByUser VARCHAR(255) NOT NULL
);

我们ORM在更新数据表时生成的SQL没有修改。这种变化似乎大大增加了死锁的风险。所讨论的系统是一个 Web 应用程序，其中包含许多夜间批处理作业。死锁的增加并没有体现在我们的网络用户对系统的日常使用中。然而，每晚的批处理作业确实会遇到死锁，因为它们在少数数据库表上进行大量工作。我们的“解决方案”是添加死锁重试策略(几乎没有争议)，虽然这似乎工作正常，但我非常想了解为什么上述更改增加了死锁的风险(如果我们可以以某种方式解决问题)。

更多信息:

• 我们每晚的批处理作业在我们的数据表上执行插入、更新和删除操作。仅对审计表执行插入操作。
• 我们对数据库事务使用可重复读取隔离级别。
• 在此更改之前，我们在运行夜间批处理作业时没有发现任何死锁。

更新:检查 SHOW ENGINE INNODB STATUS 以确定死锁的原因并发现:

*** WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `my_database_audittrail`.`customers` trx id 24972756464 lock mode AUTO-INC waiting

我的印象是自动增量是在任何事务之外处理的，以避免在不同的事务中使用相同的自动增量值？但我想我们引入的主键上的 AUTO_INCREMENT 属性似乎是问题所在？

Answer 1

这是猜测。

向带有索引的表中插入或更新不仅会锁定数据页，还会锁定索引页，包括更高级别的索引。当多个线程同时影响记录时，它们可能会锁定索引的不同部分。

这通常不会出现在单个记录插入中。但是，更新多条记录的两个语句可能会开始获取索引上的锁，并发现它们彼此死锁。重试可能足以解决此问题。或者，似乎一次运行“太多”，您可能需要考虑如何安排夜间更新工作。