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 的阻塞链。
查询直接阻塞边
在第四个观察会话执行:
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 阻塞,新的请求还会继续堆积。真正有效的治理是找到根节点及其形成原因:长事务、忘记提交、热点更新、锁顺序不一致或异常外部调用,然后修正源头并重新观察整条阻塞链是否消失。
按自己的节奏,确认真正理解。