PostgreSQL / 系统课时

排查 idle in transaction

排查 idle in transaction

idle in transaction 比普通 idle 更值得优先处理,因为连接虽然没有执行 SQL,却仍然处于一个未结束的事务中。它既长期占用连接,又可能阻碍锁和旧版本回收,严重时会把单个事务拖成连接、锁和存储资源的连锁故障。

学习目标

  • 能快速找出长期处于 idle in transaction 的会话及其最后一次状态变化
  • 能判断它对连接、锁和事务生命周期造成的影响
  • 能按“定位来源→确认根因→安全处置→验证恢复”的顺序完成排查

先确认是不是长期 idle in transaction

PostgreSQL 在 pg_stat_activity.state 中把这类会话标记为 idle in transaction。它表示事务已经打开,但当前没有正在执行的语句,因此不能把它当成普通连接池空闲连接处理。

第一步按持续时间排序,优先找最老的会话,同时保留 xact_start、state_change 和 query 等证据。query 代表当前或最近一次语句信息,排查时不要只看连接数。

sql
SELECT
    pid,
    application_name,
    usename,
    client_addr,
    xact_start,
    state_change,
    now() - xact_start AS transaction_age,
    now() - state_change AS idle_for,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start ASC NULLS LAST;

预期结果

text
 pid   | application_name | usename  | client_addr | xact_start             | state_change           | transaction_age | idle_for | wait_event_type | wait_event | query
-------+------------------+----------+-------------+------------------------+------------------------+-----------------+----------+-----------------+------------+------------------------
 27184 | order-api        | appuser  | 10.0.3.21   | 2026-09-06 02:01:14+08 | 2026-09-06 02:01:15+08 | 02:07:31        | 02:07:30 |                 |            | UPDATE orders ...

真正危险的是事务年龄,而不是单纯的 state_change 时长。因为 idle in transaction 可能在事务里执行过修改语句;即使现在没有工作,事务仍可能保持其事务上下文和相关资源。

看它有没有形成锁阻塞

长期事务并不一定持有锁,但在事务中执行过写操作后,可能一直保留锁。排查时可以把会话状态与锁信息一起看,重点寻找“老事务 + 被阻塞会话”的组合。

sql
SELECT
    a.pid AS blocker_pid,
    a.application_name AS blocker_app,
    now() - a.xact_start AS transaction_age,
    a.state AS blocker_state,
    l.locktype,
    l.mode,
    l.relation::regclass AS relation_name
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
WHERE a.state = 'idle in transaction'
ORDER BY transaction_age DESC NULLS LAST;

预期结果

text
 blocker_pid | blocker_app | transaction_age | blocker_state       | locktype | mode             | relation_name
-------------+-------------+-----------------+---------------------+----------+------------------+--------------
 27184       | order-api   | 02:07:31        | idle in transaction | relation | RowExclusiveLock | orders

这里的结果只能证明该事务当前关联了锁资源,不能单独证明某个锁一定正在阻塞其他会话。要判断实际阻塞关系,还需要继续结合等待方的 pg_locks 和会话状态核对。

沿着应用代码找出为什么没有提交

定位到 PID 后,下一步不是马上终止,而是回答“为什么事务开了以后没有结束”。常见根因包括:业务逻辑在事务内等待外部 HTTP 调用、代码异常后漏掉提交或回滚、读取结果后把事务交给用户交互、连接归还池之前事务边界没有关闭。

一个典型危险路径是:应用从连接池借出连接,执行 BEGIN,完成一次数据库修改,然后在同一个事务中调用远程服务。远程服务慢时,数据库连接一直被占用;如果修改已经产生锁,锁的生命周期也随事务延长。

因此治理时必须同时检查三条链:连接是否归还池、事务是否结束、事务内部是否包含不应存在的长等待。只修改连接池大小而不修正事务边界,通常只是把故障向后推。

设置数据库侧的保护阈值

可以使用 idle_in_transaction_session_timeout 为长期闲置事务设置上限。它针对的是“事务打开但会话处于 idle in transaction”的状态,适合作为数据库侧最后一道保护,而不是替代应用正确关闭事务。

sql
SET idle_in_transaction_session_timeout = '5min';
SHOW idle_in_transaction_session_timeout;

预期结果

text
 idle_in_transaction_session_timeout
--------------------------------------
 5min

生产环境通常应根据业务事务模型设置阈值。阈值过大,保护作用弱;过小,则可能误杀本来就需要较长人工或批处理停顿时间的事务。正式修改前应结合应用行为和历史事务时长做验证。

安全处置与恢复验证

确认某个事务已经异常、且继续保留会造成明显风险后,可以针对明确 PID 进行终止。不要把按 PID 的精确处置换成全库批量清理,因为正常业务也可能同时存在短暂的 idle in transaction。

sql
SELECT pg_terminate_backend(<PID>);

预期结果

text
 pg_terminate_backend
----------------------
 t

实际返回 t 表示请求终止该后端进程;目标进程随后应从 pg_stat_activity 中消失。应用连接池通常会感知连接断开并创建新的可用连接,但是否自动重试、事务是否安全重放,取决于客户端和业务代码。

处置后至少验证三个结果:异常事务数量下降、原先被其影响的阻塞关系解除、应用连接池能够恢复正常。如果只是 PID 消失,但新的 idle in transaction 很快再次出现,说明数据库侧清理成功了,应用侧根因却还没有解决。

idle in transaction 的排查核心,是把一个看似“空闲”的会话还原成完整事务生命周期:事务什么时候开始、最后执行了什么、为什么没有结束、是否持有影响其他会话的资源,以及终止后是否会再次出现。只有沿这条链验证,才能把一次连接告警转化为可复现、可修复的应用问题。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

排查 idle in transaction

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通