pgsql-23 锁机制详解

xingliuhua 收录于笔记

2025-09-25 约 2219 字预计阅读 5 分钟

1. 概述

MVCC 让"读写不互相阻塞"，但写与写之间、以及 DDL 操作仍然需要锁来保证一致性。理解锁机制是排查线上"卡住/超时/死锁"问题的必备能力，也是面试常考点。

PostgreSQL 的锁分为几个层次：

表级锁（Table-level Lock）：保护整张表，8 种模式。
行级锁（Row-level Lock）：保护单行，SELECT FOR UPDATE 等。
页级锁：内部使用，通常无需关心。
咨询锁（Advisory Lock）：应用层自定义语义的锁。

与 MySQL 的关键差异：PostgreSQL 没有 MySQL 那样的间隙锁（Gap Lock）/ Next-Key Lock（因为 PG 靠快照隔离防幻读，不需要锁间隙）。这是两者锁模型最本质的区别之一。

2. 表级锁

2.1 八种锁模式

从弱到强（数字越大冲突越多）：

锁模式	典型触发语句	说明
ACCESS SHARE	`SELECT`	最弱，只与最强的 8 冲突
ROW SHARE	`SELECT FOR UPDATE/SHARE`
ROW EXCLUSIVE	`INSERT / UPDATE / DELETE`	DML 常见
SHARE UPDATE EXCLUSIVE	`VACUUM`、`CREATE INDEX CONCURRENTLY`、`ANALYZE`
SHARE	`CREATE INDEX`（非 CONCURRENTLY）
SHARE ROW EXCLUSIVE	少见
EXCLUSIVE		阻塞除 ACCESS SHARE 外几乎所有
ACCESS EXCLUSIVE	`DROP/TRUNCATE`、多数 `ALTER TABLE`、`VACUUM FULL`	最强，阻塞一切，包括 SELECT

2.2 冲突要点（面试）

普通 SELECT（ACCESS SHARE）与 INSERT/UPDATE/DELETE（ROW EXCLUSIVE）不冲突 —— 这就是 MVCC 读写不阻塞的体现。
ALTER TABLE 等 DDL 会申请 ACCESS EXCLUSIVE 锁，阻塞该表所有读写。这是生产事故重灾区：一个 ALTER TABLE ADD COLUMN 若排在长事务后面，会把后续所有查询全部堵住形成"锁队列雪崩"。
大多数 ALTER TABLE 虽持有最强锁但执行极快（如加可空列不重写表）；但加带默认值的列、改类型可能重写整表，持锁时间长。

-- 手动加表锁（一般不建议）
LOCK TABLE accounts IN SHARE MODE;

3. 行级锁

3.1 四种行锁模式

SELECT ... FOR UPDATE;         -- 最强行锁，阻止其他事务更新/删除/加锁该行
SELECT ... FOR NO KEY UPDATE;  -- 稍弱，UPDATE 非键列时用
SELECT ... FOR SHARE;          -- 共享行锁，允许别人读但不能改
SELECT ... FOR KEY SHARE;      -- 最弱，保护键，外键检查用

场景	用法
悲观锁扣库存	`SELECT stock FROM goods WHERE id=1 FOR UPDATE;`
只读一致性、防止被改	`FOR SHARE`

3.2 行锁与 MVCC 的关系

行级锁不影响读（其他事务仍能通过 MVCC 读到该行的旧版本），只阻塞其他"写/加锁"操作。行锁信息主要记录在元组头（xmax）和内存中，不像某些数据库把行锁单独存表。

3.3 处理锁等待

-- 拿不到锁就立刻报错，不干等（避免请求堆积）
SELECT * FROM goods WHERE id = 1 FOR UPDATE NOWAIT;

-- 跳过被锁住的行（典型：任务队列，多个 worker 抢任务）
SELECT * FROM jobs WHERE status='pending'
ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 10;

-- 全局设置锁等待超时
SET lock_timeout = '5s';

FOR UPDATE SKIP LOCKED 是实现"数据库任务队列"的利器，多个消费者并发抢任务而不互相阻塞。

4. 死锁（Deadlock）

两个事务互相持有对方需要的锁，形成循环等待。

事务A: 锁住 行1 → 等待 行2
事务B: 锁住 行2 → 等待 行1     ← 死锁

PostgreSQL 有自动死锁检测：deadlock_timeout（默认 1s）后检查是否成环，若成环则主动 abort 其中一个事务并报错 deadlock detected，另一个继续。

避免死锁的实践：

多事务以固定顺序访问资源（如都先锁小 id 再锁大 id）。
尽量缩短事务、减少持锁时间。
用 NOWAIT / lock_timeout 快速失败重试。

5. 咨询锁（Advisory Lock）

应用层自定义语义的锁，PG 只负责加/解，锁的"含义"由业务决定。常用于分布式互斥（如保证某个定时任务全局只有一个实例在跑）。

-- 会话级：直到 unlock 或断开连接
SELECT pg_advisory_lock(12345);
SELECT pg_advisory_unlock(12345);

-- 事务级：事务结束自动释放（更安全，推荐）
SELECT pg_advisory_xact_lock(12345);

-- 尝试获取，拿不到立即返回 false（不阻塞）
SELECT pg_try_advisory_lock(12345);

6. 排查锁问题（运维必备）

-- 查看当前的锁与阻塞关系（谁在等谁）
SELECT
    blocked.pid          AS blocked_pid,
    blocked.query        AS blocked_query,
    blocking.pid         AS blocking_pid,
    blocking.query       AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
     ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

-- 查看所有锁
SELECT * FROM pg_locks WHERE NOT granted;

-- 紧急处理：终止某个持锁进程
SELECT pg_cancel_backend(pid);     -- 温和：取消当前查询
SELECT pg_terminate_backend(pid);  -- 强制：断开整个连接

pg_blocking_pids(pid) 是排查阻塞链的利器，直接告诉你某进程在等哪些进程。

7. PostgreSQL vs MySQL 锁对比

维度	PostgreSQL	MySQL (InnoDB)
间隙锁/Next-Key	❌ 无（快照隔离防幻读）	✅ RR 下用间隙锁防幻读
行锁实现	元组头 xmax + 内存	索引记录上的锁
无索引的 UPDATE	锁命中的行	可能升级为锁大量行（锁加在索引上）
死锁检测	自动，abort 一个	自动，回滚代价小的
DDL 锁	多数 ACCESS EXCLUSIVE	8.0+ 支持部分 Online DDL
咨询锁	✅ 内置 pg_advisory_*	需用 GET_LOCK() 函数

面试要点：MySQL 在可重复读下靠间隙锁防幻读，容易在"范围更新+并发插入"时产生间隙锁冲突甚至死锁；PostgreSQL 靠 MVCC 快照防幻读，没有间隙锁概念，锁模型更简单直观。

8. 面试高频问答

Q1：PostgreSQL 有间隙锁吗？和 MySQL 有什么不同？ 没有。PG 靠 MVCC 快照隔离防幻读，不需要锁间隙；MySQL InnoDB 在 RR 下用 Next-Key（记录锁+间隙锁）防幻读。

Q2：SELECT 会阻塞 UPDATE 吗？ 普通 SELECT 持 ACCESS SHARE，UPDATE 持 ROW EXCLUSIVE，两者不冲突，互不阻塞（MVCC）。但 ALTER TABLE 等 DDL 持 ACCESS EXCLUSIVE 会阻塞 SELECT。

Q3：怎么实现悲观锁扣减库存？ SELECT ... FOR UPDATE 锁定该行，其他事务的更新/加锁会阻塞，直到本事务提交。

Q4：PostgreSQL 如何处理死锁？ deadlock_timeout 后检测等待图是否成环，成环则自动 abort 其中一个事务并报 deadlock detected。避免方法是固定资源访问顺序、缩短事务。

Q5：FOR UPDATE SKIP LOCKED 有什么用？ 跳过已被其他事务锁定的行，常用于数据库任务队列，让多个消费者并发抢任务而不互相阻塞。

Q6：线上查询突然全部卡住怎么排查？ 用 pg_stat_activity 看 wait_event_type=‘Lock’ 的会话，用 pg_blocking_pids() 找到阻塞源头，常见是长事务或 DDL 持有 ACCESS EXCLUSIVE，必要时 pg_terminate_backend 终止。

9. 下一步

学习索引原理与类型，掌握 PG 丰富的索引家族。

目录