Sequelize批量增减多列：电商订单批量扣减库存方案咨询

核心设计原则 #

• 保证原子性：所有商品库存扣减要么全部成功，要么全部回滚，杜绝部分扣减导致的数据不一致
• 防止超卖：扣减前必须校验每个商品的可用库存≥订单购买量
• 性能优先：尽量用单条批量SQL完成操作，减少数据库交互次数

数据库层面实现（以MySQL为例） #

1. 批量扣减SQL语句 #

利用CASE WHEN实现批量更新，同时在WHERE子句中做库存校验，从底层避免超卖：

BEGIN; -- 开启事务

UPDATE packageTypes
SET availableQuantity = CASE
    WHEN id = 1 THEN availableQuantity - 2 -- 商品ID1，购买2件
    WHEN id = 3 THEN availableQuantity - 1 -- 商品ID3，购买1件
    ELSE availableQuantity
END
WHERE id IN (1, 3)
AND availableQuantity >= CASE
    WHEN id = 1 THEN 2
    WHEN id = 3 THEN 1
    ELSE 0
END;

-- 校验更新行数是否匹配订单商品数，不匹配则回滚
IF ROW_COUNT() != 2 THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;

2. 索引优化 #

为packageTypes表的id字段保留主键索引（默认已存在），给availableQuantity添加普通索引，提升库存校验和更新的执行效率。

代码层面示例（Java + MyBatis） #

假设订单详情数组结构如下：

// 订单详情实体
class OrderItem {
    private Integer packageTypeId; // 对应packageTypes表的id
    private Integer quantity; // 购买数量

    // 构造器、getter/setter省略
}

// 订单详情示例数组
List<OrderItem> orderItems = Arrays.asList(
    new OrderItem(1, 2),
    new OrderItem(3, 1)
);

Mapper层SQL映射 #

<update id="batchDeductStock">
    BEGIN;
    UPDATE packageTypes
    SET availableQuantity = CASE
        <foreach collection="orderItems" item="item" separator=" ">
            WHEN id = #{item.packageTypeId} THEN availableQuantity - #{item.quantity}
        </foreach>
        ELSE availableQuantity
    END
    WHERE id IN
    <foreach collection="orderItems" item="item" open="(" separator="," close=")">
        #{item.packageTypeId}
    </foreach>
    AND availableQuantity >= CASE
        <foreach collection="orderItems" item="item" separator=" ">
            WHEN id = #{item.packageTypeId} THEN #{item.quantity}
        </foreach>
        ELSE 0
    END;
    <if test="orderItems.size() > 0">
        IF ROW_COUNT() != #{orderItems.size()} THEN
            ROLLBACK;
        ELSE
            COMMIT;
        END IF;
    </if>
</update>

业务层调用 #

@Transactional(rollbackFor = Exception.class)
public void processOrderStockDeduction(List<OrderItem> orderItems) {
    int affectedRows = packageTypeMapper.batchDeductStock(orderItems);
    if (affectedRows != orderItems.size()) {
        throw new RuntimeException("部分商品库存不足，订单处理失败");
    }
}

关键注意事项 #

• 事务隔离级别：建议使用READ COMMITTED，平衡数据一致性和性能，避免不可重复读问题
• 并发场景处理：高并发下可采用乐观锁优化，给packageTypes表添加version字段，更新时带上版本号：

  UPDATE packageTypes
  SET availableQuantity = availableQuantity - #{quantity}, version = version + 1
  WHERE id = #{packageTypeId} AND availableQuantity >= #{quantity} AND version = #{version};
  

• 库存校验时机：订单创建前可做预校验，但最终扣减必须在SQL中再次校验，避免预校验后到扣减前的窗口内库存被占用
• 异常处理：扣减失败需立即回滚事务，给前端返回明确错误提示

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