如何在PostgreSQL中实现全有或全无的更新查询？

这是个非常典型的批量原子更新需求，既要支持可变数量的item_id（1-20个），又要保证「要么全部更新成功，要么一条都不更新」的一致性。PostgreSQL里有几种简洁的实现方案，完全不用复杂的CTE就能搞定，下面分场景给你详细说：

方案一：单条UPDATE语句（低并发场景首选） #

用NOT EXISTS子查询快速验证：只要存在任何一个目标item_id的quantity <= 0，就不会执行更新。这条语句简洁高效，直接支持数组参数传入，完美适配你动态item_id的需求：

UPDATE items
SET quantity = quantity - 1
WHERE user_id = $1
  AND item_id = ANY($2::int[]) -- $2是传入的item_id数组（比如ARRAY[5,6,7]）
  AND NOT EXISTS (
    -- 检查是否存在不符合条件的记录
    SELECT 1
    FROM items
    WHERE user_id = $1
      AND item_id = ANY($2::int[])
      AND quantity <= 0
  );

原理很简单：NOT EXISTS会先确认所有目标记录的quantity都大于0，只有满足这个前提，才会执行后续的更新操作。应用层只要把item_id打包成数组传入即可，不用拼接IN列表，非常灵活。

方案二：事务+行级锁（高并发场景必备） #

如果你的系统是高并发环境，方案一可能存在竞态条件：比如在NOT EXISTS检查完成后、更新执行前，其他事务修改了某个目标记录的quantity，导致原本符合条件的记录突然不符合，但更新还是执行了。这种情况下，用「事务+行级锁」的方式可以彻底避免这个问题：

BEGIN;

-- 先锁定所有目标记录，防止其他事务修改
SELECT 1 FROM items
WHERE user_id = $1 AND item_id = ANY($2::int[])
FOR UPDATE; -- 行级排他锁，锁定期间其他事务无法修改这些记录

-- 再次检查所有记录是否符合条件
IF (SELECT bool_and(quantity > 0) FROM items WHERE user_id = $1 AND item_id = ANY($2::int[])) THEN
  -- 全部符合条件才执行更新
  UPDATE items
  SET quantity = quantity - 1
  WHERE user_id = $1 AND item_id = ANY($2::int[]);
END IF;

COMMIT;

这个方案通过FOR UPDATE提前锁定所有目标记录，确保在检查和更新的过程中，没有其他事务能修改这些数据，完美保证了原子性。

关于你提到的CTE方案 #

你之前想的CTE方案其实也能实现，比如：

WITH valid_items AS (
  SELECT item_id
  FROM items
  WHERE user_id = $1 AND item_id = ANY($2::int[]) AND quantity > 0
),
validation AS (
  -- 验证有效记录数等于传入的item_id数量
  SELECT (SELECT COUNT(*) FROM valid_items) = array_length($2::int[], 1) AS can_update
)
UPDATE items
SET quantity = quantity - 1
WHERE user_id = $1
  AND item_id = ANY($2::int[])
  AND (SELECT can_update FROM validation);

但这个方案和方案一原理一致，并没有更优，反而不如NOT EXISTS的写法简洁直观，所以更推荐前面两种方案。

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