MySQL并发执行INSERT+UPDATE事务时出现插入阶段死锁求助

问题描述 #

我有一个API，执行包含以下操作的事务：

INSERT INTO tbl VALUES (...)
UPDATE tbl SET col=val WHERE id=$(aboverow)

流程为先插入一条带自增主键的新行，通过Node.js的Sequelize ORM获取主键后生成val，再执行UPDATE更新对应列。

当发起5-6次并发API调用时，会有2-3次报错：

Deadlock found when trying to get lock; try restarting transaction

我原本认为死锁仅会在事务互相干扰时出现，但这里每个事务操作的都是独立新行，不应产生死锁。通过日志排查发现，死锁一致发生在INSERT步骤。

补充表结构 #

CREATE TABLE `subscription_invoice` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `invoiceId` varchar(200) CHARACTER SET utf8 NOT NULL COMMENT '{key}',
  ...
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=81 DEFAULT CHARSET=latin1

注：invoiceId列由id和userId计算得出，因id是数据库自增生成，所以需要先插入行再更新invoiceId，整个操作在事务中完成。

问题原因分析 #

InnoDB的锁机制是核心诱因：

• 并发插入自增主键行时，InnoDB会使用自增锁（AUTO-INC Lock），这是一种特殊表级锁。对于简单INSERT语句，锁会在语句执行后释放，但事务中后续的UPDATE操作会持有行锁，可能导致锁持有顺序交叉，触发死锁。
• 即使操作独立新行，InnoDB的间隙锁（Gap Lock）可能在插入时锁定主键索引的间隙，并发事务的间隙锁范围若重叠，也会引发死锁。

解决方法 #

1. 调整自增锁模式 #

将InnoDB自增锁模式设置为innodb_autoinc_lock_mode=2（连续模式），该模式下自增锁仅在分配自增值时短暂持有，语句执行后立即释放，大幅降低锁冲突概率。

• 配置方式：在my.cnf/my.ini中添加innodb_autoinc_lock_mode=2，重启MySQL服务（MySQL 8.0默认已启用该模式）。

2. 避免先插后更，直接生成invoiceId #

跳过先插后更的流程，提前计算invoiceId后一次性插入：

• 先获取当前自增主键值：

  SELECT AUTO_INCREMENT FROM information_schema.TABLES 
  WHERE TABLE_SCHEMA='你的数据库名' AND TABLE_NAME='subscription_invoice'
  

• 用该值结合userId生成invoiceId，再执行INSERT语句插入所有字段。
• 注意：需用事务包裹SELECT和INSERT，或使用GET_LOCK()保证原子性，避免并发下自增值被抢占。

3. 优化事务逻辑，缩小锁持有时长 #

尽量缩短事务执行时间，减少锁的持有周期：

INSERT INTO subscription_invoice (userId, ...) VALUES (?, ...);
SET @last_id = 617659;
UPDATE subscription_invoice SET invoiceId = CONCAT(@last_id, '-', ?) WHERE id = @last_id;

确保上述语句在同一事务中，且中间不加入耗时的业务逻辑，快速提交事务。

4. 应用层添加死锁重试机制 #

在Sequelize中捕获死锁错误，自动重试整个事务：

async function createInvoice(userId) {
  const retryLimit = 3;
  let attempt = 0;
  while (attempt < retryLimit) {
    const transaction = await sequelize.transaction();
    try {
      const invoice = await SubscriptionInvoice.create({ userId }, { transaction });
      const invoiceId = `${invoice.id}-${userId}`;
      await invoice.update({ invoiceId }, { transaction });
      await transaction.commit();
      return invoice;
    } catch (err) {
      await transaction.rollback();
      if (err.message.includes('Deadlock found') && attempt < retryLimit - 1) {
        attempt++;
        continue;
      }
      throw err;
    }
  }
}

内容的提问来源于stack exchange，提问作者Kushagra Gupta