PostgreSQL / 系统课时

排查锁等待与慢事务

排查锁等待与慢事务

锁等待往往不是“数据库突然变慢”,而是某个事务长期占有锁,后续会话只能排队。本课用系统视图定位等待者、阻塞者和长事务,再按风险从取消语句到终止会话逐级处理,并用超时策略降低再次发生的概率。

学习目标

  • 用 pg_stat_activity 与 pg_blocking_pids() 找到等待会话和直接阻塞源
  • 识别长事务与 idle in transaction 会话,判断它们是否仍持有锁
  • 选择取消、提交回滚或终止会话,并验证阻塞已经解除

先确认真正的阻塞链

看到接口超时后,不要先重启数据库。pg_stat_activity 能看到会话状态,pg_blocking_pids(pid) 会返回直接阻塞指定会话的后端 PID。下面的查询只列出当前确实被其他会话阻塞的连接。

sql
SELECT
    w.pid AS waiting_pid,
    w.usename AS waiting_user,
    now() - w.query_start AS waiting_for,
    b.pid AS blocking_pid,
    b.usename AS blocking_user,
    now() - b.xact_start AS blocking_xact_age,
    left(w.query, 80) AS waiting_query,
    left(b.query, 80) AS blocking_query
FROM pg_stat_activity AS w
JOIN LATERAL unnest(pg_blocking_pids(w.pid)) AS p(blocking_pid) ON true
JOIN pg_stat_activity AS b ON b.pid = p.blocking_pid
ORDER BY waiting_for DESC;

预期输出

text
 waiting_pid | waiting_user | waiting_for | blocking_pid | blocking_user | blocking_xact_age | waiting_query                  | blocking_query
-------------+--------------+-------------+--------------+---------------+-------------------+--------------------------------+-----------------------------
 <PID>       | app_user     | 00:00:12    | <PID>        | app_user      | 00:03:41          | UPDATE account SET ...         | UPDATE account SET ...

<PID>、持续时间和 SQL 文本会随现场变化。重点不是等待者本身,而是 blocking_pid 对应的事务为什么一直没有结束。

锁等待定位与处理链路

再检查长事务与未提交会话

阻塞会话不一定正在执行 SQL。客户端执行过 BEGIN 后如果长时间停在 idle in transaction,事务仍未结束,可能继续持有锁和旧快照。先按事务开始时间排序,优先检查最老的会话。

sql
SELECT
    pid,
    usename,
    application_name,
    state,
    now() - xact_start AS xact_age,
    now() - state_change AS state_age,
    wait_event_type,
    wait_event,
    left(query, 100) AS last_query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

预期输出

text
 pid   | usename  | application_name | state               | xact_age | state_age | wait_event_type | wait_event | last_query
-------+----------+------------------+---------------------+----------+-----------+-----------------+------------+----------------------
 <PID> | app_user | api              | idle in transaction | 00:08:17 | 00:08:02  | Client          | ClientRead | UPDATE account SET ...

如果 xact_age 很长且 state 为 idle in transaction,应先到应用侧确认事务边界:正常业务应尽快 COMMIT 或 ROLLBACK,而不是让连接带着未提交事务回到连接池。

按破坏性从低到高处理

首先确认阻塞 PID 属于哪个应用、正在处理什么业务以及事务是否允许中断。能从应用侧正常提交或回滚时,优先让应用自己结束事务。若只是某条正在执行的语句失控,可先取消当前语句;只有确认会话已经异常遗留时,再终止整个后端连接。

sql
-- 将 <PID> 替换为已核实的阻塞会话 PID。
SELECT pg_cancel_backend(<PID>);

-- 若取消无效且确认可以回滚整个会话,再执行:
SELECT pg_terminate_backend(<PID>);

预期输出

text
 pg_cancel_backend
-------------------
 t

 pg_terminate_backend
----------------------
 t

返回 t 表示信号已成功发送,不等于业务一定无损。pg_terminate_backend 会断开目标连接,未提交事务会回滚;执行者还必须具备对目标后端发送信号的权限。

验证并减少复发

处理后重新运行阻塞链查询。若返回零行,说明当前已没有被其他后端直接阻塞的会话;同时观察业务延迟是否恢复,避免只消掉一个表象。

sql
SELECT count(*) AS blocked_sessions
FROM pg_stat_activity AS w
WHERE cardinality(pg_blocking_pids(w.pid)) > 0;

预期输出

text
 blocked_sessions
------------------
                0

长期治理应放在事务边界和超时策略上:应用在异常路径也要回滚事务;连接池归还连接前不能留下未提交事务;对交互式或在线业务可评估 idle_in_transaction_session_timeout、lock_timeout 和 statement_timeout,但应先按角色或业务场景设置合理阈值,避免全局“一刀切”误杀合法长任务。

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

目录与阅读设置

阅读设置

17

记录一个想法

排查锁等待与慢事务

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

登录哪都通