PostgreSQL / 系统课时

用 pg_stat_activity 定位异常事务

用 pg_stat_activity 定位异常事务

长事务和 idle in transaction 往往表现为锁等待、膨胀或连接占用。pg_stat_activity 能把会话状态、事务起点、最近查询、等待事件和应用来源放到同一张视图中,帮助你先定位异常会话,再决定是否继续追锁或终止连接。

学习目标

  • 用 xact_start、query_start 与 state_change 区分事务年龄、查询年龄和空闲时长。
  • 结合 state、wait_event_type 与 wait_event 判断会话是在执行、等待锁,还是客户端持有未结束事务。
  • 用 application_name、client_addr 和 pid 把数据库会话映射回具体应用实例。

先筛出真正有事务的异常会话

排查时不要只按“查询运行多久”排序。一个会话可能早已执行完 SQL,却停在 idle in transaction,此时 query_start 只是上一条语句的开始时间,真正需要关注的是更早的 xact_start 和进入当前状态的 state_change。

下面的查询筛出事务已持续至少 5 分钟,或处于 idle in transaction / idle in transaction (aborted) 的会话。使用 clock_timestamp() 可以避免诊断语句本身处于长事务时,now() 固定在事务起点造成年龄判断失真。

sql
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    xact_start,
    query_start,
    state_change,
    clock_timestamp() - xact_start AS xact_age,
    clock_timestamp() - state_change AS state_age,
    backend_xid,
    backend_xmin,
    wait_event_type,
    wait_event,
    left(query, 120) AS query
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
  AND xact_start IS NOT NULL
  AND (
      clock_timestamp() - xact_start >= interval '5 minutes'
      OR state IN ('idle in transaction', 'idle in transaction (aborted)')
  )
ORDER BY xact_start;

预期输出会列出当前实例中符合条件的会话;实际 PID、时间、IP、SQL 和等待事件会不同。

text
 pid    | application_name | state               | xact_age | state_age | wait_event_type | wait_event    | query
--------+------------------+---------------------+----------+-----------+-----------------+---------------+----------------------------
 <PID1> | checkout-api     | idle in transaction | 00:12:08 | 00:11:55  | Client          | ClientRead    | UPDATE orders SET ...
 <PID2> | billing-worker   | active              | 00:06:21 | 00:00:09  | Lock            | transactionid | UPDATE accounts SET ...

跨角色查看完整活动信息通常需要超级用户权限或 pg_read_all_stats;权限不足时,你可能只能看到自己的会话详情,其他会话的部分字段会被隐藏。

正确解释三个时间字段

xact_start 是当前事务开始时间,最适合衡量“这个事务已经活了多久”。长事务即使暂时没有运行 SQL,也可能继续持有锁或旧快照,因此它通常是事务治理的第一排序键。

query_start 表示当前查询开始时间;当 state 不是 active 时,它表示上一条查询的开始时间。对 idle in transaction 来说,不应把它误读成“当前 SQL 仍执行了这么久”。

state_change 表示会话最近一次进入当前状态的时间。对于 idle in transaction,clock_timestamp() - state_change 可以近似理解为“已经空等客户端多久”;但对于正在等待锁的 active 会话,它并不是精确的锁等待时长。

用状态和等待事件判断下一步

当 state = 'active' 且 wait_event_type = 'Lock' 时,会话正在执行 SQL,但被锁阻塞;这时应继续根据 pid 查询阻塞者,而不是把它当成长 SQL 直接终止。

当 state = 'idle in transaction' 且 wait_event_type = 'Client'、wait_event = 'ClientRead' 时,PostgreSQL 通常是在等待客户端发下一条命令,真正的问题是应用打开事务后没有及时提交或回滚。idle in transaction (aborted) 则说明事务已经报错但客户端仍未 ROLLBACK,连接会一直停在失败事务中。

如果 wait_event 为空且状态为 active,说明当前没有被 PostgreSQL 已知等待事件挂起;此时要结合 SQL、执行计划和资源指标判断是否只是计算量大。wait_event 非空也不等于“数据库坏了”,它只是告诉你后端当前在等什么。

从数据库会话追到应用代码

定位异常事务后,先用 application_name、client_addr、usename 和 pid 找到负责它的服务实例,再检查事务边界:是否忘记提交、异常分支漏回滚、连接池把关闭连接误当成结束事务,或业务代码在事务内等待远程 HTTP。

修复应用后,再运行同一筛选 SQL:异常 PID 应消失,新事务的 xact_age 应保持在业务可接受范围内。生产环境还可以配置 idle_in_transaction_session_timeout 作为兜底,但它只能限制后果,不能替代对事务边界和慢调用的修复。

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

目录与阅读设置

阅读设置

17

记录一个想法

用 pg_stat_activity 定位异常事务

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

登录哪都通