Sequelize事务执行失败：锁等待超时问题求助

问题背景 #

现有三张表：Company、OperationMaster、OperationCompanyXref，其中OperationCompanyXref是关联表，Sequelize关联配置如下：

OperationsCompanyXref.belongsTo(Company, { foreignKey: 'companyId' });
OperationsCompanyXref.belongsTo(Operations, { foreignKey: 'operationId' });
Company.hasMany(OperationsCompanyXref, { foreignKey: 'companyId' });
Operations.hasMany(OperationsCompanyXref, { foreignKey: 'operationId' });

创建新Company时，通过事务同时插入Company和OperationCompanyXref，但执行时出现锁等待超时错误：

code: 'ER_LOCK_WAIT_TIMEOUT',
errno: 1205,
sqlState: 'HY000',
sqlMessage: 'Lock wait timeout exceeded; try restarting transaction',
sql: 'INSERT INTO OperationCompanyXrefs (id,status,createdAt,updatedAt,companyId,operationId) VALUES (DEFAULT,?,?,?,?,?);'

单独插入Company或手动插入OperationCompanyXref均正常，问题出在事务内的批量插入逻辑。

可行解决方案 #

1. 替换循环单条插入为批量插入 #

原代码循环调用create会多次发起数据库请求，拉长事务持有时间，增加锁冲突概率。改用bulkCreate一次性插入所有关联数据：

async function createCompany(companyObject) {
  let transaction = await db.sequelizeConnection.transaction();
  try {
    const createCompany = await db.Company.create(companyObject, { transaction });

    // 批量构造关联数据
    const xrefData = companyObject.operations.map(opId => ({
      operationId: opId,
      companyId: createCompany.id,
      status: true
    }));
    // 批量插入
    await db.OperationCompanyXref.bulkCreate(xrefData, { transaction });

    await transaction.commit();
    return { createCompany };
  } catch (err) {
    console.error(err);
    await transaction.rollback();
    return err;
  }
}

2. 调整事务隔离级别 #

MySQL默认隔离级别是REPEATABLE READ，可能导致锁范围过大。可以将事务隔离级别调整为READ COMMITTED，减少锁持有范围：

// 创建事务时指定隔离级别
let transaction = await db.sequelizeConnection.transaction({
  isolationLevel: db.Sequelize.Transaction.ISOLATION_LEVELS.READ_COMMITTED
});

3. 优化OperationCompanyXref表索引 #

确保关联字段有合适的索引，避免插入时全表扫描导致锁表：

• 给companyId和operationId创建联合索引：

CREATE INDEX idx_company_operation ON OperationCompanyXrefs(companyId, operationId);

• 也可根据实际查询场景，给两个字段分别创建单独索引。

4. 缩短事务执行时间 #

• 提前校验companyObject.operations中的operationId是否存在于OperationMaster表，避免事务内出现无效插入导致回滚延迟；
• 移除事务内不必要的日志、计算等耗时操作，确保事务只做必要的数据库操作。

5. 临时调整数据库锁超时配置（治标方案） #

如果以上方案暂时无法生效，可以临时调大MySQL的锁超时时间，但不建议长期依赖：

SET GLOBAL innodb_lock_wait_timeout = 60; -- 默认是50秒，调整为60秒

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