Skip to content
2027校招后端+Agent八股

PostgreSQL 锁机制与高并发排障

8 种表级锁、元组级行锁（FOR UPDATE/NO KEY UPDATE）、行锁保存在元组上的秘密、咨询锁（Advisory Locks）与死锁排障。

Updated View as Markdown
For humans

PostgreSQL 锁机制与高并发排障

校招考察频度：★ 高频（核心必背·高频生死线） 一句话速记：MVCC 读写不互斥，加锁只为防并发改；表锁精细分八级，在线建索引加 CONCURRENTLY；行锁直接写元组，永不锁升级；业务防重咨询锁，排障先查 pg_locks。

在高并发系统和技术面试中，锁机制是直接考察候选人高可用架构保障与线上真实生产排障能力的硬核考点。PostgreSQL 拥有非常精细且独特的锁设计，尤其是行锁的物理实现机制，在面试中是极具杀伤力的加分项。

一、PostgreSQL 锁的架构全景

PostgreSQL 的并发控制遵循核心原则：“读不阻塞写，写不阻塞读”。 只有当两个事务试图同时修改同一行数据，或者执行 DDL（表结构变更）与维护命令时，锁的阻塞才会真实发生。

PostgreSQL 锁体系层次：
├── 表级锁（Table-level Locks）：共 8 级，细粒度协调 DML、DDL 与维护操作
├── 行级锁（Row-level Locks）：FOR UPDATE / NO KEY UPDATE / SHARE / KEY SHARE
├── 页级锁（Page-level Locks）：内部临时锁，短期保护内存中 8KB 数据页免受并发破坏
└── 咨询锁（Advisory Locks）：由应用程序代码自定义的分布式锁原语

二、8 种表级锁及其冲突矩阵（面试高频深度）

面试官：“你在生产千万级大表上做过表结构变更吗？为什么执行 DDL 会把整个数据库拖死？如何安全上线？”

PostgreSQL 没有将表锁简单粗暴地分为“共享锁”和“排他锁”，而是设计了 8 种表级锁，目的就是在保证安全的前提下最大限度允许并发：

锁级别典型触发操作锁冲突范围（与谁冲突）生产注意点
1. AccessShareLock普通 SELECT仅与 AccessExclusiveLock 冲突允许所有读写并发。
2. RowShareLockSELECT ... FOR UPDATE / FOR SHARE与 Exclusive、AccessExclusive 冲突行锁意向锁。
3. RowExclusiveLockINSERT、UPDATE、DELETE与 Share、ShareRowExclusive、Exclusive、AccessExclusive 冲突常规 DML 自动获取，多个事务可并发写不同行。
4. ShareUpdateExclusiveLockVACUUM（非 FULL）、ANALYZE、CREATE INDEX CONCURRENTLY与自身及更高级别冲突，不阻塞 SELECT、INSERT、UPDATE、DELETE！高并发黄金锁：允许维护操作与业务读写完全并发！
5. ShareLock普通 CREATE INDEX阻塞常规并发写入（DML），允许 SELECT普通建索引会阻塞写，生产禁止直接使用！
6. ShareRowExclusiveLockCREATE TRIGGER、部分 ALTER TABLE阻塞写入和弱排他锁较少显式使用。
7. ExclusiveLock阻塞绝大部分写与只读事务仅允许 AccessShareLock（纯读）并发极少自动使用。
8. AccessExclusiveLockDROP TABLE、TRUNCATE、VACUUM FULL、普通 ALTER TABLE与所有 8 种锁完全冲突！排斥一切读写！大杀器：获取该锁时，表完全不可读写，业务直接不可用。

生产避坑指南：如何在千万级大表上安全变更？

  1. 安全创建索引（必答题）：
    • 错误做法：CREATE INDEX idx_user ON orders(user_id);（获取 ShareLock，写操作全部挂起，长事务一堵瞬间打满连接池）。
    • 正确做法：必须加上 CONCURRENTLY： 
      CREATE INDEX CONCURRENTLY idx_user ON orders (user_id);
    • 原理：仅获取 ShareUpdateExclusiveLock，创建过程中所有的增删改查业务畅通无阻。
  2. 大表添加字段：
    • PostgreSQL 11 之前添加带 DEFAULT 的字段会重写全表并持锁巨久；
    • PostgreSQL 11+ 对此进行了核心优化：添加带常数默认值的列，仅需毫秒级获取元数据锁，并在系统表中记录默认值，不重写全表数据页，可以瞬间完成。

三、元组级行锁（Row-level Locks）的底层黑科技

1. 四种行级锁的语义与选择

行锁模式锁定强度典型 SQL 示例适用场景
FOR UPDATE最强排他SELECT * FROM account WHERE id = 1 FOR UPDATE;涉及本行任何字段（包括主键/外键/唯一键）的并发安全修改。
FOR NO KEY UPDATE弱排他SELECT * FROM account WHERE id = 1 FOR NO KEY UPDATE;高并发推荐：只修改非索引列（如修改 nickname、balance）。允许其他事务对本表进行并发的外键关联检查！
FOR SHARE共享读锁SELECT * FROM goods WHERE id = 1 FOR SHARE;阻止他人修改该行，但允许多个事务同时加读锁。
FOR KEY SHARE弱共享读锁外键关联检查时自动加锁仅要求唯一键不变，允许他人并发执行 FOR NO KEY UPDATE。

2. 灵魂拷问：锁定 1 亿行会不会把内存撑爆？

面试官常问：“如果一个事务通过条件一次性锁定了 1 亿行记录，数据库会不会发生‘锁升级（Lock Escalation）’或者内存耗尽崩掉？”

高分标准答案：PostgreSQL 绝对不会发生锁升级，也完全不会耗尽内存！

  • 底层原因：
    • 传统数据库若将每条行锁作为一个对象保存在共享内存的“锁表（Lock Table）”中，当锁定的行数达到几千万时，共享内存会被耗尽，被迫将行锁强制升级为表锁（锁升级）。
    • 但在 PostgreSQL 中，元组级行锁根本不存放在内存锁表里！
    • 当你对某一行执行 FOR UPDATE 时，PostgreSQL 会直接修改该行在堆表数据页中的元组头部（Tuple Header）：
      1. 将元组头的 xmax 字段设置为当前加锁事务的事务 ID；
      2. 将元组头的状态标记 t_infomask 设置为包含 HEAP_XMAX_EXCL_LOCK 标志。
  • 核心结论：
    • 行锁的本质就是数据页元组头部的一个属性位！
    • 无论你锁定 1 行还是 1 亿行，所消耗的锁内存都是 0 字节！因此 PostgreSQL 根本不需要也不存在“锁升级”机制。

四、应用级利器：咨询锁（Advisory Locks）

很多同学做秒杀、定时任务防重时第一反应是引入 Redis 分布式锁，但面试官很喜欢追问：“如果系统不想多引入一套 Redis 组件，如何直接依靠 PostgreSQL 实现分布式锁？”

答案就是 PostgreSQL 原生咨询锁（Advisory Locks）！

1. 核心特性

  • 锁的逻辑和 ID 完全由应用端指定（传入一个 64 位整数或两个 32 位整数）；
  • 性能极高，锁状态纯内存管理；
  • 不会阻塞任何数据表的读写。

2. 两种作用域

事务级咨询锁（推荐）

-- 尝试获取锁，事务结束（COMMIT / ROLLBACK）自动释放
SELECT pg_advisory_xact_lock(1001);

-- 非阻塞版本：抢不到立刻返回 false，不卡顿
SELECT pg_try_advisory_xact_lock(1001);
  • 优势：由事务生命周期托管，即使应用进程突然宕机崩溃，数据库连接断开触发事务回滚，锁自动瞬间释放，绝不会发生死锁残留！

会话级咨询锁

SELECT pg_advisory_lock(1001);
-- 必须在业务处理完毕后显式释放
SELECT pg_advisory_unlock(1001);

五、死锁的本质与线上实战排障

1. 死锁触发原理

两个并发事务交叉申请同一组资源，形成环路等待：

  • 事务 A：锁住行 1（持有），等待获取行 2（阻塞）；
  • 事务 B：锁住行 2（持有），等待获取行 1（阻塞）。

2. PostgreSQL 自动死锁检测

  • 由内核参数 deadlock_timeout（默认 1 秒）控制；
  • 当事务申请锁等待超过 1 秒时，触发死锁检测算法，在有向等待图（Wait-for Graph）中寻找是否存在环；
  • 若检测到环路，PostgreSQL 会自动选择并主动回滚（Abort）其中一个代价最小的事务，向客户端抛出错误代码 40P01 (deadlock_detected)，解开环路让另一个事务顺利完成。

3. 线上锁等待排查 SQL（必须熟练掌握）

当生产环境出现接口响应剧烈变慢、连接数飙升时，立刻执行以下排障查询：

SELECT 
    blocked_activity.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_activity.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS blocking_statement,
    now() - blocked_activity.query_start AS waiting_duration
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

4. 应急处置与代码级避坑

  1. 现场应急杀会话：
    • 软取消当前查询：SELECT pg_cancel_backend(blocking_pid);
    • 强行切断连接释放锁：SELECT pg_terminate_backend(blocking_pid);
  2. 代码规避四大准则：
    • 严格保持一致的加锁顺序：例如跨表更新永远先操作 orders 表再操作 order_items 表；操作多行记录时必须先对 ID 进行升序排序（ORDER BY id ASC FOR UPDATE）；
    • 缩减持锁事务生命周期：网络 I/O、RPC 调用、第三方支付网关请求严禁放在数据库事务内执行；
    • 优先使用 FOR NO KEY UPDATE：避免无意义地阻塞其他事务的外键检查；
    • 善用 NOWAIT 或 SKIP LOCKED： 
      -- 抢锁失败立刻报错，不排队等待
SELECT * FROM queue WHERE status = 'READY' LIMIT 1 FOR UPDATE NOWAIT;

-- 任务队列神器：直接跳过已经被别人锁住的行，并发吞吐极高
SELECT * FROM queue WHERE status = 'READY' LIMIT 1 FOR UPDATE SKIP LOCKED;
Navigation

Type to search…

↑↓ navigate↵ selectEsc close