PostgreSQL 的常见锁:为什么你的 ALTER TABLE 会卡住_MsSqlserver

你的 alter table 卡住，核心原因是它需要获取 ‌access exclusive 锁‌，这是 postgresql 中最强的表级锁，会与表上所有其他锁冲突（包括普通的 select）。只要有其他会话以任何方式持有了这张表的锁（哪怕只是一个长时间运行的查询），你的 alter table 就得排队等它释放，表现就是“卡住”。

上周同事问我，为什么给一张表加个字段，整个表就像被冻住了一样，连普通的 select 都进不来。其实这就是 postgresql 的锁在起作用。

很多人用 pg，觉得它 mvcc 很厉害，读写互不阻塞。这没错，但 mvcc 主要解决的是“读”和“写”之间的冲突。真到了写和写、ddl 和 dml 之间，还是得靠锁来协调。今天就把 pg 里常见的锁捋一遍，顺便说说怎么排查锁等待和死锁。

先搞清楚：普通 select 到底加不加锁？

这是最容易误解的地方。在 postgresql 里，普通的 select 只会在表上加一个 access share 锁，这个锁非常弱，唯一会跟它冲突的就是 access exclusive。也就是说，你平时查询，基本不会阻塞别人，别人也很难阻塞你。

真正会打架的是写操作和 ddl。比如 alter table、drop table、truncate 这些通常会加 access exclusive，这个锁一出来，所有其他访问都得排队。

所以，那个同事遇到的“加字段卡住”，大概率是 alter table 在等 access exclusive，而前面有长事务或者慢查询占着表，导致后面所有请求都堵住了。

表级锁

postgresql 表级锁有 8 种模式，强度从弱到强排下来是：

access share
row share
row exclusive
share update exclusive
share
share row exclusive
exclusive
access exclusive

兼容矩阵我贴在下面，x 表示冲突，空白表示兼容：

请求\持有	access share	row share	row exclusive	share update exclusive	share	share row exclusive	exclusive	access exclusive
access share								x
row share							x	x
row exclusive					x	x	x	x
share update exclusive				x	x	x	x	x
share			x	x		x	x	x
share row exclusive			x	x	x	x	x	x
exclusive		x	x	x	x	x	x	x
access exclusive	x	x	x	x	x	x	x	x

这张表不用背，记住几个关键就行：

access share 是最弱的，普通 select 拿的就是它。它只跟 access exclusive 冲突。
row exclusive 是 insert/update/delete 拿的，它跟 share、share row exclusive、exclusive、access exclusive 冲突。
access exclusive 是老大，跟所有锁都冲突。alter table、drop table、truncate、vacuum full、cluster、reindex 这些基本都会拿它。
create index concurrently 比较特殊，它拿的是 share update exclusive，所以不会阻塞 dml，这也是大表建索引推荐它的原因。

我一般会提醒团队：ddl 之前一定要设 lock_timeout。不然一个 alter table 可能因为等锁排到所有查询后面，把整个库拖慢。

set lock_timeout = '3s';
alter table users add column age int;

如果 3 秒拿不到锁，它自己就放弃了，不会一直堵着。

行级锁

行级锁是写操作在行上加的锁。普通 select 不加行锁。四种行锁：

for key share
for share
for no key update
for update

强度从左到右递增。兼容矩阵：

请求\持有	for key share	for share	for no key update	for update
for key share				x
for share			x	x
for no key update		x	x	x
for update	x	x	x	x

几个实际场景：

update 通常拿 for no key update。如果更新的是唯一键列，可能会升级成 for update。
delete 通常拿 for update。
select ... for update 显式拿最强的行锁，用来做悲观锁。
select ... for share 拿共享锁，别人可以读，但不能改。
select ... for key share 最弱，外键检查时经常用。比如子表插入一条记录，会去父表对应行上加 for key share，防止父表键被删掉或改掉。

行锁信息存在元组头的 xmax 里。多个事务锁同一行时，pg 会用 multixact 来记录。行锁在事务结束时释放，所以长事务会一直占着行锁，这是很多锁等待的根源。

一个常见坑：外键会让子表写入阻塞父表键更新。比如你更新父表的主键，子表可能正在插入，两边就会互相等。设计外键时心里要有数。

锁等待和死锁，怎么查？

pg 提供了几个视图，最常用的是 pg_locks 和 pg_stat_activity。

查当前没拿到的锁：

select pid, locktype, relation::regclass, mode, granted, query
from pg_locks l
left join pg_stat_activity a using (pid)
where not granted;

查谁被谁阻塞了：

select pid, pg_blocking_pids(pid) as blocking_pids, query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

pg_blocking_pids 这个函数特别好用，直接告诉你哪个进程堵住了当前查询。

如果要杀查询，有两个选择：

select pg_cancel_backend(pid);   -- 取消当前查询，连接还在
select pg_terminate_backend(pid); -- 直接断开连接

一般先 cancel，不行再 terminate。

死锁是另一个话题。pg 有 deadlock_timeout，默认 1 秒。如果一个事务等锁超过这个时间，就会触发死锁检测。检测到循环等待，它会杀掉其中一个事务，报 deadlock detected，应用应该捕获这个错误并重试。

死锁没法完全避免，但可以降低概率：

事务尽量短，别在事务里等用户输入。
多个表更新时，固定访问顺序。
用 select ... for update nowait 或 skip locked 避免无限等待。

比如队列消费场景，skip locked 就很好用：

select * from jobs
where status = 'pending'
order by id
limit 1
for update skip locked;

这样多个 worker 可以同时取任务，不会互相等。

咨询锁：应用层的互斥

除了表锁和行锁，pg 还有咨询锁（advisory lock）。它不绑定任何数据库对象，纯粹是应用自己约定的一把锁。

会话级：

select pg_advisory_lock(12345);
select pg_advisory_unlock(12345);

事务级：

select pg_advisory_xact_lock(12345);

事务级会在事务结束时自动释放，用起来更省心。咨询锁适合做“同一时间只有一个任务跑”这种场景，比如定时任务、分布式锁的简易实现。但注意，它只在同一个数据库集群内有效，跨实例不行。

几个实践里踩过的坑

长事务是万恶之源。一个事务开着不提交，行锁不释放，vacuum 也清不掉死元组。监控 pg_stat_activity 里 state = 'idle in transaction' 的会话。
ddl 一定要加 lock_timeout。尤其是 alter table，不加锁超时，它可能排在一个长查询后面，把后面所有请求都堵死。
大表建索引用 create index concurrently。普通 create index 会拿 share 锁，阻塞写。并发建索引虽然慢一点，但不阻塞 dml。
vacuum full 和 cluster 会拿 access exclusive，生产环境慎用，最好在低峰期做。
隔离级别要心里有数。read committed 是默认，每个语句一个快照。repeatable read 和 serializable 可能报序列化失败，应用要有重试逻辑。
监控锁等待。可以定期查 pg_locks 里 granted = false 的记录，或者用 pg_blocking_pids 看阻塞链。