KernelRanch
返回文章列表

PostgreSQL 加一列为什么会卡住 SELECT：用三个会话看懂 DDL 锁队列

2026-11-12 #PostgreSQL#migrations#locks#troubleshooting

发布时执行 ALTER TABLE ... ADD COLUMN，几秒后连普通 SELECT 都开始超时。先查 pg_blocking_pids()。一个打开事务后迟迟未提交的读请求，可以让加列语句在队列里等待。排在 DDL 后面的新读请求也可能被它挡住。一张只有一行的表就足以复现这条阻塞链。

1. 开四个 psql 窗口，前三个制造阻塞

以下命令按 PostgreSQL 18 的行为编写。在可丢弃的数据库里打开四个 psql 会话，连接同一个库。第四个窗口用来观察锁。

先在 A 创建表，再读一行，保持事务打开：

SET application_name = 'ddl_lab_a';
CREATE TABLE ddl_lab_orders (id bigint PRIMARY KEY);
INSERT INTO ddl_lab_orders VALUES (1);

BEGIN;
SELECT id FROM ddl_lab_orders WHERE id = 1;
-- 先停在这里，等观察结束再 ROLLBACK。

B 模拟迁移。整段粘贴执行，最后一条语句开始等待后切到 C：

SET application_name = 'ddl_lab_b';
BEGIN;
SET LOCAL lock_timeout = '60s';
SET LOCAL statement_timeout = '65s';
ALTER TABLE ddl_lab_orders ADD COLUMN source text;

C 模拟新进来的读请求。看到等待后，立即用第四个窗口查询。如果 B 已经超时，先在 B 执行 ROLLBACK，再从 B 的 BEGIN 开始重试：

SET application_name = 'ddl_lab_c';
SET statement_timeout = '70s';
SELECT id FROM ddl_lab_orders WHERE id = 1;

2. 查清楚谁在等谁

在第四个窗口执行下面的查询。演练时按 application_name 筛选。排查生产故障时，可改用受影响的 PID，或筛选同库的锁等待者：

SELECT a.pid,
       a.application_name AS waiter,
       a.wait_event_type,
       a.wait_event,
       b.pid AS blocker_pid,
       b.application_name AS blocker,
       b.state AS blocker_state,
       now() - b.xact_start AS blocker_xact_age,
       left(b.query, 120) AS blocker_last_query
FROM pg_stat_activity AS a
CROSS JOIN LATERAL unnest(pg_blocking_pids(a.pid)) AS p(blocker_pid)
LEFT JOIN pg_stat_activity AS b ON b.pid = p.blocker_pid
WHERE a.application_name IN ('ddl_lab_b', 'ddl_lab_c')
ORDER BY a.application_name, p.blocker_pid;

按文档推导，60 秒窗口内应看到下面两条边。查询其它账号的完整会话详情需要相应权限，例如 pg_read_all_stats。

等待者直接阻塞者原因
B 的 ALTER TABLEAA 持有 AccessShareLock，B 等待 AccessExclusiveLock
C 的 SELECTBC 请求 AccessShareLock，与排在前面的 B 所请求的锁冲突

A 的状态通常是 idle in transaction。它的 SELECT 已经返回结果，但表锁通常会保留到事务结束。blocker_last_query 显示的是当前或最后执行的语句，判断长事务要看 xact_start，并结合应用请求日志确认是谁一直没提交。

如果还想确认具体锁模式，在第四个窗口执行：

SELECT a.application_name, l.mode, l.granted
FROM pg_locks AS l
JOIN pg_stat_activity AS a USING (pid)
WHERE l.locktype = 'relation'
  AND l.relation = 'ddl_lab_orders'::regclass
  AND a.application_name IN ('ddl_lab_a', 'ddl_lab_b', 'ddl_lab_c')
ORDER BY a.application_name;

这组实验里，B 的 AccessExclusiveLock 应显示 granted = false。只查已授予的锁会漏掉它对 C 的影响。也可以在 B 等待时用 Ctrl+C 取消 DDL，再执行 ROLLBACK。此时 A 仍保持事务打开，C 可以继续读。若等到 B 自己超时，B 会报 canceling statement due to lock timeout，也应在 B 执行 ROLLBACK。结束后在 A 执行 ROLLBACK，再于第四个窗口执行 DROP TABLE ddl_lab_orders; 清理演练表。

3. 把锁等待预算写进迁移

下面用 500 毫秒作为示例预算。把这段放在迁移工具实际执行 DDL 的事务内。如果工具已经打开事务，使用事务内部的三条语句，由工具负责提交和异常回滚。

BEGIN;
SET LOCAL lock_timeout = '500ms';
SET LOCAL statement_timeout = '5s';
ALTER TABLE orders ADD COLUMN source text;
COMMIT;
看到什么下一步
SQLSTATE 55P03，消息为锁超时回滚当前迁移事务，确认阻塞来源，再决定是否重新执行整笔事务
SQLSTATE 57014，消息为语句超时核对 DDL 做了哪些工作，有无扫描、重写或多次锁等待，再评估语句预算
DDL 成功，读请求仍在等查迁移事务是否已经提交，尤其留意同一事务里后续的大批量 UPDATE

lock_timeout 对每次获取锁的等待分别计时。statement_timeout 约束整条语句的执行时间，包含等待。两者都按语句生效，所以一串迁移语句仍需独立的发布时限。发生超时后要回滚，再从干净事务重试。给自动重试设上限和间隔，每次 DDL 排队都可能短暂挡住新读请求。

4. 评审迁移时先分开两种成本

  • 获取锁的成本。 ADD COLUMN 默认需要 ACCESS EXCLUSIVE。本例新增可空、无默认值的列，无须重写旧行，仍要先拿到锁。
  • 持有锁的成本。 PostgreSQL 18 可以避免为非易变默认值重写整表。带 clock_timestamp() 这类易变默认值的加列会重写表和索引，锁会一直持有到事务结束。
  • 需要按行回填时。 先提交加列迁移，让应用兼容旧行的 NULL，再通过独立事务分批回填。若之后要设 NOT NULL，把约束验证作为单独的发布步骤，提前核对相应锁级别和扫描成本。

现场排查可结合 pg_stat_activity 一页纸，确认长事务来自哪个应用实例。

小结

加列前先给获取锁设预算，拿到锁后尽早提交。读请求在发布期间一起变慢时，用 pg_blocking_pids() 沿等待边往前找，分别识别排队中的迁移和最早占着锁的事务。

数据来源与检索时间

检索时间 2026-11-12。

来源URL对应内容
PostgreSQL 18 显式锁文档https://www.postgresql.org/docs/18/explicit-locking.html表锁冲突矩阵、事务结束时释放锁
PostgreSQL 18 系统信息函数https://www.postgresql.org/docs/18/functions-info.htmlpg_blocking_pids() 如何识别持锁者和排在前面的等待者
PostgreSQL 18 客户端参数https://www.postgresql.org/docs/18/runtime-config-client.html两种超时的范围与计时方式
PostgreSQL 18 ALTER TABLEhttps://www.postgresql.org/docs/18/sql-altertable.html加列锁级别、默认值与表重写
PostgreSQL 18 错误码https://www.postgresql.org/docs/18/errcodes-appendix.html55P03 与 57014
PostgreSQL 18 统计视图https://www.postgresql.org/docs/18/monitoring-stats.htmlpg_stat_activity 字段和权限

核验说明

  • 锁模式、队列关系和超时语义已与 PostgreSQL 18 官方文档核对。
  • 本次环境未能启动 PostgreSQL 演练实例，表中的阻塞关系为按文档推导的预期结果。500 毫秒与 5 秒是示例预算，需按服务的响应时限调整。
#PostgreSQL#migrations#locks#troubleshooting
返回文章列表