PostgreSQL 多行约束为何失守:写偏差、Serializable 与守卫行验收

两个事务都读到条件满足、各自更新不同记录,提交后仍可能共同破坏业务规则。本文限定 PostgreSQL 18 单主库,以审批组至少保留一名有效成员为例,复现 Repeatable Read 下的写偏差,比较 Serializable 全事务重试与 Read Committed 守卫行锁,并给出旧快照、旁路写入、重试与提交结果未知的验收步骤。

如果业务规则依赖多行共同满足,稳定快照和单行更新成功并不足以保护它。需要让参与规则的完整读写决策具备可串行化语义,或让所有相关写路径在同一守卫对象上协调。本篇以 PostgreSQL 18、同一主库中的审批组成员管理为范围,给出双会话操作示例及预期结果;数据库保证还必须与实际连接、重试和权限路径一起验收。

把业务不变量写成可以检查的集合条件

设定一个审批组 G,属于租户 T,当前启用且有 A、B 两名有效审批成员。业务约定为:启用中的组,在每次成员变更提交后至少保留一名有效成员。这里只检查系统里的有效成员记录,不判断人员是否实际在线、是否具备处理某笔业务的授权,也不处理跨数据库的成员同步。

定义 N(T,G)=该租户该组中 active=true 的成员条数,要求启用组始终满足 N(T,G)≥1。移除一名仍有效的成员之前,应确认其存在且 N≥2;如果目标本来无效,应按约定返回无变化,不能再次减一。停用整个组属于另一项业务操作,应独立定义审批与流程,不能借停用标志绕过成员维护要求。

示例数据结构为 approval_group(tenant_id, group_id, enabled),联合主键是 tenant_id、group_id;approval_member(tenant_id, group_id, member_id, active),三列组成联合主键,并用前两列外键关联组。enabled 与 active 均为 boolean NOT NULL。初始化组 (T,G,true),成员 (T,G,A,true)、(T,G,B,true),核对数量为2。维护账户必须能在获授权租户内读取完整的成员集合;行级权限或视图若把部分成员藏起来,应先明确其对计数口径的影响。

主键避免重复身份,外键避免不存在的组,NOT NULL 避免状态含糊,但它们没有表达“至少一个有效成员”。PostgreSQL 18 的约束文档说明,普通 CHECK 不支持依赖被检查行之外的表数据来维持这种保证。[1] 把跨行计数藏在自定义函数中也没有自动解决并发问题;适合唯一或排他约束的规则应优先使用相应约束,本例则需要协调协议。

SQL 多查询快照一致性讨论的是一份读取结果是否混用了版本。本例更进一步:即使每个事务都看到完整、稳定的旧版本,两个分别正确的决定也可能组合成错误的提交状态。验收对象必须是所有成功事务提交后的业务不变量。

用两个会话复现:两次检查都通过,最终却无人审批

在隔离测试库初始化上述三条记录,打开连接一和连接二,各执行 BEGIN ISOLATION LEVEL REPEATABLE READ;。随后两边分别执行 SELECT count(*) FROM approval_member WHERE tenant_id='T' AND group_id='G' AND active;。用人工屏障或测试驱动确认两次查询都完成并返回2,再允许任何一方更新;只让两个线程“差不多同时开始”并不能稳定构造相遇顺序。

连接一在检查目标A有效后,执行 UPDATE approval_member SET active=false WHERE tenant_id='T' AND group_id='G' AND member_id='A' AND active RETURNING member_id;。连接二按相同方式将B改为false。两条语句各返回一个成员标识,随后分别COMMIT。生产代码应用绑定参数;这些固定字面量仅用于展示同一组测试输入。

在此受控顺序、没有额外触发器或约束的前提下,Repeatable Read允许两者都提交,因为它们没有更新同一成员行。新连接查询N得到0。每个事务在自己的快照中都认为另一个人仍有效,单独执行都能留下1人,并发结果却违反N≥1。这类依赖同一集合、修改不同记录的异常称为写偏差。

它不是丢失更新:A行和B行的修改都被保留,也没有读取未提交数据。PostgreSQL 18明确区分Repeatable Read的稳定快照与Serializable的可串行化保证;前者仍可能出现序列化异常。[2] 把两个更新改成同一事务内的条件语句,若条件仍只读取各自快照里的跨行数量,也不能凭语句更短就排除这次异常。

尝试排列两个操作即可检查矛盾:若A先移除,B随后会看到只剩自己而应被拒绝;若B先移除,A应被拒绝。不存在符合既定检查规则的串行顺序能让两者都成功。因此问题不在某一条UPDATE返回值,而在两个提交能否对应一条合法的完整决策顺序。

方案一:让完整决策进入 Serializable

把两条连接的事务改为 BEGIN ISOLATION LEVEL SERIALIZABLE;,其余仍在事务内读取组状态、目标成员和有效人数,判断后更新目标行并提交。必须从开始就使用该隔离级别,不是在读取旧数量后才临时升级,也不能先从报表缓存或副本取人数,再只把UPDATE包进Serializable事务。

重新安排双方先读到2、再修改不同成员的时间线,数据库不应允许这两个互相矛盾的事务都成功提交。通常一个事务完成,另一个收到序列化失败;失败可能在执行语句或提交阶段出现,不能写死一定由连接二、一定在COMMIT时报错。被中止的修改整体回滚,新的独立读取应仍看到至少一名有效成员。

Serializable Snapshot Isolation,简称SSI,是PostgreSQL实现可串行化的一种机制。Ports与Grittner的实现论文解释了读写依赖的危险结构,以及为什么系统可能保守地中止一些本可串行排列的执行。[3] 因而收到冲突不代表数据已经损坏,也不代表每次中止都对应一个最终会成环的完整历史。

本例中,连接一读到B仍有效,与连接二对B的修改形成依赖;连接二读到A仍有效,又与连接一修改A形成反向依赖。检查这些关系与只比较写入行是否相同不同。SSI的SIReadLock用于跟踪读取关系,不是把每个读取对象都变成阻塞写入的互斥锁。[2] 常规行更新仍可能发生锁等待,不能据此承诺Serializable完全不会等待。

该保证有两个前提:事务单独执行时遵守正确规则,且参与同一不变量的相关路径按一致策略运行。官方应用层一致性文档强调对相关读写的覆盖。[4] 若某个维护脚本不检查N便直接清空成员,串行执行它本来就会违反规则,Serializable不会替应用发明缺失的业务检查。

重试必须重做判断,不能重复旧结论

PostgreSQL以SQLSTATE 40001表示序列化失败,官方要求重试整个事务,包括决定执行哪些SQL及使用哪些值的逻辑。[5] 在本例里,失败者必须重新BEGIN、重读目标状态与N,再决定是否移除;不能保存旧的N=2,仅重试最后一条UPDATE。40001是重新决策的信号,不是要求不惜代价完成旧决定。

假定A已成功移除,B的事务失败。B整笔重试后读到N=1,应返回“会移除最后一名有效成员”的业务拒绝,并保持B有效。如果应用不断重试直到B也被设为false,即使每次数据库调用都成功,它也破坏了业务条件。业务拒绝、事务冲突和调用超时需要三个不同状态,便于用户与运行人员理解。

一种可实现的控制流为:保存稳定的业务意图与参数;在总期限内开启新事务;读取并检查;执行变更并保存结果;COMMIT成功后返回。捕获40001则回滚、丢弃本轮读数与待返回结果,有限退避后从事务起点重跑。达到次数或时间上限时返回可继续核查的状态,而非假装已执行。退避和期限由本系统负载与业务时限确定,没有通用安全次数。

40P01表示死锁,通常需要回滚后按完整协议考虑重试;唯一冲突、外键错误、无权限和非法参数则必须分类分析,不能都套用40001的处理。[5] 重试框架还应检查驱动是否自动开启了新事务,以及旧连接是否仍处在失败事务状态。只在异常捕获后再次发送SQL,可能一直得到事务已中止的错误。

不要在可重试事务内部发送邮件、调用外部审批接口或让模型执行不可逆工具动作。数据库回滚不会撤回这些副作用。需要通知时,可把待发事件与成员结果在同一事务中保存,提交后再按稳定事件身份发送;这里仍需单独的投递与去重机制。事务保护的是数据库决策,不是所有外部系统的共同提交。

方案二:在 Read Committed 下锁住一个稳定的守卫行

对于成员变更很少、组的边界稳定且写入口能够收敛的系统,可把approval_group中的组行作为守卫对象。所有会影响该组不变量的操作,先在同一连接、同一事务里锁定这个对象,再读成员集合并决定。守卫代表整个组的变更权,不是用某个恰好存在的成员充当临时锁。

具体顺序为:BEGIN ISOLATION LEVEL READ COMMITTED;,再执行 SELECT group_id FROM approval_group WHERE tenant_id='T' AND group_id='G' AND enabled FOR UPDATE;,确认恰好返回一行。随后以另一条SQL读取目标状态,再读取该组有效人数;条件满足才更新目标成员并COMMIT。若锁定结果为空,按组不存在或状态不允许处理,不能跳过锁继续改成员。

让A先取得守卫锁并完成移除,B在同一守卫上等待。A提交后,B获得锁;B之后开始的成员查询在Read Committed下能看到A已失效,N=1,于是拒绝移除B。若A回滚,B取得锁后读到N=2,可以移除B。两种顺序都保留至少一人;锁等待本身不是业务成功。

把“取得锁”和“检查人数”分成先后两条语句有具体意义。不要为了减少一次往返,将守卫锁与人数查询随意塞入同一条语句或CTE,并假定等待结束就刷新了该语句的所有读取视图。所需证据是:守卫锁确实到手之后,才开始用于决策的成员读取。读数、变更、提交也必须留在同一事务中。

更不能直接把这套锁流程放进已经建立旧快照的Repeatable Read事务。若守卫只被锁定、没有实际更新,等待者可能拿到锁后仍按旧快照看见两名有效成员。官方文档明确提醒锁定时机与快照时机的区别。[4] 本方案明确采用Read Committed的后续语句新视图;需要保留Repeatable Read时,应重新论证实际更新守卫版本与全事务重试等完整替代设计。

守卫协议的覆盖范围,决定它是否真能保护集合

行锁只保护被锁定的行,并不会自动为所有成员查询建立整个集合的禁止修改规则。[6] 因此成员新增、失效、删除、批量导入、跨组迁移和相关组配置变更,都必须走同一守卫协议。后台批处理或管理员脚本若直接改成员,即使普通接口测试通过,集合仍可能失守。权限可收敛到受控过程,具体授权应结合真实维护流程制定。

守卫应预先存在,并以租户与组的联合键唯一定位;不能对不存在的组执行SELECT FOR UPDATE后把零行当作已经获得一把锁。建立新组时,可在一笔事务中写入组与首名有效成员,满足初始化条件后再对外可用。允许空组草建时,应把未启用状态与正式启用验收明确分开,不能让初始非法状态混入本例的保证范围。

跨组迁移同时影响来源与目标,应按统一的租户、组键顺序取得两个守卫,并在一个事务内完成检查与变更。统一顺序减少反向等待;PostgreSQL锁文档将一致锁序作为避免死锁的重要方法。[6] 原组只剩一名成员时,必须按业务流程补充成员或拒绝迁移,不能先提交原组移除再等待目标组写入。

如果把有效人数另外存为计数器,可用单行条件更新表达数量约束,但计数器、成员明细与所有状态转移必须原子维护,并持续对账。它可能减少扫描,代价是新增派生状态与修复责任。不要为了套用单行方案而悄悄改变业务口径:禁用成员、删除成员和停用整个组并非同一个减数操作。

两条主要路径的取舍因此很明确:守卫锁把同组变更排队,便于解释,却要求完整盘点写入口;Serializable允许不同操作先并行判断,通过冲突中止保护合法顺序,却要求可重放事务与重试容量。关系复杂、访问集合变化频繁时应重点评估后者;少量清楚的聚合根、短事务且热点可控时,前者往往更易落实。两者都不能替代授权。

六组失败验收:从触发条件到修正后的证据

第一组,复现原异常。输入为两名有效成员,屏障让两个Repeatable Read事务都先读到2,再分别移除不同成员。预期基线可能双成功并得到0;切到Serializable后,不允许双成功,失败者整笔重试应读到1并拒绝。保留每条语句完成顺序、实际隔离级别、SQLSTATE与最终成员表。若修复后仍为0,检查是否读取发生在事务外或有旁路写入,再交换两边提交顺序复验。

第二组,验证守卫等待和回滚。A持守卫锁完成未提交移除,B尝试获取同一锁。预期B在取得锁前不能进入有效人数判断;A提交后B读到1并拒绝,A回滚后B读到2可继续。证据包括两个后端连接标识、等待阶段和成员状态。若B提前判断,修正事务对象传递、连接池与语句顺序,再分别执行提交和回滚分支。

第三组,主动暴露旧快照。把守卫方案误配成Repeatable Read,让B在A提交前查询成员建立快照;A完成移除,B再取得仅被锁过的守卫并读人数。预期这个错误实现可能仍读到2,从而违反规则。将它修为规定的Read Committed流程后,应读到1。保留快照建立顺序与守卫是否实际更新的证据,不能仅凭“日志里有FOR UPDATE”判定修复。

第四组,检查未覆盖入口。让批量失效脚本与常规移除同时作用于同一组。合格系统应让脚本遵守相同协议,或由权限拒绝不受控写入;不得留下启用且N=0的组。发现旁路后先限制相关入口,再补协议与最小权限,使用同一脚本和并发时序复验,并抽查其他维护任务,而非只修改前台接口。

第五组,检查重试与副作用。故意触发40001,让初次读数是2、重试时是1。预期业务拒绝,成员不再减少,任何外部通知都不应代表一次被回滚的成功。记录尝试编号、同一业务意图、每轮读数和提交结果;若只重试UPDATE或重复发出动作,修正重试边界和提交后投递,再在更新后、提交前等位置触发失败复验。

第六组,检查零行、跨租户和迁移死锁。不存在的组、已失效目标或错误租户不能变成无条件写入;反向迁移先按错误锁序构造等待,再用统一锁序复验。预期是可解释的无变化、业务拒绝或回滚,最终每个启用组仍满足N≥1。检查锁键包含租户、目标更新行数与整笔回滚,不以接口返回200替代数据库证据。

提交结果未知与运行容量,要留在交付设计里

COMMIT后连接断开,与数据库明确返回40001不同。前者可能已经成功提交,不能直接宣布回滚后换一个业务请求执行。对于需要稳定回复的成员变更,可在同一事务内保存以租户、request_key唯一标识的操作结果与参数摘要;恢复时先从主库按原键查询,并核对参数。未确认结果时保持未知状态,不能从滞后副本读到空记录便认定失败。

状态型操作看似重复设置false无害,也可能跨越重新启用事件产生副作用。因此需要区分“重取上次结果”和“现在再次提出移除”。请求账本与成员当前状态各有用途,旧键应返回原操作结果而非重新判断新的业务意图。账本写入也属于事务的一部分,异常时与成员变更一起回滚;保留期应覆盖允许重试的业务窗口。

容量评估应记录每个业务意图的总尝试数、40001与死锁数量、守卫等待、事务总时长及最终成功或拒绝。举例设定1000个原始意图,产生150次额外尝试,则数据库实际执行1150次,额外尝试/原始意图为15%。它不等于15%的意图发生重试,因为少数意图可能多次重试;这两个分母要分别统计。

热点组的守卫必然限制同组并发,增加连接不会让它同时允许多个相互冲突的修改。Serializable则可能因热点、扫描范围或长事务增加冲突开销。应使用真实组大小与热点分布测试,分别观察排队和重复工作。论文中的旧版本基准不能作为本系统的吞吐承诺,索引与连接数调整也应依据实际执行计划和测量。

事务内只保留维护不变量所需的操作,不等待模型长文本、人工确认或远端系统响应。对异常时长设置可解释的上限,超时后确认事务与连接已得到处理。上线先清点所有变更入口、核对现存启用组,再按同一协议逐步切换;回退也要维持一个完整受控路径,不能混用半套守卫和半套旧检查。最终交付物应包含不变量、两会话时间线、写入口清单、失败复验记录与恢复责任人。

参考资料

  1. [1] PostgreSQL 18 — Constraints(查阅2026-11-22)
  2. [2] PostgreSQL 18 — Transaction Isolation(查阅2026-11-22)
  3. [3] Ports and Grittner — Serializable Snapshot Isolation in PostgreSQL(PVLDB 2012)
  4. [4] PostgreSQL 18 — Data Consistency Checks at the Application Level(查阅2026-11-22)
  5. [5] PostgreSQL 18 — Serialization Failure Handling(查阅2026-11-22)
  6. [6] PostgreSQL 18 — Explicit Locking(查阅2026-11-22)
返回洞察
鲁ICP备2024109755号-2
可拖动移动。右键、长按或按 Shift+F10 可选择停靠位置。