PostgreSQL / 系统课时

识别 DDL 的强锁风险

识别 DDL 的强锁风险

DDL 风险往往不在语句执行多久,而在它要先拿到什么表级锁。一个等待中的强锁请求还可能把后续本可并发的查询挡在队列后面。本课通过三个会话复现这种放大效应,并给出上线前的锁检查与超时策略。

学习目标

  • 判断 ALTER TABLE、DROP TABLE、TRUNCATE 等 DDL 的表级锁风险
  • 复现等待中的 AccessExclusiveLock 如何放大后续查询阻塞
  • 使用 pg_locks、pg_blocking_pids() 与 lock_timeout 控制变更风险

先看锁模式再看执行时间

普通 SELECT 会在目标表上取得 AccessShareLock,写入通常取得 RowExclusiveLock。AccessExclusiveLock 与所有表级锁模式冲突,因此它一旦获得,就连普通 SELECT 也不能并发访问同一张表。

DROP TABLE 和 TRUNCATE 会取得 AccessExclusiveLock。ALTER TABLE 则必须看具体子命令:很多形式使用 AccessExclusiveLock,并且没有特别说明更低锁级别时应按强锁评估。即使某个 ALTER TABLE 只是快速修改元数据,等锁时间仍可能远长于真正执行时间。

操作 典型表级锁风险 评估重点
TRUNCATE AccessExclusiveLock 会阻塞同表普通读写
DROP TABLE AccessExclusiveLock 需要排空同表并发访问
ALTER TABLE ... ADD COLUMN 通常为 AccessExclusiveLock 元数据操作可能很快,但拿锁前可能长时间等待
其他 ALTER TABLE 子命令 依具体子命令而定 必须按目标 PostgreSQL 版本核对具体锁级别

复现等待队列放大

先准备一张实验表。

sql
DROP TABLE IF EXISTS ddl_lab;
CREATE TABLE ddl_lab (
    id bigint PRIMARY KEY,
    payload text NOT NULL
);
INSERT INTO ddl_lab (id, payload)
VALUES (1, 'ready');

预期输出:

text
DROP TABLE
CREATE TABLE
INSERT 0 1

若表首次不存在,DROP TABLE IF EXISTS 可能额外打印 NOTICE。接下来使用三个独立会话。会话 A 开启事务并更新一行,持续持有目标表的 RowExclusiveLock。

sql
SET application_name = 'ddl-holder';
BEGIN;
UPDATE ddl_lab
SET payload = 'held'
WHERE id = 1;

预期输出:

text
SET
BEGIN
UPDATE 1

不要提交 A。会话 B 请求一个需要强锁的 DDL,并设置 15 秒锁等待上限。

sql
SET application_name = 'ddl-ddl';
SET lock_timeout = '15s';
ALTER TABLE ddl_lab ADD COLUMN note text;

A 的 RowExclusiveLock 与 B 请求的 AccessExclusiveLock 冲突,所以 B 会先等待;如果 15 秒内 A 一直不结束,预期最终输出为:

text
SET
SET
ERROR:  canceling statement due to lock timeout

在 B 仍处于等待的 15 秒窗口内,用观察会话检查目标表锁。pg_blocking_pids() 会直接告诉你等待者被哪些后端阻塞,不必自己猜锁冲突矩阵。

sql
SELECT a.application_name,
       l.mode,
       l.granted,
       pg_blocking_pids(a.pid) AS blockers
FROM pg_stat_activity AS a
JOIN pg_locks AS l
  ON l.pid = a.pid
WHERE l.locktype = 'relation'
  AND l.relation = 'ddl_lab'::regclass
  AND a.application_name IN ('ddl-holder', 'ddl-ddl')
ORDER BY a.application_name, l.granted DESC;

预期能看到持锁者已授予、DDL 请求未授予;PID 会因环境而异:

text
 application_name |        mode         | granted | blockers
------------------+---------------------+---------+-----------
 ddl-ddl          | AccessExclusiveLock | f       | {<PID>}
 ddl-holder       | RowExclusiveLock    | t       | {}

现在趁 B 仍在等待,从会话 C 发起一个普通查询,并把锁等待限制为 2 秒。

sql
SET application_name = 'ddl-reader';
SET lock_timeout = '2s';
SELECT count(*) FROM ddl_lab;

预期结果不是立即返回,而是超时:

text
SET
SET
ERROR:  canceling statement due to lock timeout

DDL 强锁等待队列放大效应

为什么后来的 SELECT 也会被挡住

关键不是 C 与 A 是否冲突,而是 B 已经在锁等待队列中排在 C 前面。PostgreSQL 会把“持有冲突锁的会话”视为 hard blocker,也会把“正在等待、且位于当前请求之前并与当前请求冲突的会话”视为 soft blocker。

因此,A 的 RowExclusiveLock 本来允许 C 的 AccessShareLock 并发存在,但如果系统绕过等待中的 B 持续放行新查询,B 的强锁可能长期饥饿。等待队列让 B 成为 C 的软阻塞者,于是一次 DDL 等锁会表现成越来越多新查询一起堆积。

等 B 因 lock_timeout 退出后,在 A 仍保持事务打开的前提下,再从 C 执行普通查询即可验证这一点。

sql
SET lock_timeout = '2s';
SELECT count(*) FROM ddl_lab;

预期输出:

text
SET
 count
-------
     1
(1 row)

这次能立即返回,说明真正放大读阻塞的是排在队列前面的强锁请求,而不是 A 的普通写锁本身。实验结束后在 A 执行回滚释放锁。

sql
ROLLBACK;

预期输出:

text
ROLLBACK

把 DDL 等锁变成可控失败

生产变更不要只估计“DDL 本身几秒能跑完”,而要先检查当前长事务、同表活跃写入和已有等待者。对必须在线执行的强锁 DDL,优先在会话级设置较短 lock_timeout:拿不到锁就退出本次尝试,避免一个等待中的 AccessExclusiveLock 长时间卡住后续业务请求。

变更期间若出现堆积,先用 pg_stat_activity 确认等待会话,再用 pg_blocking_pids(pid) 找直接阻塞者,并结合 pg_locks 核对目标 relation、请求模式与 granted=false。不要仅按“最老 SQL”猜根因,因为真正把大量查询挡住的可能正是一个排在队列中的 DDL。

最后还要控制事务边界:表级锁通常会持有到事务结束,所以不要把 DDL 放进包含大量无关操作、人工确认或长时间业务逻辑的事务。对于 ALTER TABLE,也不要把所有子命令一概认定为同一种锁;上线前应按具体子命令与目标 PostgreSQL 版本核对锁级别,再决定维护窗口、超时和替代方案。

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

目录与阅读设置

记录一个想法

识别 DDL 的强锁风险

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

登录哪都通