Sequelize执行INSERT后无法立即执行UPDATE问题的解决方法

问题根因 #

你遇到的时序问题核心是两个可能的原因：

1. 冗余的Promise嵌套导致await的时序控制失效，async函数本身会返回Promise，不需要额外包裹new Promise
2. 事务可见性问题：如果你的INSERT操作所在的事务未提交，后续的UPDATE操作无法读取到未提交的插入数据

修复方案 #

方案1：修复语法+显式控制事务（推荐） #

直接把三个操作放到同一个显式事务中，保证操作顺序和数据可见性，同时移除冗余的Promise包装：

const generateProductList = async () => {
  // 开启事务
  const t = await ProductPresentation.sequelize.transaction();
  try {
    await ProductPresentation.destroy({ truncate: true, transaction: t });

    const productSql = `INSERT INTO m_product_presentation (productId, sku, name, status, urlKey, category, shortDescription, imageSmall, imageThumbnail) 
    SELECT id, sku, name, status, urlKey, category, shortDescription, imageSmall, imageThumbnail FROM m_product;`;
    await ProductPresentation.sequelize.query(productSql, { 
      type: QueryTypes.INSERT,
      transaction: t
    });

    const priceSql = `UPDATE m_product_presentation INNER JOIN m_price
    ON m_product_presentation.productId = m_price.productId
    SET m_product_presentation.priceRrp = m_price.priceRrp, m_product_presentation.priceRegular = m_price.priceRegular, m_product_presentation.priceSpecial = m_price.priceSpecial;`;
    await ProductPresentation.sequelize.query(priceSql, { 
      type: QueryTypes.UPDATE,
      transaction: t
    });

    const stockSql = `UPDATE m_product_presentation INNER JOIN m_inventory
    ON m_product_presentation.productId = m_inventory.productId
    SET m_product_presentation.stockAvailability = m_inventory.stockAvailability, m_product_presentation.stockQty = m_inventory.stockQty;`;
    await ProductPresentation.sequelize.query(stockSql, { 
      type: QueryTypes.UPDATE,
      transaction: t
    });

    // 提交事务，所有操作一次性生效
    await t.commit();
  } catch(err) {
    // 出错回滚所有变更
    await t.rollback();
    logger.error(err);
    throw err;
  }
}

方案2：合并SQL语句（性能更优） #

你完全可以把插入+两次更新合并为单个INSERT语句，直接在SELECT阶段关联价格和库存表，一次性写入所有字段，避免多次数据库交互：

const generateProductList = async () => {
  const t = await ProductPresentation.sequelize.transaction();
  try {
    await ProductPresentation.destroy({ truncate: true, transaction: t });

    const insertAllSql = `
    INSERT INTO m_product_presentation (
      productId, sku, name, status, urlKey, category, shortDescription, 
      imageSmall, imageThumbnail, priceRrp, priceRegular, priceSpecial,
      stockAvailability, stockQty
    )
    SELECT 
      p.id, p.sku, p.name, p.status, p.urlKey, p.category, p.shortDescription,
      p.imageSmall, p.imageThumbnail, pr.priceRrp, pr.priceRegular, pr.priceSpecial,
      i.stockAvailability, i.stockQty
    FROM m_product p
    LEFT JOIN m_price pr ON p.id = pr.productId
    LEFT JOIN m_inventory i ON p.id = i.productId;
    `;
    await ProductPresentation.sequelize.query(insertAllSql, { 
      type: QueryTypes.INSERT,
      transaction: t
    });
    await t.commit();
  } catch(err) {
    await t.rollback();
    logger.error(err);
    throw err;
  }
}

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