postgresql事务ID回卷问题（事务 ID 用尽）

postgresql事务ID回卷问题（事务 ID 用尽）

目录

• postgresql事务ID回卷问题（事务 ID 用尽）
  • 一、什么是事务id回卷
    • 1. 事务ID（XID）机制
    • 2. 可见性规则
    • 3. 回卷风险
    • 4. 内核保护阈值
    • 5. 预防措施
    • 6. 抢救步骤（若已只读）
    • 总结
  • 二、为什么会出现“回卷”
  • 三、内核保护阈值（代码写死，不可改）
  • 四、日常巡检：
  • 四、预防：让 freeze 提前、加速、不掉队
  • 五、真·回卷宕机后如何抢救

一、什么是事务id回卷

事务ID回卷（Transaction ID Wraparound）是PostgreSQL中由于事务ID（XID）循环使用而导致的一种机制，可能引发数据可见性问题或数据库只读锁定。以下是其关键点：

1. 事务ID（XID）机制

• PostgreSQL使用32位无符号整数存储XID，最大值为42亿（2³²-1）。
• XID按顺序分配，用完后会循环（从3重新开始）。

2. 可见性规则

• 通过比较XID的差值判断数据版本可见性。
• 若差值超过21亿（2³¹），旧数据会被误判为“未来事务”，导致不可见。

3. 回卷风险

• 数据消失：未冻结的旧记录可能突然不可查询。
• 数据库只读：当最旧活跃XID接近21亿时，PostgreSQL强制进入只读模式，报错：
  ERROR: database is not accepting commands to avoid wraparound failure。

4. 内核保护阈值

• 警告阈值：xidWarnLimit（约21亿-1000万）时触发日志警告。
• 停止阈值：xidStopLimit（约21亿-100万）时拒绝新XID分配。

5. 预防措施

• 冻结（Freeze）：通过VACUUM FREEZE或autovacuum将旧XID标记为“冻结”，避免参与比较。
• 参数调优：

autovacuum_freeze_max_age = 1.2亿（提前触发冻结）。
vacuum_freeze_min_age = 5000万（立即冻结阈值）。

• 监控：定期检查最旧XID年龄（如age(datfrozenxid)），确保剩余空间充足（如>1000万）。

6. 抢救步骤（若已只读）

• 1.单用户模式启动：postgres --single -D $PGDATA mydb。
• 2.执行手动冻结：VACUUM FREEZE;。
• 3.重启数据库并调整参数防复发。

总结

事务ID回卷是PostgreSQL的MVCC机制与有限XID空间共同作用的结果。通过提前冻结和持续监控可有效避免数据库锁定或数据不可见问题。

二、为什么会出现“回卷”

• 1.PostgreSQL 所有行版本可见性靠 32 位无符号事务号（XID）判断，上限 42 亿（2³²-1）。
• 2.事务号顺序派发，用完后会从 3 重新开始循环。
• 3.MVCC 比较规则靠“XID 差值转有符号 int32”——
  差值 > 2 147 483 647（21 亿）就被认为“未来事务”。
• 4.如果某条老记录在它“被看成未来”之前没做 freeze，那么整条记录对任何事务都不可见，表现为数据突然消失；再严重点，实例直接拒绝分配新 XID，所有写入报 ERROR: database is not accepting commands to avoid wraparound failure，业务停摆。

三、内核保护阈值（代码写死，不可改）

xidStopLimit        = (2³¹ – 10 000 000)   ≈ 21 亿 – 1 千万
xidWarnLimit        = xidStopLimit – 1 千万
xidEmergencyLimit   = xidStopLimit – 1 百万

• 当最旧活跃 XID 与 nextXid 距离 ≥ xidWarnLimit 时，日志出现（也就是剩余100万时）
  WARNING: database "xx" must be vacuumed within XXX transactions
• 距离 ≥ xidStopLimit 时强制进入只读。
• 只有单用户模式 postgres --single 或超级用户后台可以执行 VACUUM 抢救。

四、日常巡检：

-- 1. 实例级“年龄”
SELECT datname, age(datfrozenxid) AS age,
       2^31 - age(datfrozenxid) AS remaining_before_stop
FROM pg_database
ORDER BY age DESC;

-- 2. 表级 TOP10
SELECT nspname, relname, age(relfrozenxid) AS age,
       2^31 - age(relfrozenxid) AS remaining
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind IN ('r','m') AND age(relfrozenxid) > 1e8
ORDER BY age DESC
LIMIT 10;

-- 3. 是否已有自动冻结阻塞
SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE backend_type = 'autovacuum worker'
  AND wait_event_type = 'Lock';

remaining_before_stop < 1 千万就准备通宵加班。

四、预防：让 freeze 提前、加速、不掉队

1.参数（9.6+ 通用，2025 仍有效）

autovacuum_freeze_max_age = 1.2亿   (默认 2 亿，提前触发)
vacuum_freeze_min_age     = 5千万   (单次 vacuum 立即 freeze 的阈值)
autovacuum_work_mem       = 1GB     (给 worker 足够内存，防磁盘排序)
maintenance_work_mem      = 2GB
log_autovacuum_min_duration = 0     (记录所有 freeze 耗时，方便审计)

2.对超高频写的大表，用“分段 freeze”降低锁冲击

VACUUM (FREEZE, SKIP_LOCKED) big_table;
13+ 可用 VACUUM (PARALLEL 4, FREEZE)。

3.长事务、复制槽、prepared transaction 是三大“vacuum 杀手”，必须加告警：

SELECT pid, usename, state, backend_xmin, backend_xid,
       now() - xact_start AS xact_duration,
       query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
  AND now() - xact_start > interval '15 min';

五、真·回卷宕机后如何抢救

现象：
所有写 SQL 报 ERROR: database is not accepting commands to avoid wraparound failure
步骤：
1.立刻取消业务写入口，防止雪崩。
2.超级用户连入，先看最老年龄：

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

3.若 age 已 > 21 亿，

• a) 关闭实例
• b) 单用户模式启动

postgres --single -D $PGDATA mydb

c) 手工 full freeze

VACUUM FREEZE;

d) 退出单用户，正常启动。
4.事后把 autovacuum_freeze_max_age 调小，防止二次踩坑。