PostgreSQL数据库批量更新全量回滚实现方案咨询

方案1：显式事务包裹全量操作（最推荐） #

PostgreSQL 原生支持ACID事务的原子性，你只要放弃逐条提交的逻辑，把所有更新和校验操作都放在同一个显式事务内即可：

• 操作前执行BEGIN命令关闭自动提交，开启事务
• 逐行执行校验、更新逻辑，全程不单独提交
• 所有待更新记录全部处理完成且校验通过后，再执行COMMIT一次性提交所有变更
• 任意步骤出错时直接执行ROLLBACK，事务内所有未提交的变更会全部清除，不会残留任何中间数据

如果不想在应用层控制事务，也可以用PL/pgSQL封装逻辑，校验失败抛出异常即可自动触发回滚，示例代码：

DO $$
DECLARE
  rec record;
BEGIN
  -- 遍历所有待更新记录
  FOR rec IN SELECT id, 需要校验的字段 FROM 目标表 WHERE 筛选条件 LOOP
    -- 逐行校验逻辑，不满足则抛出异常
    IF rec.校验字段 不符合规则 THEN
      RAISE EXCEPTION '记录ID % 校验失败', rec.id;
    END IF;
    -- 执行更新
    UPDATE 目标表 SET 待更新字段 = 新值 WHERE id = rec.id;
  END LOOP;
-- 捕获异常自动回滚
EXCEPTION
  WHEN OTHERS THEN
    RAISE NOTICE '处理失败，已回滚所有变更：%', SQLERRM;
    -- 抛出异常会自动触发整个事务回滚
    RAISE;
END $$;

方案2：临时表预校验（适合怕长事务锁表的场景） #

如果担心大事务长时间持有表锁影响其他业务，可以先做预校验，确认全部没问题再执行更新：

• 新建临时表，把所有待更新的记录和目标更新值全量导入
• 在临时表内完成所有逐行校验逻辑，全程不碰正式业务表
• 确认所有记录校验通过后，开启短事务，一次性用临时表的数据批量更新正式表，完成后提交
• 即使更新阶段出错，短事务回滚也不会残留中间数据

方案3：标记清理兜底（实在无法使用事务的场景） #

如果业务限制必须分批次提交，可以加清理机制兜底：

• 处理前先把所有待更新记录的原始值备份到临时表，同时给待处理记录加处理状态标记
• 逐条提交更新，同步修改对应记录的状态为「已处理」
• 一旦中途出错，直接用临时表备份的原始值，把所有标记为「已处理」的记录批量恢复到更新前的状态

注意事项 #

• 日常处理批量更新前，一定要先关闭客户端的自动提交功能，避免误提交
• 数据量超过10万级的场景优先用方案2，避免长事务导致的锁等待、WAL日志膨胀问题

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