PostgreSQL / 系统课时

构建阻塞链与根阻塞者

构建阻塞链与根阻塞者

数据库里最末端的等待会话通常只是受害者,真正应该定位的是阻塞链最上游、自己没有再等待别人的根阻塞者。pg_blocking_pids() 可以把“谁在等谁”变成有向边,再结合 pg_stat_activity 还原多级阻塞关系。

学习目标

  • 构造一个 A 阻塞 B、B 再阻塞 C 的多级等待实验
  • 使用 pg_blocking_pids() 找出直接阻塞关系
  • 从阻塞边识别根阻塞者,而不是只处理末端等待者

先构造三级阻塞

准备实验表:

sql
DROP TABLE IF EXISTS lock_demo;

CREATE TABLE lock_demo (
    id integer PRIMARY KEY,
    value integer NOT NULL
);

INSERT INTO lock_demo VALUES (1, 0), (2, 0);

预期输出:

text
DROP TABLE
CREATE TABLE
INSERT 0 2

会话 A 先锁住第 1 行:

sql
BEGIN;
UPDATE lock_demo SET value = value + 1 WHERE id = 1;
SELECT pg_backend_pid() AS pid_a;

预期输出:

text
BEGIN
UPDATE 1
 pid_a
-------
 <PID_A>
(1 row)

保持 A 不提交。会话 B 先锁第 2 行,再尝试更新第 1 行:

sql
BEGIN;
UPDATE lock_demo SET value = value + 1 WHERE id = 2;
SELECT pg_backend_pid() AS pid_b;
UPDATE lock_demo SET value = value + 1 WHERE id = 1;

预期结果:

text
前两条语句成功;最后一条 UPDATE 等待会话 A 释放第 1 行相关锁。

此时会话 C 尝试更新第 2 行:

sql
BEGIN;
SELECT pg_backend_pid() AS pid_c;
UPDATE lock_demo SET value = value + 1 WHERE id = 2;

预期结果:

text
会话 C 的 UPDATE 等待会话 B;此时形成 C -> B -> A 的阻塞链。

PostgreSQL 多级阻塞链与根阻塞者

查询直接阻塞边

在第四个观察会话执行:

sql
WITH edges AS (
    SELECT
        a.pid AS waiting_pid,
        b.blocking_pid
    FROM pg_stat_activity AS a
    CROSS JOIN LATERAL
        unnest(pg_blocking_pids(a.pid)) AS b(blocking_pid)
)
SELECT
    e.waiting_pid,
    e.blocking_pid,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM edges AS x
            WHERE x.waiting_pid = e.blocking_pid
        ) THEN false
        ELSE true
    END AS blocker_is_root
FROM edges AS e
ORDER BY e.waiting_pid;

预期输出:

text
 waiting_pid | blocking_pid | blocker_is_root
-------------+--------------+-----------------
 <PID_B>     | <PID_A>      | t
 <PID_C>     | <PID_B>      | f
(2 rows)

PID 是动态值,以实际环境为准。这张边表已经说明 A 自己没有继续等待其他会话,因此它是当前链路的根阻塞者。

再看根阻塞者在做什么

确认根节点后,再结合 pg_stat_activity 看事务开始时间、状态、等待事件和 SQL:

sql
SELECT
    pid,
    state,
    xact_start,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE pid IN (<PID_A>, <PID_B>, <PID_C>)
ORDER BY xact_start NULLS LAST;

预期输出:

text
 pid     | state               | xact_start | wait_event_type | wait_event    | query
---------+---------------------+------------+-----------------+---------------+----------------
 <PID_A> | idle in transaction | <TIME>     | Client          | ClientRead    | <VALUE>
 <PID_B> | active              | <TIME>     | Lock            | transactionid | <VALUE>
 <PID_C> | active              | <TIME>     | Lock            | transactionid | <VALUE>

具体 state、wait_event 和 SQL 文本可能因版本、执行时点和锁类型不同而变化。

处理根节点前先判断业务安全

发现根阻塞者不代表可以立即终止它。先确认它是否正在执行关键事务、是否只是“idle in transaction”忘记提交、终止后应用是否会自动重试,以及是否会放大业务副作用。

如果只终止 C,B 仍然被 A 阻塞,新的请求还会继续堆积。真正有效的治理是找到根节点及其形成原因:长事务、忘记提交、热点更新、锁顺序不一致或异常外部调用,然后修正源头并重新观察整条阻塞链是否消失。

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

目录与阅读设置

阅读设置

17

记录一个想法

构建阻塞链与根阻塞者

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

登录哪都通