mysql2/promise+NodeJS下MySQL多表事务锁问题求助

你的核心问题是事务操作分散在连接池的不同连接上：mysql2/promise的db.query()每次都会从连接池取新连接，导致START TRANSACTION、后续增删改、COMMIT/ROLLBACK根本不在同一个会话里执行——等于你没开启有效的事务。同时循环单条插入会产生大量数据库IO，锁持有时间过长，进一步加剧锁冲突和冻结。

解决方案步骤 #

1. 绑定事务到单个连接 #

从连接池获取专属连接，整个事务生命周期都用这个连接执行，确保事务的原子性和会话一致性。

2. 优化批量操作 #

把循环单条插入改成批量插入，减少数据库交互次数，缩短锁持有时间，大幅提升性能。

修改后的代码示例 #

service.js（优化后） #

const db = require("../../config/database");

module.exports = {
  save: async (data, callBack) => {
    let connection;
    try {
      // 从连接池获取单个专属连接
      connection = await db.getConnection();
      
      // 设置会话级事务隔离级别（按需调整，非必须）
      await connection.query("SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");
      // 开启事务
      await connection.query("START TRANSACTION");

      // 执行表A的UPDATE操作
      await connection.query(`UPDATE QUERY FOR TABLE A`, [myParams]);

      // 执行各表的DELETE操作
      await connection.query(`DELETE QUERY FOR SOME DATA ON TABLE B`, [myParam]);
      await connection.query(`DELETE QUERY FOR SOME DATA ON TABLE C`, [myParam]);
      await connection.query(`DELETE QUERY FOR SOME DATA ON TABLE D`, [myParam]);
      await connection.query(`DELETE QUERY FOR SOME DATA ON TABLE E`, [myParam]);
      await connection.query(`DELETE QUERY FOR SOME DATA ON TABLE F`, [myParam]);
      await connection.query(`DELETE QUERY FOR SOME DATA ON TABLE G`, [myParam]);

      // 批量插入工具函数：将数组转为批量SQL语句
      const insertBatch = (table, cols, items) => {
        if (!items.length) return Promise.resolve();
        // 生成占位符
        const placeholders = items.map(() => `(${cols.map(() => '?').join(',')})`).join(',');
        // 扁平化参数数组
        const values = items.flat();
        return connection.query(`INSERT INTO ${table} (${cols.join(',')}) VALUES ${placeholders}`, values);
      };

      // 批量插入各表数据（替换为你的实际字段名）
      await insertBatch('table_b', ['col1', 'col2'], data.b);
      await insertBatch('table_c', ['col1', 'col2'], data.c);
      await insertBatch('table_d', ['col1', 'col2'], data.d);
      await insertBatch('table_e', ['col1', 'col2'], data.e);
      await insertBatch('table_f', ['col1', 'col2'], data.f);
      await insertBatch('table_g', ['col1', 'col2'], data.g);

      // 提交事务
      await connection.query("COMMIT");
      console.log("事务提交成功");
      return callBack(null, someReturnData);
    } catch (err) {
      console.error("事务执行失败，回滚中:", err);
      // 仅当连接已获取时执行回滚
      if (connection) await connection.query("ROLLBACK");
      return callBack(err);
    } finally {
      // 无论成功失败，必须释放连接回池，避免连接池耗尽
      if (connection) connection.release();
    }
  },
};

关键注意事项 #

• 强制释放连接：在finally块中释放连接，防止连接池耗尽导致后续请求冻结。
• 批量插入限制：MySQL默认有max_allowed_packet限制，若单次批量数据量过大，需拆分为多个小批量插入，或调整数据库配置。
• 隔离级别选择：READ UNCOMMITTED会带来脏读风险，仅业务允许时使用，否则建议保留默认的REPEATABLE READ。
• 异常覆盖：确保所有可能的异常都被捕获，避免连接泄漏。

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