Node.js使用mysql2连接池实现INSERT/UPDATE事务如何避免死锁

1. 优先使用MySQL原生UPSERT语法代替手动事务判断 #

你之前先查再写的逻辑本身就容易产生并发冲突，直接用INSERT ... ON DUPLICATE KEY UPDATE语法，单条SQL自带原子性，完全不需要手动开启事务，从根源避免死锁。
使用前提是你需要对判断记录存在性的字段添加唯一索引，比如你用id、user_id这类字段判断记录是否存在，先给该字段创建唯一索引即可，SQL示例：

INSERT INTO 表名 (字段1, 字段2, 判重唯一字段) VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE 字段1=VALUES(字段1), 字段2=VALUES(字段2);

Node.js中调用的代码示例：

const [result] = await pool.execute(sql, [val1, val2, uniqueVal]);

这种方式是单条原子SQL，不会出现事务中断导致的死锁问题，性能也比手动开启事务高很多。

2. 必须使用手动事务的优化方案 #

如果业务逻辑复杂无法用上述单条SQL实现，按以下规则调整你的事务代码：

• 从连接池获取连接后，所有事务操作全程复用同一个连接，不要跨连接执行SQL，多数死锁问题都是因为事务内的查询和更新用了不同的连接导致的
• 查询时加FOR UPDATE行锁，避免并发场景下多个事务同时查到同一条记录不存在，同时触发INSERT导致锁冲突：

SELECT * FROM 表名 WHERE 判重字段 = ? FOR UPDATE;

• 严格控制事务执行时长，事务内不要放入任何外部IO操作（比如调用第三方接口、读写本地文件等），避免事务长时间挂起持有锁
• 所有异常场景必须加回滚逻辑，不管是SQL执行报错还是代码逻辑报错，都要主动调用rollback释放连接和锁，禁止出现异常后直接抛错不回滚的情况
• 给MySQL设置合理的事务超时时间，避免异常中断的事务长时间持有锁

代码示例：

const conn = await pool.getConnection();
try {
  await conn.beginTransaction();
  // 查询加行锁
  const [rows] = await conn.execute('SELECT * FROM 表名 WHERE unique_key = ? FOR UPDATE', [uniqueVal]);
  if (rows.length > 0) {
    await conn.execute('UPDATE 表名 SET 字段1 = ? WHERE unique_key = ?', [val1, uniqueVal]);
  } else {
    await conn.execute('INSERT INTO 表名 (字段1, unique_key) VALUES (?, ?)', [val1, uniqueVal]);
  }
  await conn.commit();
} catch (err) {
  // 异常必须回滚
  await conn.rollback();
  throw err;
} finally {
  // 无论成功失败都释放连接回连接池
  conn.release();
}

3. 死锁排查补充方向 #

如果调整后仍存在死锁，可开启MySQL死锁日志定位具体冲突来源：

SET GLOBAL innodb_print_all_deadlocks = 1;

查看死锁日志确认具体的锁冲突场景，按照上述两种方案调整基本都能解决。

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