京公网安备11010502063487号“线上执行 ALTER TABLE 之后整张表的查询全卡住了” 是 PostgreSQL 最典型的线上事故之一。这篇在本机自己起一个 PostgreSQL 16.15,用三个会话复现锁队列,用 pg_locks 看到排队的锁,验证 lock_timeout 和重试循环,再逐个量 DDL 的耗时、是否重写整表。
这时(A 还在跑)在第四个会话里看谁在等谁:
select count(*), pg_sleep(2) from orders 等待事件 Timeout/PgSleep,被 0 个会话挡着
alter table orders add column extra int 等待事件 Lock/relation,被 1 个会话挡着
select count(*) from orders 等待事件 Lock/relation,被 1 个会话挡着
pg_locks 里 orders 表上的锁(mode / granted / 个数):AccessShareLock 已授予 1;AccessExclusiveLock 在排队 1;AccessShareLock 在排队 1
A:ok,总共 2.00 s
B:ok,总共 1.80 s
C:ok,总共 1.59 s
C 明明只是一条 select count(*),却一直被挡到 A 结束——因为 B 在队列里排着,后来的请求不能插队长查询拿着 ACCESS SHARE,ALTER TABLE 要 ACCESS EXCLUSIVE 只能排队,后来的 SELECT 也只能排在它后面。
B(lock_timeout = 300ms):LockNotAvailable: canceling statement due to lock timeout,0.30 s 后放弃 C:ok,只等了 0.10 s A:ok,2.00 s(没有受影响)
pg_stat_activity 里等待事件是 Lock/relation;pg_locks 里能看到排队的 AccessExclusiveLock 和被它挡住的 AccessShareLock。
B 尝试了 5 次才成功,总共 1.30 s;期间不断发 select 的探测会话,最长一次只等了 200 ms
给 DDL 设 lock_timeout,等不到锁就主动放弃,队列放开,业务查询只被挡很短时间。
add column c1 int(可空,没有默认值) 0.6 ms 没有重写 add column c2 int default 0(常量默认值) 0.5 ms 没有重写 add column c3 double precision default random() 594.2 ms 重写了整张表 alter column a type bigint(int → bigint) 540.9 ms 重写了整张表 alter column b type varchar(50)(text → varchar(50)) 503.4 ms 重写了整张表 alter column a set not null(要全表扫描检查) 60.0 ms 没有重写 drop column c1 0.3 ms 没有重写
带 lock_timeout 的重试循环,在长查询结束后拿到锁,业务查询最多被挡一个 lock_timeout。
CREATE INDEX 建索引 0.19 s;并发 INSERT 完成 66 次,最慢的一次 182 ms CREATE INDEX CONCURRENTLY 建索引 0.25 s;并发 INSERT 完成 104 次,最慢的一次 1 ms
可空列、常量默认值、drop column 几乎瞬时;volatile 默认值和改类型要重写;CREATE INDEX 挡写入,CONCURRENTLY 不挡但更慢。