PostgreSQL idle in transaction — 空闲会话长期持锁

定位 PostgreSQL 中停留在 idle in transaction 的会话，测量其持锁与快照时长，通过事务超时与应用边界修复阻塞和表膨胀。

现象

平时毫秒级完成的查询突然排队；pg_stat_activity 中出现 state = idle in transaction 且 state_change 很久没更新；autovacuum 清不掉死元组，表持续膨胀；创建请求早已返回，相关锁却一直处于 granted 状态。

诊断

按事务开始时间排出最老的空闲事务：

SELECT pid, usename, application_name, client_addr, state,
       now() - state_change AS idle_for, now() - xact_start AS xact_age,
       left(query, 80) AS last_query
FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%'
ORDER BY xact_start LIMIT 20;

xact_age 大而 idle_for 小，说明事务本身运行很久；idle_for 大则说明客户端停止发语句却把事务留在打开状态。接着联查 pg_locks 看它是否持有 AccessExclusiveLock 或行锁，并用 pg_blocking_pids(pid) 找出等待者的真正阻塞源。最后检查 SHOW idle_in_transaction_session_timeout;，值为 0 表示没有任何兜底。

修复

根因通常是代码开启事务后去调用外部 API、等待用户输入，或 ORM 在归还连接前没有回滚。把网络调用和文件写入移到事务之外，确保每条路径都显式 COMMIT 或 ROLLBACK，连接池在归还脏连接时执行回滚。服务端再加一道保护：

ALTER ROLE app SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE app SET statement_timeout = '30s';

超时只是安全网，不能替代修复。若某个会话是误开的 psql，可用 SELECT pg_terminate_backend(pid); 结束它，但要清楚这会回滚整个事务。

验证

保持一个事务打开超过阈值，确认后端被服务端终止且日志有记录，然后执行此前被阻塞的 DDL 或更新，应能立即拿到锁。观察完整流量周期，pg_stat_activity 中不应再出现超过阈值的 idle in transaction；若仍出现，说明还有代码路径在归还连接前未结束事务。