Node.js导入MySQL数据遇大量死锁问题求助

AI 小北

2026-5-28

看起来你碰到了InnoDB并发写入时典型的死锁问题，根源在于INSERT ... ON DUPLICATE KEY UPDATE在并发场景下的锁竞争，再加上自增主键的锁机制，很容易触发循环等待导致死锁。我来给你拆解一下问题并给出标准解决方案：

一、先搞清楚死锁的根源

你的表有两个关键锁点：

唯一键(tourItineraryId, longitude, latitude)：并发插入时，事务会先检查这个唯一键是否存在，获取对应的间隙锁或行锁。
自增主键tourItineraryLocationId：插入新记录时需要获取自增锁来生成ID。

当多个事务同时执行INSERT ... ON DUPLICATE KEY UPDATE时，可能出现这样的循环等待：

事务A检查唯一键发现不存在，持有了间隙锁，准备插入新记录时等待自增锁；
事务B检查另一条记录的唯一键也不存在，持有了另一个间隙锁，同样等待自增锁；
或者反过来，事务A先拿到自增锁，等待唯一键的锁，事务B拿到唯一键的锁，等待自增锁。这种互相等待的情况就触发了死锁，MySQL会主动回滚其中一个事务，抛出ER_LOCK_DEADLOCK错误。

二、标准解决方案

1. 修复Node.js的重试逻辑（核心）

你之前用while循环重试导致程序停滞，是因为回调式代码很容易陷入作用域和异步流程的坑。改用async/await封装重试逻辑，能更清晰地处理重试流程：

// 封装带死锁重试的查询函数
async function queryWithDeadlockRetry(pool, sql, params, maxRetries = 3) {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      // 使用Promise风格的查询（mysql2默认支持，mysql库需用util.promisify转换）
      const [results] = await pool.query(sql, params);
      return results;
    } catch (error) {
      if (error.code === 'ER_LOCK_DEADLOCK') {
        console.log(`Deadlock detected, retrying (attempt ${attempt}/${maxRetries})...`);
        // 加随机延迟，避免多个请求同时重试再次冲突
        await new Promise(resolve => setTimeout(resolve, Math.random() * 1000));
        continue;
      }
      // 非死锁错误直接抛出
      throw error;
    }
  }
  throw new Error(`Exceeded max retries (${maxRetries}) due to deadlocks`);
}

// 使用示例（需在async函数中调用）
async function processLocation(itineraryId, location) {
  try {
    await queryWithDeadlockRetry(
      pool,
      "CALL AddItineraryDayLocation(?,?,?,?)",
      [itineraryId, location.name, location.longitude, location.latitude]
    );
  } catch (error) {
    console.error(`Failed to process location: ${error.message}`);
  }
}

如果用的是旧版mysql库，需要先把pool.query转为Promise风格：

const util = require('util');
const query = util.promisify(pool.query).bind(pool);

2. 优化存储过程和事务

你之前加事务没效果，可能是事务范围不对或者隔离级别太高。调整如下：

DELIMITER //
CREATE PROCEDURE `AddItineraryDayLocation`(
  IN tourItineraryId int,
  IN name VARCHAR(50),
  IN longitude decimal(12,8),
  IN latitude decimal(12,8)
)
BEGIN
  -- 用READ COMMITTED隔离级别，减少锁持有时间
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  
  START TRANSACTION;
    -- ON DUPLICATE KEY里不需要更新tourItineraryId，唯一键已保证它不会变化
    INSERT INTO touritinerarylocation (`tourItineraryId`, `name`, `longitude`, `latitude`)
    VALUES (tourItineraryId, name, longitude, latitude)
    ON DUPLICATE KEY UPDATE
      `name` = VALUES(name),
      `longitude` = VALUES(longitude),
      `latitude` = VALUES(latitude);
  COMMIT;
END //
DELIMITER ;

用VALUES(name)代替直接用参数名，是更规范的写法，避免参数和字段名冲突。

3. 数据库配置优化

调整自增锁模式：如果你的MySQL版本是5.1+，可以设置innodb_autoinc_lock_mode = 2（MySQL 8.0默认就是这个值）。这个模式下，InnoDB不会持有表级的自增锁，而是用行级锁，能大幅减少并发插入时的锁竞争。
在my.cnf或my.ini中添加：
```
innodb_autoinc_lock_mode = 2
```
重启MySQL生效。
分析死锁日志：执行SHOW ENGINE INNODB STATUS;，查看LATEST DETECTED DEADLOCK部分，能看到具体是哪些事务和锁导致的死锁，帮助你进一步优化。

4. 批量导入优化

如果是大量数据导入，尽量减少单条请求的数量，改用批量插入：

async function batchProcessLocations(itineraryId, locations) {
  // 构造参数化批量插入的参数数组
  const params = locations.map(loc => [itineraryId, loc.name, loc.longitude, loc.latitude]);
  const sql = `INSERT INTO touritinerarylocation (tourItineraryId, name, longitude, latitude) VALUES ? ON DUPLICATE KEY UPDATE name = VALUES(name), longitude = VALUES(longitude), latitude = VALUES(latitude)`;
  
  await queryWithDeadlockRetry(pool, sql, [params]);
}

批量插入能大幅减少并发请求数，降低死锁概率，同时提升导入效率。

三、总结

死锁在高并发写入场景下很难完全避免，标准的处理流程是：

客户端实现可靠的重试逻辑（针对ER_LOCK_DEADLOCK错误）；
优化数据库操作，减少锁持有时间和冲突概率；
批量处理数据，降低并发压力；
监控死锁日志，持续优化。

内容的提问来源于stack exchange，提问作者Andy Furniss