Sequelize事务中CompSubmission更新触发外键约束异常排查

问题描述 #

在Sequelize事务中执行update查询时，触发如下错误：

insert or update on table "comp_submissions" violates foreign key constraint "comp_submissions_comp_id_fkey"

提示comp未入库，但事务内已成功创建Comp实例，且使用comp.id成功创建了CompAddressRel；CompSubmission与CompAddressRel均以comp_id为外键，仅CompSubmission的更新操作失败。

原代码如下：

await sequelize.transaction(async (t) => {
  const comp = await Comp.create({
    ...req.body,
    submitted_by_id: req.user.id,
    is_files_uploaded: false,
    publishedAt: req.body.is_public ? new Date() : null,
    status: APPROVED
  }, { transaction: t })

  // 此操作正常
  await CompAddressRel.create({
    comp_id: comp.id,
    address_detail_pid: address.address_detail_pid,
    building_id: req.body.building_id,
    land_id: req.body.land_id,
    campaign_id: req.body.campaign_id,
    owner_id: req.body.owner_id,
    planning_permit_id: req.body.planning_permit_id
  }, { transaction: t })


  // 此操作失败
  await CompSubmission.update({
    status: APPROVED,
    comp_id: comp.id
  }, {
    where: {
      id: req.body.submission_id
    }
  }, { transaction: t })
})

问题原因 #

Sequelize的update方法仅接受两个参数：第一个是更新字段对象，第二个是包含where、transaction等配置的对象。原代码错误地将{ transaction: t }作为第三个参数传入，导致更新操作未绑定到当前事务。

事务内创建的Comp记录在事务提交前仅对同事务内的操作可见，未绑定事务的update操作无法访问这条未提交的记录，因此触发外键约束验证失败。

修复代码 #

将transaction配置移到第二个参数的对象中，与where同级：

await sequelize.transaction(async (t) => {
  const comp = await Comp.create({
    ...req.body,
    submitted_by_id: req.user.id,
    is_files_uploaded: false,
    publishedAt: req.body.is_public ? new Date() : null,
    status: APPROVED
  }, { transaction: t })

  // 此操作正常
  await CompAddressRel.create({
    comp_id: comp.id,
    address_detail_pid: address.address_detail_pid,
    building_id: req.body.building_id,
    land_id: req.body.land_id,
    campaign_id: req.body.campaign_id,
    owner_id: req.body.owner_id,
    planning_permit_id: req.body.planning_permit_id
  }, { transaction: t })


  // 修复后的更新操作
  await CompSubmission.update({
    status: APPROVED,
    comp_id: comp.id
  }, {
    where: {
      id: req.body.submission_id
    },
    transaction: t // 事务配置移至此处
  })
})

说明 #

修复后，update操作会在同一事务内执行，能够访问到事务中刚创建的Comp记录，外键约束验证即可通过。

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