报表连接读完一页后idle in transaction六小时。它不更新数据也没有阻塞写锁订单表却不断膨胀VACUUM 每次都跑却删不掉旧版本。锁回答“现在谁能操作对象”快照 horizon 回答“哪些旧版本仍可能被看见”。只读长事务不必阻塞 UPDATE也能阻止 VACUUM 回收。双会话复现写入不阻塞清理却受阻在测试库准备数据DROPTABLEIFEXISTSorder_state;CREATETABLEorder_state(idbigintPRIMARYKEY,statustextNOTNULL,notetextNOTNULL);INSERTINTOorder_stateSELECTg,created,repeat(x,200)FROMgenerate_series(1,200000)ASg;VACUUM(ANALYZE)order_state;会话 A 建立并固定旧快照BEGINISOLATIONLEVELREPEATABLEREADREADONLY;SELECTcount(*)FROMorder_state;-- 第一次查询取得事务级快照-- 保持事务打开会话 B 更新全部行然后普通 VACUUMUPDATEorder_stateSETstatuspaid;VACUUM(VERBOSE,ANALYZE)order_state;SELECTn_live_tup,n_dead_tup,last_vacuumFROMpg_stat_user_tablesWHERErelidorder_state::regclass;UPDATE 可以完成因为会话 A 没持有与之冲突的业务行写锁。但 A 的快照仍需看到更新前的created版本VACUUM 不能删除它们。会话 A 验证快照仍旧SELECTstatus,count(*)FROMorder_stateGROUPBYstatus;COMMIT;会话 A 提交后在 B 再执行VACUUM(VERBOSE,ANALYZE)order_state;旧版本此时才有机会回收。对比两次 VACUUM 的 verbose 输出、dead tuple 趋势和 relation 尺寸可以观察 horizon 的影响。实验能证明固定旧快照会推迟旧版本回收不能证明生产中的唯一保留者就是该会话也不能保证普通 VACUUM 后文件缩小——可复用空间与归还文件系统仍是两回事。READ COMMITTED 为什么也可能出问题REPEATABLE READ明确固定事务快照最容易复现。READ COMMITTED通常每条命令取得新快照但事务仍可能因为正在执行的长查询、游标、导出快照等保持资源和可见性边界。因此排障不能只按隔离级别猜测要看pg_stat_activity.backend_xmin、事务开始时间、当前状态和具体工作负载。xact_start很老但backend_xmin为空也不能据此断言它正在挡住 tuple 回收两者是不同证据。找到真正的 horizon 保留者普通 backendSELECTpid,usename,application_name,client_addr,state,xact_start,query_start,backend_xmin,now()-xact_startASxact_age,wait_event_type,wait_event,left(query,200)ASquery_sampleFROMpg_stat_activityWHERExact_startISNOTNULLORDERBYxact_start;这是只读检查但 SQL 文本和客户端地址属于敏感运行信息应限制展示范围。异常信号是长时间idle in transaction、很老的backend_xmin或持续运行的大查询下一步是定位应用所有者和事务用途而不是立即pg_terminate_backend。Prepared transactionSELECTtransaction,gid,prepared,owner,databaseFROMpg_prepared_xactsORDERBYprepared;两阶段提交中已经 PREPARE、尚未 COMMIT/ROLLBACK 的事务可能长期保留资源。处理前必须由事务协调器和业务账务确认最终决议猜测提交或回滚都可能破坏原子性。Replication slotSELECTslot_name,slot_type,active,active_pid,xmin,catalog_xmin,restart_lsn,confirmed_flush_lsn,inactive_since,wal_status,invalidation_reasonFROMpg_replication_slots;xmin是槽要求保留的最老事务catalog_xmin约束系统目录 tuple 清理restart_lsn则约束 WAL 文件保留。它们可能同时出现但不是同一种债务。Standby feedback开启hot_standby_feedback的副本会把其查询需要的 horizon 反馈到上游减少副本查询因清理冲突被取消却可能让主库膨胀。这里的取舍是“取消副本长查询”与“保留主库旧版本”不是免费消除冲突。两条债务链不要混为一个复制延迟旧 snapshot / xmin / catalog_xmin → tuple 仍可能被需要 → VACUUM 不能删除 → heap、索引与 catalog 债务 restart_lsn → 消费者仍可能需要旧 WAL → checkpoint 不能回收相关 WAL → pg_wal 容量债务一个 CDC 任务可能同时造成两条链也可能只造成其中一条。主从 replay lag 为零不能排除闲置逻辑槽pg_wal正常也不能排除catalog_xmin正在拖住系统目录。为什么只查阻塞锁一定会漏SELECT*FROMpg_locksWHERENOTgranted;结果为空只说明当前没有等待授予的锁。MVCC 允许读者和写者在很多场景并发因此旧快照造成的危害恰恰可能没有锁告警。同理VACUUM进度到 100% 只证明扫描完成不证明所有旧版本都达到可删除条件。要把活动会话、VACUUM verbose 日志、dead tuple 趋势和业务事务生命周期串起来。生产处置先确认所有者再改变状态只读证据建立时间线磁盘/查询退化何时开始哪个 backend 或 slot 的 horizon 同期变老。核对应用连接池、报表、CDC 和 prepared transaction 所有者。对目标表比较 UPDATE 速度、dead tuple 斜率与 VACUUM 输出。区分 tuple 保留和 WAL 保留分别量化业务风险。最小止血先阻止新的超长报表事务和非必要批量 UPDATE给 autovacuum 追赶窗口。若必须终止某个会话要先确认查询可重试、事务只读且不存在客户端依赖并从最明确的单个 PID 灰度。终止会话只解除当前 horizon不会自动压缩文件也不会修复连接池继续泄漏事务的根因。根因修复ALTERROLE report_userSETidle_in_transaction_session_timeout5min;ALTERROLE report_userSETstatement_timeout30min;超时值必须根据真实导出和事务 SLA 设计。idle_in_transaction_session_timeout处理事务内空闲statement_timeout处理执行过久的语句二者不能互相替代。应用还应做到连接归还池前显式提交或回滚不把事务跨 HTTP 请求或用户思考时间大报表用可恢复分页、批量导出或专用副本而非无限固定快照prepared transaction 有协调器、超时告警和人工决议流程每个 slot 有负责人、消费 SLA、容量上限与重建手册。删除 replication slot 会丢失连续消费位点CDC 可能必须重新快照ROLLBACK PREPARED可能与协调器决议冲突。这些都需要独立授权、恢复点和业务对账不能写进自动清理脚本。验收与停止条件修复后同时验证最老backend_xmin恢复到合理窗口、dead tuple 净斜率转负、VACUUM 能移除版本、业务报表仍完成、CDC 无缺失重复、WAL 与磁盘水位稳定。若终止会话后业务错误率上升、报表不可恢复或下游重新快照能力不确定应停止扩大处理范围。回滚超时配置可以恢复更长事务窗口却不能复活已终止的会话或已删除的 slot。证据边界证据能证明不能证明没有 waiting lock当前无锁等待没有旧快照阻止清理backend_xmin很老backend 保持较老 horizon终止它一定无业务影响VACUUM 跑完扫描任务结束所有版本已删除、文件已缩小slotactive false当前无流式消费者slot 不保留 tuple 或 WAL结束事务后可清理量上升该事务是重要保留者系统不存在其他保留者面试表达主线MVCC 旧版本只有在不再可能被任何相关快照看到时才能回收。只读长事务虽不阻塞 UPDATE却可通过backend_xmin推迟 VACUUMprepared xact、复制槽和 standby feedback 也能保持 horizon。排障不能只查锁要同时查 activity、prepared xacts、slots 与 VACUUM 证据。实验清理确认会话 A 已提交或回滚后执行DROPTABLEIFEXISTSorder_state;官方资料PostgreSQL 18MVCCPostgreSQL 18pg_stat_activityPostgreSQL 18pg_replication_slotsPostgreSQL 18Client Connection DefaultsPostgreSQL 18Hot Standby FeedbackPostgreSQL 18.6 源码标签 REL_18_6
PostgreSQL 事务排障实战（第 8 篇）：没有锁等待，只读事务为什么仍能拖胖整库
报表连接读完一页后idle in transaction六小时。它不更新数据也没有阻塞写锁订单表却不断膨胀VACUUM 每次都跑却删不掉旧版本。锁回答“现在谁能操作对象”快照 horizon 回答“哪些旧版本仍可能被看见”。只读长事务不必阻塞 UPDATE也能阻止 VACUUM 回收。双会话复现写入不阻塞清理却受阻在测试库准备数据DROPTABLEIFEXISTSorder_state;CREATETABLEorder_state(idbigintPRIMARYKEY,statustextNOTNULL,notetextNOTNULL);INSERTINTOorder_stateSELECTg,created,repeat(x,200)FROMgenerate_series(1,200000)ASg;VACUUM(ANALYZE)order_state;会话 A 建立并固定旧快照BEGINISOLATIONLEVELREPEATABLEREADREADONLY;SELECTcount(*)FROMorder_state;-- 第一次查询取得事务级快照-- 保持事务打开会话 B 更新全部行然后普通 VACUUMUPDATEorder_stateSETstatuspaid;VACUUM(VERBOSE,ANALYZE)order_state;SELECTn_live_tup,n_dead_tup,last_vacuumFROMpg_stat_user_tablesWHERErelidorder_state::regclass;UPDATE 可以完成因为会话 A 没持有与之冲突的业务行写锁。但 A 的快照仍需看到更新前的created版本VACUUM 不能删除它们。会话 A 验证快照仍旧SELECTstatus,count(*)FROMorder_stateGROUPBYstatus;COMMIT;会话 A 提交后在 B 再执行VACUUM(VERBOSE,ANALYZE)order_state;旧版本此时才有机会回收。对比两次 VACUUM 的 verbose 输出、dead tuple 趋势和 relation 尺寸可以观察 horizon 的影响。实验能证明固定旧快照会推迟旧版本回收不能证明生产中的唯一保留者就是该会话也不能保证普通 VACUUM 后文件缩小——可复用空间与归还文件系统仍是两回事。READ COMMITTED 为什么也可能出问题REPEATABLE READ明确固定事务快照最容易复现。READ COMMITTED通常每条命令取得新快照但事务仍可能因为正在执行的长查询、游标、导出快照等保持资源和可见性边界。因此排障不能只按隔离级别猜测要看pg_stat_activity.backend_xmin、事务开始时间、当前状态和具体工作负载。xact_start很老但backend_xmin为空也不能据此断言它正在挡住 tuple 回收两者是不同证据。找到真正的 horizon 保留者普通 backendSELECTpid,usename,application_name,client_addr,state,xact_start,query_start,backend_xmin,now()-xact_startASxact_age,wait_event_type,wait_event,left(query,200)ASquery_sampleFROMpg_stat_activityWHERExact_startISNOTNULLORDERBYxact_start;这是只读检查但 SQL 文本和客户端地址属于敏感运行信息应限制展示范围。异常信号是长时间idle in transaction、很老的backend_xmin或持续运行的大查询下一步是定位应用所有者和事务用途而不是立即pg_terminate_backend。Prepared transactionSELECTtransaction,gid,prepared,owner,databaseFROMpg_prepared_xactsORDERBYprepared;两阶段提交中已经 PREPARE、尚未 COMMIT/ROLLBACK 的事务可能长期保留资源。处理前必须由事务协调器和业务账务确认最终决议猜测提交或回滚都可能破坏原子性。Replication slotSELECTslot_name,slot_type,active,active_pid,xmin,catalog_xmin,restart_lsn,confirmed_flush_lsn,inactive_since,wal_status,invalidation_reasonFROMpg_replication_slots;xmin是槽要求保留的最老事务catalog_xmin约束系统目录 tuple 清理restart_lsn则约束 WAL 文件保留。它们可能同时出现但不是同一种债务。Standby feedback开启hot_standby_feedback的副本会把其查询需要的 horizon 反馈到上游减少副本查询因清理冲突被取消却可能让主库膨胀。这里的取舍是“取消副本长查询”与“保留主库旧版本”不是免费消除冲突。两条债务链不要混为一个复制延迟旧 snapshot / xmin / catalog_xmin → tuple 仍可能被需要 → VACUUM 不能删除 → heap、索引与 catalog 债务 restart_lsn → 消费者仍可能需要旧 WAL → checkpoint 不能回收相关 WAL → pg_wal 容量债务一个 CDC 任务可能同时造成两条链也可能只造成其中一条。主从 replay lag 为零不能排除闲置逻辑槽pg_wal正常也不能排除catalog_xmin正在拖住系统目录。为什么只查阻塞锁一定会漏SELECT*FROMpg_locksWHERENOTgranted;结果为空只说明当前没有等待授予的锁。MVCC 允许读者和写者在很多场景并发因此旧快照造成的危害恰恰可能没有锁告警。同理VACUUM进度到 100% 只证明扫描完成不证明所有旧版本都达到可删除条件。要把活动会话、VACUUM verbose 日志、dead tuple 趋势和业务事务生命周期串起来。生产处置先确认所有者再改变状态只读证据建立时间线磁盘/查询退化何时开始哪个 backend 或 slot 的 horizon 同期变老。核对应用连接池、报表、CDC 和 prepared transaction 所有者。对目标表比较 UPDATE 速度、dead tuple 斜率与 VACUUM 输出。区分 tuple 保留和 WAL 保留分别量化业务风险。最小止血先阻止新的超长报表事务和非必要批量 UPDATE给 autovacuum 追赶窗口。若必须终止某个会话要先确认查询可重试、事务只读且不存在客户端依赖并从最明确的单个 PID 灰度。终止会话只解除当前 horizon不会自动压缩文件也不会修复连接池继续泄漏事务的根因。根因修复ALTERROLE report_userSETidle_in_transaction_session_timeout5min;ALTERROLE report_userSETstatement_timeout30min;超时值必须根据真实导出和事务 SLA 设计。idle_in_transaction_session_timeout处理事务内空闲statement_timeout处理执行过久的语句二者不能互相替代。应用还应做到连接归还池前显式提交或回滚不把事务跨 HTTP 请求或用户思考时间大报表用可恢复分页、批量导出或专用副本而非无限固定快照prepared transaction 有协调器、超时告警和人工决议流程每个 slot 有负责人、消费 SLA、容量上限与重建手册。删除 replication slot 会丢失连续消费位点CDC 可能必须重新快照ROLLBACK PREPARED可能与协调器决议冲突。这些都需要独立授权、恢复点和业务对账不能写进自动清理脚本。验收与停止条件修复后同时验证最老backend_xmin恢复到合理窗口、dead tuple 净斜率转负、VACUUM 能移除版本、业务报表仍完成、CDC 无缺失重复、WAL 与磁盘水位稳定。若终止会话后业务错误率上升、报表不可恢复或下游重新快照能力不确定应停止扩大处理范围。回滚超时配置可以恢复更长事务窗口却不能复活已终止的会话或已删除的 slot。证据边界证据能证明不能证明没有 waiting lock当前无锁等待没有旧快照阻止清理backend_xmin很老backend 保持较老 horizon终止它一定无业务影响VACUUM 跑完扫描任务结束所有版本已删除、文件已缩小slotactive false当前无流式消费者slot 不保留 tuple 或 WAL结束事务后可清理量上升该事务是重要保留者系统不存在其他保留者面试表达主线MVCC 旧版本只有在不再可能被任何相关快照看到时才能回收。只读长事务虽不阻塞 UPDATE却可通过backend_xmin推迟 VACUUMprepared xact、复制槽和 standby feedback 也能保持 horizon。排障不能只查锁要同时查 activity、prepared xacts、slots 与 VACUUM 证据。实验清理确认会话 A 已提交或回滚后执行DROPTABLEIFEXISTSorder_state;官方资料PostgreSQL 18MVCCPostgreSQL 18pg_stat_activityPostgreSQL 18pg_replication_slotsPostgreSQL 18Client Connection DefaultsPostgreSQL 18Hot Standby FeedbackPostgreSQL 18.6 源码标签 REL_18_6
