PostgreSQL 事务与并发系列 · 第五期

实战调优：事务的常见陷阱与最佳实践

前四期我们深入了 MVCC、锁、隔离级别与序列化异常。本期将把这些知识落地到生产环境的日常运维与开发中，剖析长事务、空闲事务、表膨胀、死锁超时等真实痛点，并提供一套可立即上手的监控、报警与优化方案。

一、长事务：性能与运维的头号杀手

1.1 为什么长事务如此危险？

• 阻止 VACUUM 清理：长事务持有的快照会让所有在它之后产生的死亡元组都无法被清理，导致表与索引持续膨胀。
• 事务 ID 回卷风险：事务 ID 是 32 位环状空间，长事务可能导致 xid 无法冻结，最终数据库会强制关闭。
• 锁持有时间过长：长事务可能持有表锁或行锁，阻塞其他正常业务。

1.2 如何定位长事务？

SELECT pid,
       now() - xact_start AS xact_duration,
       state,
       query,
       backend_xid,
       backend_xmin
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND state NOT LIKE 'idle%'
ORDER BY xact_duration DESC;

重点关注：

• xact_duration 超过 1 分钟的事务（视业务而定）
• backend_xmin 非空且 age 很大的会话

1.3 治理策略

• 应用层超时 + 重试：设置合理的语句超时（statement_timeout）和事务超时（idle_in_transaction_session_timeout）。
• 拆分长事务：将长时间运行的事务拆分为多个短事务，或使用异步处理。
• 定期监控与告警：当长事务超过阈值（如 5 分钟）时，通过脚本发送告警甚至自动终止。

自动终止示例（谨慎使用，建议先通知后终止）：

-- 终止 xact_duration > 10 分钟的事务
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE xact_start < now() - interval '10 minutes'
  AND state != 'idle';

二、idle in transaction：被遗忘的会话

2.1 危害

会话处于 idle in transaction 状态，表示它已经 BEGIN 但既不提交也不回滚，也不执行任何语句。这种会话：

• 持有可能的行锁或表锁，阻塞其他事务。
• 持有快照，同样阻止 VACUUM 清理。
• 占用连接资源，导致连接池枯竭。

2.2 发现与清理

SELECT pid, state, xact_start, now() - xact_start AS idle_duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY idle_duration DESC;

根治方案：在 PostgreSQL 配置文件中设置：

idle_in_transaction_session_timeout = '60s'

一旦事务空闲超过 60 秒，自动被终止并回滚。生产环境建议设为 30~120 秒。

同时检查应用代码：确保每一次 BEGIN 后都有对应的 COMMIT 或 ROLLBACK（尤其是异常处理路径）。

三、表膨胀：监控与回收

3.1 为什么会膨胀？

MVCC 的副作用：更新/删除会产生死元组（dead tuples）。如果 VACUUM 跟不上产生速度，表就会膨胀，占用更多磁盘，降低索引效率。

长事务和 idle in transaction 是 VACUUM 最大的敌人——它们强制保留旧版本。

3.2 如何检测膨胀？

使用 pg_stat_user_tables 观察死元组比例：

SELECT schemaname, relname,
       n_live_tup,
       n_dead_tup,
       round(100 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio,
       last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_ratio DESC;

如果 dead_ratio 经常超过 10%，需要调整 autovacuum 参数或手动 VACUUM。

3.3 治理膨胀

• 调优 autovacuum：

  autovacuum_vacuum_scale_factor = 0.05   # 默认 0.2，降低触发阈值
  autovacuum_vacuum_threshold = 1000
  autovacuum_naptime = 30s

• 对大表针对性设置 storage parameters：

  ALTER TABLE large_table SET (autovacuum_vacuum_scale_factor = 0.05);

• 手动 VACUUM：VACUUM VERBOSE large_table; （不锁表）
• 极端膨胀时使用 VACUUM FULL：会锁表并重写整个表，生产环境需谨慎（可使用 pg_repack 在线重建）。

四、超时设置的合理配置

PostgreSQL 提供了多个超时参数，合理设置可以防止事务失控：

设置示例：

-- 针对当前会话
SET statement_timeout = '30s';
SET lock_timeout = '5s';

-- 针对特定用户
ALTER ROLE app_user SET statement_timeout = '60s';

应用层也应配合超时与重试。

五、锁监控与实时告警

5.1 找出当前阻塞链

使用递归查询找出阻塞关系：

WITH RECURSIVE blocking AS (
    SELECT pid, array[pid] AS path, 1 AS level
    FROM pg_stat_activity
    WHERE pid NOT IN (SELECT unnest(pg_blocking_pids(pid)) FROM pg_stat_activity WHERE pg_blocking_pids(pid) <> ARRAY[]::int[])
    UNION ALL
    SELECT blocked.pid, blocking.path || blocked.pid, blocking.level + 1
    FROM pg_stat_activity blocked
    JOIN blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
    WHERE NOT blocked.pid = ANY(blocking.path)
)
SELECT pid, path, level
FROM blocking
ORDER BY level DESC, pid;

5.2 持续监控与告警脚本（示例）

使用 Shell + psql 定期检查，并通过 Webhook 发送告警：

#!/bin/bash
THRESHOLD_SEC=300   # 长事务阈值 5 分钟
ALERT_WEBHOOK="https://hooks.slack.com/..."

psql -At -c "
SELECT json_agg(row_to_json(t))
FROM (
  SELECT pid, age(now(), xact_start) AS duration, query
  FROM pg_stat_activity
  WHERE xact_start IS NOT NULL
    AND age(now(), xact_start) > interval '$THRESHOLD_SEC seconds'
    AND state NOT LIKE 'idle%'
) t
" | jq . > /tmp/long_xacts.json

if [ $(jq length /tmp/long_xacts.json) -gt 0 ]; then
  curl -X POST -H 'Content-type: application/json' 
    --data "{"text": "Long transactions detected: $(cat /tmp/long_xacts.json)"}" 
    $ALERT_WEBHOOK
fi

（可将此脚本放入 crontab 每分钟执行）

六、连接池与事务的配合

连接池（如 pgbouncer、HikariCP）在提高性能的同时也会带来事务管理的陷阱。

6.1 常见问题

• 事务悬挂：应用获取连接后忘了 COMMIT，归还连接时事务依然打开。连接池再次借出该连接时，新请求会继续在旧事务中运行，导致不可预测的结果。
• 连接池模式选择：pgbouncer 有三种模式 – session、transaction、statement。如果使用 transaction 模式，每个事务结束会自动回滚到空闲状态，可以避免事务悬挂，但不支持某些特性（如 PREPARE 跨事务）。

6.2 最佳实践

• 检查事务状态：应用在从连接池获取连接后，建议执行 DISCARD ALL 或至少检查 SHOW transaction_isolation; 等，确保处于干净状态。
• 使用事务级别的连接池：如果业务事务短小，transaction 模式是最安全的。
• 设置连接池的超时：例如 pgbouncer 的 server_idle_timeout 及时回收异常空闲连接。

七、事务优化的检查清单

在日常开发与运维中，对照以下清单避免常见陷阱：

• [ ] 是否显式控制了事务边界？BEGIN 与 COMMIT/ROLLBACK 成对出现。
• [ ] 事务中是否有远程 API 调用或用户交互（极易导致长事务）？
• [ ] 是否设置了 idle_in_transaction_session_timeout？
• [ ] 是否监控了长事务与 idle in transaction？
• [ ] 是否配置了合理的 statement_timeout 与 lock_timeout？
• [ ] 是否定期检查膨胀并调优 autovacuum？
• [ ] 是否在应用层处理了序列化失败（40001）的重试逻辑？
• [ ] 连接池配置是否符合事务模式（推荐 transaction 或显式重置会话）？
• [ ] 是否在需要强一致性的地方使用了 SELECT FOR UPDATE 或 SERIALIZABLE？

八、总结与系列收官

本系列五期内容从 ACID 与 MVCC 原理出发，逐步深入到锁机制、隔离级别、序列化异常，最后以生产实战调优收尾。通过这个系列，你应该已经能够：

• 解释 PostgreSQL 的 MVCC 如何实现高并发读写
• 诊断并解决常见的锁等待、死锁、写偏斜问题
• 为不同业务场景选择合适的隔离级别
• 监控并治理长事务、膨胀、空闲事务等运维顽疾

事务与并发是数据库领域的核心深水区，但有了一套系统的理解和工具，你就能自信地应对生产环境的各种挑战。

感谢阅读整个系列！ 希望这些内容能帮助你在实际工作中少踩坑，多产出。如果对任何一期有疑问或建议，欢迎留言讨论。

我们下个技术专题再见！