如何确保一个表中的金额总和小于另一个表的数量？

Question

的表格

假设我有一张大理石ID	颜色	总计
1	蓝色	5
2	红色	10
3	Swirly	3

，我需要将它们放入上唯一约束（BAG_ID，MARBLE_ID）的袋子中：

bag_id	marble_id	量
1 1	1 1 1 （蓝色）	2
1	2（红色）	3
2	1（蓝色）	2

我有一个查询，最多可以袋装剩余的大理石数量

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) )

，直到一天，它的剩余大理石数量很棒，它与同一物品同时被调用了两次我最终在一个袋子里放了6个swirly大理石（也许只有3个袋子中的3个），即使总共只有3个。

我想我明白了为什么，但是我不知道如何防止这种情况发生？

Answer 1

您的算法对我来说并不清楚，但是核心问题是 /strong> 。

手动锁定

查询过程一次在表大理石中的一个给定行。最便宜的解决方案是服用大理石和 bag 的唯一查询编写）。然后，下一个试图与同一种大理石混乱的交易必须等到当前的交易（或回滚）。

BEGIN;

SELECT FROM marble WHERE id = :marble_id FOR UPDATE;  -- row level lock

WITH unbagged AS ( ...

COMMIT;

serializable

或使用，这是更昂贵的“接收”解决方案 - 并准备在序列化误差时重复交易。喜欢：

BEGIN ISOLATION LEVEL SERIALIZABLE;

WITH unbagged AS ( ...

COMMIT;

相关：

• 如何原子替换表数据的子集

• 原子更新。