---
title: "PostgreSQL 加一列为什么会卡住 SELECT：用三个会话看懂 DDL 锁队列"
date: 2026-11-12
slug: postgresql-ddl-lock-queue
canonical: https://kernelranch.com/blog/2026/11/postgresql-ddl-lock-queue
markdown_url: https://kernelranch.com/blog/2026/11/postgresql-ddl-lock-queue.md
tags: ["PostgreSQL", "migrations", "locks", "troubleshooting"]
---发布时执行 `ALTER TABLE ... ADD COLUMN`，几秒后连普通 `SELECT` 都开始超时。先查 `pg_blocking_pids()`。一个打开事务后迟迟未提交的读请求，可以让加列语句在队列里等待。排在 DDL 后面的新读请求也可能被它挡住。一张只有一行的表就足以复现这条阻塞链。

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

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

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

```sql
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：

```sql
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` 开始重试：

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

## 2. 查清楚谁在等谁

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

```sql
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 TABLE` | A | A 持有 `AccessShareLock`，B 等待 `AccessExclusiveLock` |
| C 的 `SELECT` | B | C 请求 `AccessShareLock`，与排在前面的 B 所请求的锁冲突 |

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

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

```sql
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 的事务内。如果工具已经打开事务，使用事务内部的三条语句，由工具负责提交和异常回滚。

```sql
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 一页纸](/blog/2026/11/pg-stat-activity-cheatsheet.md)，确认长事务来自哪个应用实例。

## 小结

加列前先给获取锁设预算，拿到锁后尽早提交。读请求在发布期间一起变慢时，用 `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.html | `pg_blocking_pids()` 如何识别持锁者和排在前面的等待者 |
| PostgreSQL 18 客户端参数 | https://www.postgresql.org/docs/18/runtime-config-client.html | 两种超时的范围与计时方式 |
| PostgreSQL 18 `ALTER TABLE` | https://www.postgresql.org/docs/18/sql-altertable.html | 加列锁级别、默认值与表重写 |
| PostgreSQL 18 错误码 | https://www.postgresql.org/docs/18/errcodes-appendix.html | `55P03` 与 `57014` |
| PostgreSQL 18 统计视图 | https://www.postgresql.org/docs/18/monitoring-stats.html | `pg_stat_activity` 字段和权限 |

## 核验说明

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