如何保证一表数量总和不超过另一表值 解决并发写入超量问题

场景说明 #

存储弹珠库存的marble表结构与示例数据：

存储装袋记录的bag表存在(bag_id, marble_id)联合唯一约束，表结构与示例数据：

原有单场景可用的装袋SQL如下，并发场景下会出现总装袋量超过库存的问题：

WITH unbagged AS (
  SELECT
    marble.total - COALESCE( SUM( bag.quantity ), 0 ) AS quantity
  FROM marble
    LEFT JOIN bag ON marble.id = bag.marble_id
  WHERE marble.id = :marble_id
  GROUP BY marble.id )
  
INSERT INTO bag (bag_id, marble_id, quantity)
SELECT
  :bag_id,
  :marble_id,
  LEAST( :quantity, unbagged.quantity )
FROM unbagged
ON CONFLICT (bag_id, marble_id) DO UPDATE SET
  quantity = bag.quantity
    + LEAST(
        EXCLUDED.quantity,
        (SELECT quantity FROM unbagged) )

典型异常场景：总库存为3的swirly弹珠，并发请求下可能出现单个袋子装6个、两个袋子各装3个的超量情况。

原有SQL的核心漏洞 #

• CTE中unbagged的剩余库存计算基于事务快照读，并发场景下多个同时到达的事务会读到完全相同的剩余库存值，各自按该值计算写入量后必然超发
• 冲突更新分支复用了事务启动时计算的旧unbagged值，没有读取其他并发事务已经提交的最新装袋量，增量计算基于过期数据，结果不可靠

可靠实现方案 #

以下方案均可以100%保证bag表中同一种弹珠的quantity总和永远小于等于marble表对应total值。

方案1：行级锁强制串行操作（性能最优，无需改表结构） #

核心逻辑是操作前先锁定对应弹珠的库存行，强制同一种弹珠的装袋操作串行执行，从根源避免并发读不一致问题，SQL如下：

BEGIN;
-- 锁定目标弹珠的库存行，事务提交前其他同id操作会排队等待锁释放
SELECT id FROM marble WHERE id = :marble_id FOR UPDATE;

-- 插入/更新时实时计算剩余库存，不复用提前计算的快照值
INSERT INTO bag (bag_id, marble_id, quantity)
SELECT
  :bag_id,
  :marble_id,
  LEAST(:quantity, stock.remaining)
FROM (
  SELECT m.total - COALESCE(SUM(b.quantity),0) AS remaining
  FROM marble m
  LEFT JOIN bag b ON m.id = b.marble_id
  WHERE m.id = :marble_id
  GROUP BY m.id, m.total
) stock
ON CONFLICT (bag_id, marble_id) DO UPDATE SET
  quantity = bag.quantity + LEAST(
    EXCLUDED.quantity,
    -- 冲突更新时重新读取最新库存计算可用量
    (SELECT m.total - COALESCE(SUM(b.quantity),0)
     FROM marble m
     LEFT JOIN bag b ON m.id = b.marble_id
     WHERE m.id = :marble_id
     GROUP BY m.id, m.total)
  )
-- 最终校验兜底，避免计算逻辑偏差
WHERE (
  SELECT COALESCE(SUM(b.quantity),0)
  FROM bag b
  WHERE b.marble_id = :marble_id
) <= (SELECT total FROM marble WHERE id = :marble_id);

COMMIT;

注意：FOR UPDATE锁仅在显式事务内生效，禁止将上述逻辑拆分为无事务的单条SQL执行。

方案2：数据库CHECK约束强兜底（可靠性最高，兼容任意写入逻辑） #

如果不想在业务代码中显式加事务和锁，可以在数据库层面加约束做强制校验，从规则层面禁止超量写入。
首先创建库存校验函数：

CREATE OR REPLACE FUNCTION check_marble_stock(p_marble_id INT)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN (
    SELECT COALESCE(SUM(quantity),0)
    FROM bag
    WHERE marble_id = p_marble_id
  ) <= (SELECT total FROM marble WHERE id = p_marble_id);
END;
$$ LANGUAGE plpgsql STABLE;

然后给bag表添加校验约束：

ALTER TABLE bag ADD CONSTRAINT chk_marble_stock CHECK (check_marble_stock(marble_id));

约束生效后，任何导致总装袋量超过库存的写入都会直接抛出约束违反错误，事务自动回滚。应用层只需要捕获该类错误，返回库存不足即可。

该方案是最终兜底手段，哪怕业务代码存在逻辑漏洞，数据库层面也绝对不会出现超量情况。性能略低于方案1，但足以支撑绝大多数业务场景的并发量。

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