适用场景
应用运行一段时间后，新请求开始等待数据库连接，接口 P99 持续升高，最终出现连接池获取超时或 PostgreSQL too many connections。数据库 CPU、磁盘 I/O 和慢 SQL 指标却不高，重启应用后又能短暂恢复。
这类现象经常不是“连接池太小”，而是业务代码开启事务后没有及时提交或回滚。连接停在 idle in transaction 时，看似没有执行 SQL，却仍占用连接和事务快照，还可能持有行锁或表锁，阻塞 DDL、放大表膨胀，并最终耗尽连接池。
本文以 PostgreSQL 13 及以上版本为主，给出现场取证、止损、代码修复和超时兜底的完整实践。
现象描述
典型告警通常成组出现：
- 应用报连接池获取超时，但 PostgreSQL 活跃查询数不多；
pg_stat_activity中有大量idle in transaction会话；- 同一批会话的
xact_start很早，state_change之后长期没有新命令； ALTER TABLE、CREATE INDEX或业务更新语句持续等待锁；- autovacuum 正常运行，但部分表的
n_dead_tup继续增长； - 重启应用或连接池后暂时恢复，过一段时间再次复发。
需要先区分两种状态：普通 idle 表示会话正在等待客户端命令且不在事务中，通常只是连接池保留的空闲连接；idle in transaction 表示会话仍处于打开的事务中，只是当前没有执行语句，风险明显更高。
可能原因
常见根因都与事务边界不完整有关：
- 业务分支提前
return，跳过了commit或rollback； - 捕获异常后只记录日志，没有回滚事务；
- 查询完成后执行外部 HTTP、文件处理或消息发送，事务一直保持打开；
- ORM 会话按请求创建，却没有在请求结束时可靠关闭；
- 手工关闭了自动提交，但遗漏某条只读路径的事务结束；
- 客户端中断或网络异常后，应用没有及时释放连接；
- 把连接池容量调大掩盖泄漏，使问题更晚、更猛烈地暴露。
注意，pg_stat_activity.query 对非 active 会话显示的是最近执行的语句，不代表该语句仍在运行。定位时必须结合 state、xact_start、state_change、锁信息和应用日志判断。
排查思路
1. 统计连接状态和占比
先按数据库、用户和状态聚合，确认连接究竟消耗在哪里：
SELECT
datname,
usename,
state,
count(*) AS connection_count
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY datname, usename, state
ORDER BY connection_count DESC;
connection_count 应与应用实例数、每实例连接池上限对照。若大量连接处于普通 idle，需要核对池容量；若大量连接处于 idle in transaction，应继续追踪事务年龄和来源，而不是立刻增大 max_connections。
2. 找出长期未结束的事务
下面的查询按事务持续时间排序，并排除当前排查连接：
SELECT
pid,
datname,
usename,
application_name,
client_addr,
now() - xact_start AS transaction_age,
now() - state_change AS idle_age,
wait_event_type,
wait_event,
backend_xid,
backend_xmin,
left(query, 300) AS last_query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
AND xact_start IS NOT NULL
AND pid <> pg_backend_pid()
ORDER BY xact_start;
重点关注：
transaction_age：整个事务已经持续多久，是风险排序的主要依据；idle_age：最近一次状态变化后空闲多久，可帮助识别客户端“忘记继续”；application_name、client_addr、usename：用于映射应用、实例和连接账号；backend_xid、backend_xmin：非空且长期不变时，要警惕旧事务影响垃圾元组回收；last_query：只作为定位代码路径的线索，不能把它直接认定为当前慢 SQL。
普通账号通常只能完整查看自己的会话。集中排障账号可按最小权限原则授予 pg_read_all_stats，不要让业务账号长期持有超级用户权限。
3. 确认是否正在阻塞其他会话
idle in transaction 不一定持有冲突锁，因此不能见到该状态就批量终止。先用 pg_blocking_pids() 建立被阻塞会话和阻塞者的对应关系：
SELECT
blocked.pid AS blocked_pid,
blocked.usename AS blocked_user,
now() - blocked.query_start AS blocked_for,
blocker.pid AS blocker_pid,
blocker.state AS blocker_state,
now() - blocker.xact_start AS blocker_transaction_age,
left(blocked.query, 200) AS blocked_query,
left(blocker.query, 200) AS blocker_last_query
FROM pg_stat_activity AS blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS blocking_pid
JOIN pg_stat_activity AS blocker ON blocker.pid = blocking_pid
ORDER BY blocked.query_start;
如果 blocker_state 是 idle in transaction，且阻塞时间与应用告警吻合，就形成了较强证据。继续通过 application_name、连接地址、数据库账号、SQL 注释或请求 trace_id 回到具体代码路径。
4. 判断是否影响垃圾回收
长事务持有旧快照时，VACUUM 可能无法回收对该事务仍可见的旧版本。可以先观察表级趋势：
SELECT
schemaname,
relname,
n_live_tup,
n_dead_tup,
last_autovacuum,
autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
单次 n_dead_tup 较高不能直接证明长事务就是根因。应结合最老事务的 backend_xmin、表更新速率、autovacuum 日志和多次采样趋势判断，避免看到膨胀就盲目执行 VACUUM FULL。
现场止损
1. 先限制流量，再处理连接
如果连接池已经耗尽，先对问题接口降载、暂停任务消费者或摘除异常实例，阻止新事务继续堆积。保留 pg_stat_activity、锁链、应用日志和连接池指标后，再处理明确的问题会话。
2. 对空闲事务使用终止会话，而不是取消查询
pg_cancel_backend() 只取消正在执行的查询；空闲事务当前没有查询可取消。确认业务影响后，使用 pg_terminate_backend() 断开指定会话，PostgreSQL 会回滚其未提交事务：
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
AND now() - xact_start > interval '10 minutes'
AND usename = 'app_user'
AND datname = 'app_db'
AND pid <> pg_backend_pid();
生产执行前应先把相同条件改为 SELECT 审核目标 PID、事务年龄和来源，逐个或小批处理。终止会话会回滚未提交数据，并可能触发客户端重连与重试；必须确认操作幂等性，不能仅凭状态进行无边界批量终止。
修复方案
方案一：让事务生命周期由上下文管理器负责
以 psycopg 3 为例，把数据库操作限制在清晰的上下文中。成功时提交，异常时回滚，离开外层上下文后关闭连接：
from collections.abc import Callable
import psycopg
from psycopg import Connection
def update_order_status(
connection_factory: Callable[[], Connection[tuple]],
order_id: int,
status: str,
) -> None:
if order_id <= 0:
raise ValueError("订单 ID 必须为正整数")
if status not in {"paid", "cancelled"}:
raise ValueError("订单状态不在允许范围内")
with connection_factory() as connection:
with connection.transaction():
connection.execute(
"UPDATE orders SET status = %s WHERE id = %s",
(status, order_id),
)
参数通过驱动绑定，不能拼接 SQL。外部 HTTP 调用、文件上传和消息发送应移到事务之外；若必须保证数据库与消息的一致性，可采用 outbox 等明确的一致性方案，而不是让事务跨越不受控的网络等待。
对于 Web 框架或 ORM，应把会话的创建、提交、回滚和关闭统一放在请求依赖、中间件或工作单元边界，并为提前返回、校验失败、数据库异常和客户端取消编写测试。
方案二：为应用角色设置空闲事务超时
代码修复是根本措施，数据库超时是防止单个缺陷无限占用资源的安全网。优先按业务角色设置，避免影响复制、迁移或管理连接：
ALTER ROLE app_user IN DATABASE app_db
SET idle_in_transaction_session_timeout = '60s';
新建会话会读取该设置。上线前先观察正常事务持续时间分布，在测试环境验证客户端遇到断连后的行为，再选择明显高于正常值的阈值。连接池必须能够识别失效连接并重新建立连接；不要把 idle_session_timeout 当作等价替代，因为普通空闲连接通常不持有事务资源，而且连接池中间件未必能正确处理意外断连。
可在应用连接建立后核对实际值：
SHOW idle_in_transaction_session_timeout;
方案三：限制连接池并设置获取超时
连接池需要同时具备以下边界：
- 每实例最小和最大连接数，且所有实例总和为管理、迁移和监控连接预留余量；
- 获取连接超时，避免请求无限排队；
- 连接健康检查，能丢弃已被服务端超时终止的连接；
- 连接持有时间与等待队列指标，用于区分数据库慢和业务未归还连接；
- 请求结束后的泄漏检测或会话状态复位。
增大池容量只能改变故障出现时间，不能修复事务泄漏。容量计算应以数据库可用连接预算为上限，并结合实例数、峰值并发和事务耗时压测。
验证示例
修复完成后，应覆盖成功、异常和提前返回三条路径。下面的集成测试思路用于验证异常不会留下空闲事务：
import pytest
def test_failed_operation_rolls_back_and_releases_connection(db_pool) -> None:
with pytest.raises(RuntimeError, match="模拟业务失败"):
with db_pool.connection() as connection:
with connection.transaction():
connection.execute("SELECT 1")
raise RuntimeError("模拟业务失败")
with db_pool.connection() as connection:
state = connection.execute(
"SELECT state FROM pg_stat_activity WHERE pid = pg_backend_pid()"
).fetchone()
assert state is not None
assert state[0] != "idle in transaction"
实际项目还应在独立测试库中验证：事务内抛出数据库异常、业务校验提前返回、请求取消、连接断开，以及超时终止后连接池能否自动淘汰坏连接。时间相关断言要留出 CI 抖动余量，测试结束后清理数据和连接。
监控与预防措施
- 监控
idle in transaction会话数、最老事务年龄和连接池使用率，而不只看总连接数； - 为
application_name设置稳定的服务与实例标识，便于从数据库反查来源； - 告警中同时展示数据库、账号、客户端地址和事务年龄，不记录完整敏感 SQL 参数；
- 对事务内的外部网络调用进行代码扫描和评审；
- 对批处理任务设置分批提交，避免一个事务覆盖整批长耗时工作；
- 在发布前压测连接池等待、事务 P95/P99 和超时后的恢复能力；
- 定期演练终止问题会话，确认业务重试具备幂等边界。
参考资料：
总结
连接池耗尽不等于数据库算力不足。大量 idle in transaction 说明连接虽然没有执行 SQL，却仍被未结束事务占用，并可能持有锁和旧快照。正确路径是先用 pg_stat_activity 找到长事务，再用 pg_blocking_pids() 确认影响范围，谨慎终止明确的问题会话完成止损，最后通过可靠的事务上下文、角色级超时、连接池边界和回归测试消除复发条件。扩大连接池只能延后告警，清晰且可验证的事务生命周期才是根治方案。
Discussion
评论