PostgreSQL 写偏斜:值班人数为何越查越错

两人同时退出值班,事务都成功却无人留守。用关键 SQL 对比班次守卫锁与 Serializable,拆解快照、整笔重试、热点容量和提交结果未知的边界。

两名工程师同时申请退出一个值班班次。页面都显示“当前还有两人在岗”,后端也分别检查过人数,两笔更新都成功提交。最后班次无人值守。日志没有脏读,没有某行被覆盖,也没有事务报错。问题不在于数据库漏掉一次写入,而在于系统把“每个人的状态更新正确”误当成了“整张值班表始终正确”。

本文用一个简化排班系统讨论这种写偏斜。目标只有一个:已启用班次必须至少保留一名值班人。它不是医疗决策系统,也不包含资质、休息时间等真实排班规则。所有改变在岗集合的操作都落在同一个 PostgreSQL 主库;异步副本只供展示,不能承担批准退出的判断。以下是基于 PostgreSQL 18 文档的设计与待执行数据库实验,不是生产压测报告。

先把要守住的条件写出来

我们不要求两位申请人都成功退出,也不要求后发请求一定失败。允许任何一人退出,另一人收到明确拒绝;不允许两人都收到成功。这个区别很重要:并发控制不是替业务选择谁留下,而是在业务尚未决定优先级时,保证结果仍然合法。

CREATE TABLE duty_shift (
  id bigint PRIMARY KEY,
  enabled boolean NOT NULL DEFAULT false
);
CREATE TABLE duty_member (
  shift_id bigint NOT NULL REFERENCES duty_shift(id),
  member_id bigint NOT NULL,
  on_call boolean NOT NULL,
  PRIMARY KEY (shift_id, member_id)
);
BEGIN;
INSERT INTO duty_shift(id) VALUES (7001);
INSERT INTO duty_member VALUES (7001, 11, true), (7001, 22, true);
UPDATE duty_shift SET enabled = true WHERE id = 7001;
COMMIT;

班次主键为并发协调提供一个稳定对象。成员主键避免同一个人在同一班次出现两条记录,却无法表达“这一组记录至少有一条为真”。在业务评审中,应把后者单独列为不变量,而不是期待字段约束顺带保护它。

还需明确生命周期:创建班次可以先以停用状态准备成员,再在受控事务中启用;停用班次是否允许清空,必须有另外的业务分支。下面的退出协议只处理已启用、已存在的班次。不能让成员删除接口、批量导入和管理员工具绕过协议,再指望普通退出接口兜底。

稳定快照为什么仍然会出错

PostgreSQL 的 Repeatable Read 防止幻读,但仍允许序列化异常;Serializable 才约束成功提交的事务集合具有某种串行执行的等价结果。这里的具体实现语义见官方隔离级别文档。不要把其他数据库同名级别的经验直接套过来。

在两个独立的 psql 会话中,先分别执行下面两条语句,确保两边都读完,再进行各自更新:

-- A、B 都先执行到这里,结果均为 2
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM duty_member
WHERE shift_id = 7001 AND on_call;

-- A 随后执行
UPDATE duty_member SET on_call = false
WHERE shift_id = 7001 AND member_id = 11;
COMMIT;

-- B 在自己的事务中执行,不要复制 A 的 member_id
UPDATE duty_member SET on_call = false
WHERE shift_id = 7001 AND member_id = 22;
COMMIT;

这组实验的预期是两次提交都能成功,最终人数为零。A 根据旧状态认为 B 会留下,B 根据同一个旧状态认为 A 会留下;它们写不同的行,所以“同一行更新冲突”并不是可靠的报警器。再次查询人数也没用:若判断仍基于原快照,重复查询只是更加确信过时的前提。

看串行结果就容易识别矛盾。若 A 先退出,B 检查时只剩一人,应当留下;若 B 先退出,A 应当留下。两人都退出不属于任何合法串行次序。因此排查这类问题,不应只搜索“丢失更新”,更要寻找业务决策中的集合读取:数量、总额、区间是否重叠,以及是否存在另一条能承担职责的记录。

把检查和更新写成一个带子查询的 UPDATE,并不会自动修复设计。若两边仍可从各自快照得到相同许可,语句更短不等于约束更强。真正需要改变的是竞争者之间的协调关系。

方案一:班次守卫行,把竞争范围收窄到业务对象

这个场景中,我会优先选择显式班次锁。每个涉及该班次在岗集合的写事务,先独占锁住 duty_shift 对应行,再读取成员、决定并写入。不同班次彼此独立,同一班次有意排队。下面函数是关键实现,使用 READ COMMITTED 调用;它假设调用方已做身份认证和授权。

CREATE OR REPLACE FUNCTION leave_duty(p_shift bigint, p_member bigint)
RETURNS text LANGUAGE plpgsql AS $$
DECLARE
  v_enabled boolean;
  v_on_call boolean;
  v_count bigint;
BEGIN
  SELECT enabled INTO v_enabled FROM duty_shift
  WHERE id = p_shift FOR UPDATE;
  IF NOT FOUND THEN RETURN 'shift_missing'; END IF;
  IF NOT v_enabled THEN RETURN 'shift_disabled'; END IF;

  SELECT on_call INTO v_on_call FROM duty_member
  WHERE shift_id = p_shift AND member_id = p_member;
  IF NOT FOUND THEN RETURN 'member_missing'; END IF;
  IF NOT v_on_call THEN RETURN 'already_left'; END IF;

  SELECT count(*) INTO v_count FROM duty_member
  WHERE shift_id = p_shift AND on_call;
  IF v_count <= 1 THEN RETURN 'last_member'; END IF;

  UPDATE duty_member SET on_call = false
  WHERE shift_id = p_shift AND member_id = p_member;
  RETURN 'left';
END;
$$;

BEGIN ISOLATION LEVEL READ COMMITTED;
SET LOCAL lock_timeout = '500ms';
SELECT leave_duty(7001, 11);
COMMIT;

函数的默认 VOLATILE 属性应保留,不应为了“优化”改成 STABLE。这里依靠锁之后的后续查询看见前一持锁者已提交的状态;函数内查询的快照行为可核对函数易变性说明。锁的语义与事务结束释放规则可参阅官方显式锁文档。客户端应等提交成功才返回退出成功,不能拿到函数返回值就提前通知。

为什么不直接锁所有当前在岗成员?那样需要保证查询命中的行集覆盖所有未来竞争路径,空集合、成员迁移和不同筛选条件都会增加证明难度。班次行则是一个不随在岗人数减少而消失的协调点。它不保证所有设计都最快,但让代码审查可以问一个明确问题:这条写路径是否先获得同一个班次锁?

一个容易忽视的陷阱是“提高”成 Repeatable Read 后照搬协议。假设事务早已读取其他数据并建立旧快照,随后等待班次锁;前一个事务只锁了班次行、更新了成员,守卫行本身没有改变。拿到锁不意味着旧快照自动刷新。本文明确使用 READ COMMITTED,不把隔离级别视为可随意替换的部署参数。若团队必须使用固定快照,应重新设计并验证,而不是把上面的证明继续沿用。

成员迁移涉及两个班次,必须按班次 ID 的固定顺序锁住两个守卫,再做源班次保底检查与目标班次写入。只锁来源会遗漏目标侧可能存在的容量规则;按请求参数顺序加锁则可能制造环形等待。规则越多,锁协议越需要成为模块接口,而不能散落在控制器中。

方案二:Serializable,让事务冲突触发整笔重算

当规则横跨多个集合、很难选择稳定守卫时,可以让相关写流程一致使用 Serializable。单笔事务仍必须写对业务逻辑:读取班次状态和成员状态,检查人数,再更新。数据库不会从字段名字猜出“至少一人”的需求。它负责排除无法与某种串行执行对应的成功组合,而不是为缺失的检查补代码。

复现实验时,先把两个成员恢复为在岗,把前面的 BEGIN 改成 SERIALIZABLE,仍让两边先完成读取后分别更新。预期至少一方遇到序列化失败,错误可能出现在后续语句或提交阶段,不能硬编码“第二个 COMMIT 必定失败”。失败方从头重新读时,会发现只剩一人,并正常返回业务拒绝。

这里的整笔重试必须执行带人数判断和拒绝分支的业务事务,不能原样重跑前面用于演示冲突的无条件 UPDATE;否则即使串行执行,也可能把最后一人改为退出。

应用重试必须包住读取、判断、更新与提交全过程,不能只补发失败的 UPDATE。官方失败处理说明列出了 40001,并强调重试整笔事务。下面是控制流伪代码,不是某个数据库驱动可直接运行的 API:

for attempt in 1..3:
    开始一个新的 SERIALIZABLE 事务
    try:
        读取并验证班次、成员和人数
        若最后一人:提交只读结果,返回业务拒绝
        更新目标成员;写入本事务的待通知事件
        提交
        返回成功
    catch error:
        尽力回滚并清理连接
        若 error.sqlstate != "40001":向上抛出
        若达到次数或请求总截止时间:返回可重试的繁忙结果
        在事务外做短暂随机退避

这里限制三次是示例策略,不是数据库保证,也不是最佳常数。若剩余请求时间不够完成一次事务,就应停止,避免用户已经超时、数据库却持续做无效尝试。认证、输入解析等无数据库副作用的步骤可提前完成,但影响决策的数据库查询必须留在重试闭包内。

我不会把短信、Webhook 或第三方排班 API 调用放进这个闭包。事务重算会重复执行应用代码,数据库回滚不能撤回外部消息。更稳妥的边界是把待通知事件与排班修改写进同一事务,提交后再投递;投递消费者仍需按事件 ID 去重。这只说明如何衔接,不意味着 Serializable 解决了跨系统恰好一次。

版本边界也不能省略:2026 年 11 月 12 日发布的 PostgreSQL 18.7、17.12、16.16、15.20、14.25 修复了 SERIALIZABLE 下 INSERT ... ON CONFLICT 对冲突行遗漏谓词锁的问题,未包含修复的旧版本可能放过本应触发序列化失败的并发更新。本文的 SELECT + UPDATE 示例没有使用这条路径;若将业务扩展为 UPSERT,应使用包含修复的版本,并针对实际冲突分支补做受控并发测试,不能把本文的推演当作该路径已经验证的证据。

两种方案付出的成本不同

守卫行方案把代价暴露为等待,Serializable 方案把一部分代价暴露为失败后重做。不能只比成功请求的平均耗时:被取消、重试耗尽和业务拒绝都应留在统计分母中。对排班这种单班次低频修改,我倾向于可解释的排队;对关联集合复杂、冲突较少的规则,统一序列化事务可能降低长期维护成本。

做一个纯容量预算:假设同一班次的持锁区间平均为二十毫秒,不考虑其他开销时,其串行处理能力的理想上界约为每秒五十次。若一次外部 HTTP 请求让持锁时间变成二百毫秒,上界便降到每秒五次。这些是算术假设,不是本机测试数字,也不是 PostgreSQL 性能结论。它们说明应先缩短临界区,而不是先扩大连接池。

一万个互不相关班次不等于一万个事务都应同时进入数据库。连接数仍受 CPU、存储和其他请求制约。但同一热门班次上的竞争,不会因为有一万个班次而自动平均掉。压测流量应包含热点分布、长尾事务和重复请求,均匀随机负载很容易掩盖真正的排队风险。

Serializable 的预算需要统计每个业务成功所消耗的总尝试数。若一百次业务操作产生一百五十笔数据库尝试,容量规划不能仍按一百笔估算。这里不能假设失败概率独立;冲突越高,重试越可能互相放大。观察到持续争用时,可以退回显式按班次排队,或者重新拆分真正独立的业务对象,而不是无限加重试。

故障恢复不能省略“不知道是否提交”

锁超时与最后一人拒绝不是同一结果。前者意味着本次没有完成判断,客户端可以稍后重试;后者意味着已按照当前规则拒绝退出。把两者都返回“系统错误”,会让运营人员无法判断应该扩容还是修改排班。

还有一类更棘手的情况:COMMIT 已发送,连接随即断开,客户端没收到确认。不能把它归成 40001 直接声称数据库回滚。应以业务请求 ID 查已记录结果,或者通过具有幂等语义的查询确认最终状态。示例函数中的 already_left 能避免重复退出,但它不能区分“本请求成功过”和“另一请求先替你退出”,需要强审计语义时必须另建请求结果记录。

线上权限也属于协议的一部分。若普通应用角色仍可任意更新 duty_member,新增脚本就可能绕过守卫。实际部署应收敛写入口、审查所有写角色,并设计安全的函数权限;本文没有为了演示方便提供 SECURITY DEFINER 模板,因为搜索路径、对象所有者和调用权限需要结合真实部署审计。不要把教学函数直接暴露给不可信客户端。

怎样证明修复不是碰巧没撞上

数据库验收至少需要三组受控交错,而不是循环跑一千次就宣布安全。第一组让两个事务都读到两人后再放行写入,复现基线错误;第二组让 A 持有守卫锁,确认 B 不能越过协调点,A 提交后 B 返回 last_member;第三组切换到 Serializable,捕获实际 SQLSTATE,完整重试后检查最终人数始终不少于一。

另外要分别测试不存在的班次、已退出的成员、停用班次、跨班次迁移,以及提交结果未知后的对账。客户端显示层可以缓存人数,但退出接口必须重新判断,不能接受浏览器提交的“当前人数为二”作为授权依据。测试断言应落在最终合法状态和对外响应一致性上,不要依赖某个请求固定成为赢家。

本次环境未安装 PostgreSQL 服务端与 psql,因此没有执行上述双会话 SQL,也没有测量真实锁等待、序列化冲突或吞吐量。实际执行的是 Python 状态模型,九项断言全部通过:枚举两种串行退出顺序,验证都留下一个人;构造两份共同旧快照,验证会让两人退出;再验证等待后仍沿用旧快照并不能修复错误。模型用于核查论证中的状态转移,不是数据库实现的替身。

这套设计的核心不在于隔离级别越高越好,而在于能明确指出:哪个业务条件必须一直成立,哪些操作能破坏它,竞争者通过哪个机制协调。能回答这三个问题,才有资格选择排队还是重试;回答不出来,把 BEGIN 后面的单词换掉通常只是延后下一次事故。

相关文章

评论

登录后即可参与讨论

暂无评论。