锁冲突矩阵

前面几页分别讲了表级锁、行级锁、等待与超时。这些知识要真正用起来，还需要一张「全局地图」——哪两种锁会互相阻塞、哪两种可以并存。这张地图就是锁冲突矩阵（Lock Conflict Matrix）。

它是排查线上锁事故最快的方式：只要把「谁持有什么锁」和「谁在等什么锁」代入矩阵，就能立刻判断出「这次阻塞是不是必然的」「该等多久」「能不能换个锁模式绕过去」。本页先给出完整的 8×8 表级锁矩阵与 4×4 行级锁矩阵，再逐行解读、给出常用语句速查表，最后用矩阵把几个真实的线上锁事故逐一拆开。

前置知识

阅读本节前，建议先了解：表级锁（ACCESS SHARE / EXCLUSIVE）、行级锁（FOR UPDATE / FOR SHARE）

基础概念

冲突矩阵怎么读

冲突矩阵是一张二维真值表：

行（请求方）：某个事务正在申请的锁模式。
列（持有方）：另一个事务已经持有的锁模式。
表格内容：X 表示冲突（申请者必须等待）；空白表示兼容（可以直接获得）。

判定规则只看这两行两列，与表里有几行数据、事务多大多小都无关。

text

         持有方 →
请求方 ↓   AS   RS   RE   SUE   S    SRE   E    AE
  AS       ·    ·    ·    ·     ·    ·     ·    X
  ...

矩阵是「对称」的

表级锁冲突关系是对称的：A 与 B 冲突，则 B 与 A 也冲突。所以完整矩阵关于对角线对称。这意味着你不需要记住两个方向，只要记住「这两个模式冲不冲突」。

八种表级锁模式速记

八个模式的名字很拗口，先建立直觉：

#	模式	简称	谁在用	语义
1	`ACCESS SHARE`	AS	`SELECT`	「我只是读表」
2	`ROW SHARE`	RS	`SELECT ... FOR UPDATE/SHARE`	「我要锁表里的某些行」
3	`ROW EXCLUSIVE`	RE	`INSERT` / `UPDATE` / `DELETE`	「我要改表里的行」
4	`SHARE UPDATE EXCLUSIVE`	SUE	`VACUUM` / `CREATE INDEX CONCURRENTLY` / `ANALYZE` / `REINDEX CONCURRENTLY`	「我维护表，但和 DML 不冲突」
5	`SHARE`	S	`CREATE INDEX`（非并发）	「我要读全表建索引」
6	`SHARE ROW EXCLUSIVE`	SRE	`CREATE TRIGGER` / 某些 `ALTER TABLE`	「我要改表结构，但允许别人读」
7	`EXCLUSIVE`	E	`REFRESH MATERIALIZED VIEW`（非并发）	「我独占表，只允许别人读」
8	`ACCESS EXCLUSIVE`	AE	`DROP TABLE` / `TRUNCATE` / `LOCK TABLE` / 大多数 `ALTER TABLE` / `REINDEX` / `CLUSTER`	「我独占整张表」

记忆口诀

越往下越霸道：ACCESS EXCLUSIVE 最霸道（跟谁都冲突），ACCESS SHARE 最温和（只跟 ACCESS EXCLUSIVE 冲突）。

语法与用法

完整的 8×8 冲突矩阵

这是 PostgreSQL 官方文档中的标准矩阵（X 表示冲突）：

请求 \ 已持有	ACCESS SHARE	ROW SHARE	ROW EXCL	SHARE UPDATE EXCL	SHARE	SHARE ROW EXCL	EXCLUSIVE	ACCESS EXCL
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`	`ACCESS EXCLUSIVE`
`ROW SHARE`	`EXCLUSIVE`、`ACCESS EXCLUSIVE`
`ROW EXCLUSIVE`	`SHARE`、`SHARE ROW EXCLUSIVE`、`EXCLUSIVE`、`ACCESS EXCLUSIVE`
`SHARE UPDATE EXCLUSIVE`	自身、`SHARE`、`SHARE ROW EXCLUSIVE`、`EXCLUSIVE`、`ACCESS EXCLUSIVE`
`SHARE`	`ROW EXCLUSIVE`、`SHARE UPDATE EXCLUSIVE`、`SHARE ROW EXCLUSIVE`、`EXCLUSIVE`、`ACCESS EXCLUSIVE`
`SHARE ROW EXCLUSIVE`	`ROW EXCLUSIVE`、`SHARE UPDATE EXCLUSIVE`、`SHARE`、自身、`EXCLUSIVE`、`ACCESS EXCLUSIVE`
`EXCLUSIVE`	`ROW SHARE`、`ROW EXCLUSIVE`、`SHARE UPDATE EXCLUSIVE`、`SHARE`、`SHARE ROW EXCLUSIVE`、自身、`ACCESS EXCLUSIVE`
`ACCESS EXCLUSIVE`	全部（含自身）

四种行级锁的冲突矩阵

行级锁是另一套体系（没有间隙锁、没有页级锁）：

请求 \ 已持有	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

注意两个矩阵的语法形式不同：表级用 LOCK TABLE ... IN <mode> MODE，行级用 SELECT ... FOR <mode>。

详细说明

先从矩阵最上面两行开始。

ACCESS SHARE（AS）行：只和 ACCESS EXCLUSIVE 冲突。这是最「无害」的锁，对应所有普通 SELECT。它的意义是：只要没有人在做 DROP TABLE / TRUNCATE / 破坏性 ALTER TABLE，读表永远不会被别人阻塞。

sql

-- 两个 SELECT 永远互不阻塞（都是 ACCESS SHARE）
-- 会话 1
BEGIN; SELECT * FROM orders LIMIT 1;   -- 持有 AccessShareLock

-- 会话 2（同一时刻）
SELECT count(*) FROM orders;            -- 也能拿到 AccessShareLock，不等待
COMMIT;

text

-- 观察：两个会话在 pg_locks 中同时持有 AccessShareLock，且都 granted
 locktype |  relation  |       mode       | granted
----------+------------+------------------+---------
 relation | orders     | AccessShareLock  | t
 relation | orders     | AccessShareLock  | t

ROW SHARE（RS）行：与 EXCLUSIVE、ACCESS EXCLUSIVE 冲突。它由 SELECT ... FOR UPDATE / FOR SHARE 触发，是「我要锁行」的表级声明。

sql

-- 会话 1：持有 ROW SHARE 表锁 + 某行的行锁
BEGIN;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;

text

-- 会话 2：执行 REFRESH MATERIALIZED VIEW（需 EXCLUSIVE）会被阻塞
REFRESH MATERIALIZED VIEW mv_orders;

text

ERROR:  could not obtain lock on relation "mv_orders"

逐行解读 ROW EXCLUSIVE 与 SUE

ROW EXCLUSIVE（RE）行：INSERT / UPDATE / DELETE 的默认表锁。它与四个 SHARE 系模式冲突，但与 ACCESS SHARE 兼容——这正是「DML 不会被普通查询阻塞」的根据。

sql

-- 会话 1：UPDATE 持有 ROW EXCLUSIVE
BEGIN;
UPDATE orders SET status = 'paid' WHERE id = 1;

-- 会话 2：普通 SELECT 只要 ACCESS SHARE，完全不冲突
SELECT * FROM orders WHERE id = 1;      -- 立即返回

text

-- 会话 2 不会被阻塞；但在 READ COMMITTED 下看到的仍是旧值（MVCC 快照）
 id |  status
----+----------
  1 | pending

SHARE UPDATE EXCLUSIVE（SUE）行：这是八种模式中定位最特殊的一个——它只与自己冲突，与 AS、RS、RE 全部兼容。

sql

-- VACUUM 持有 SHARE UPDATE EXCLUSIVE
VACUUM orders;

-- 同时：INSERT / UPDATE / DELETE / SELECT 都照常进行
-- 但另一个 VACUUM 或 CREATE INDEX CONCURRENTLY 会互相阻塞

text

-- 观察：VACUUM 运行时，DML 与 SELECT 都不受影响
SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event_type = 'Lock';

text

 pid  | wait_event_type | wait_event
------+-----------------+------------
(0 rows)

为什么 VACUUM 不阻塞 DML

VACUUM 只是回收「已死」的元组版本，不修改任何人还在读的数据，因此不需要挡住 DML。而 CREATE INDEX CONCURRENTLY 会两次扫表、等待所有旧事务结束，如果同时还有另一个 VACUUM 或 CIC 在跑，两者的假设会冲突，所以 SUE 之间互斥。

继续往下，模式越来越霸道。

SHARE（S）行：只与 ROW EXCLUSIVE 及以上冲突，但与 ACCESS SHARE、ROW SHARE 兼容。对应非并发的 CREATE INDEX：它要扫全表但不能有人改数据，同时不挡读。

sql

-- 会话 1：CREATE INDEX 持有 SHARE
CREATE INDEX idx_orders_status ON orders (status);

-- 会话 2：SELECT 可以继续（ACCESS SHARE 与 SHARE 兼容）
SELECT count(*) FROM orders WHERE status = 'pending';

-- 会话 3：INSERT 会被阻塞（ROW EXCLUSIVE 与 SHARE 冲突）
INSERT INTO orders (customer, amount) VALUES ('c', 1);

text

-- 会话 3 的等待状态
 wait_event_type | wait_event
-----------------+------------
 Lock            | relation

SHARE ROW EXCLUSIVE（SRE）行：与 ROW EXCLUSIVE、SUE、SHARE、自身、EXCLUSIVE、AE 冲突，但与 AS、RS 兼容。它由 CREATE TRIGGER、部分 ALTER TABLE（如 ADD COLUMN 带默认值的老版本行为、ALTER TABLE ... SET (...) 的一部分）触发。

sql

-- 会话 1：CREATE TRIGGER 持有 SHARE ROW EXCLUSIVE
CREATE TRIGGER trg_orders_audit
AFTER UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_order();

-- 会话 2：SELECT 仍可执行（AS 兼容）
SELECT * FROM orders LIMIT 5;

-- 会话 3：UPDATE 被阻塞（RE 冲突）
UPDATE orders SET status = 'x' WHERE id = 1;

text

-- 会话 3 阻塞
 wait_event_type | wait_event
-----------------+------------
 Lock            | relation

EXCLUSIVE（E）行：只与 ACCESS SHARE 兼容——允许读、禁止一切写。对应 REFRESH MATERIALIZED VIEW（不带 CONCURRENTLY）。

sql

-- 会话 1：REFRESH MATERIALIZED VIEW 持有 EXCLUSIVE
REFRESH MATERIALIZED VIEW mv_orders;

-- 会话 2：SELECT 可以（AS 与 E 兼容）
SELECT * FROM mv_orders;

-- 会话 3：SELECT ... FOR UPDATE 被阻塞（ROW SHARE 与 E 冲突）
SELECT * FROM mv_orders FOR UPDATE;

text

-- 会话 3 阻塞在 relation 锁上
 wait_event_type | wait_event
-----------------+------------
 Lock            | relation

ACCESS EXCLUSIVE（AE）行：与所有模式冲突（包括 AS 与自身）。它是唯一能挡住 SELECT 的模式。

sql

-- 会话 1：ALTER TABLE 持有 ACCESS EXCLUSIVE
ALTER TABLE orders ADD COLUMN note text;

-- 会话 2：连最简单的 SELECT 都会被阻塞（AS 与 AE 冲突）
SELECT 1 FROM orders LIMIT 1;

text

-- 会话 2 一直等待，直到 ALTER TABLE 完成
 wait_event_type | wait_event
-----------------+------------
 Lock            | relation

为什么 AE 与所有模式冲突

ACCESS EXCLUSIVE 只有一个目标：保证在持有期间，没有任何其他人以任何方式引用这张表。原因是它要做的操作会改变表的物理结构或元数据：

DROP TABLE：表即将不存在，任何引用都是错的。
TRUNCATE：所有行即将消失，正在读的人会读到「已释放的页」。
ALTER TABLE ... ADD COLUMN：所有行的元组布局要改，正在解析行的人会错位。
ALTER TABLE ... ALTER COLUMN TYPE：要重写整表，读旧格式的人会读错。

这些操作无法容忍并发的读，因此必须独占。这也是为什么必须先取 AS 再升级（实现上的加锁顺序），以及为什么一条卡住的 AE 会堵住后面所有人。

sql

-- 经典事故：AE 卡住 → 后面的 AS 全部排队
-- 会话 1
BEGIN;
SELECT * FROM orders LIMIT 1;         -- 持有 AccessShareLock，不提交

-- 会话 2
ALTER TABLE orders ADD COLUMN x int;  -- 请求 AccessExclusiveLock，被会话 1 挡住并进入队列

-- 会话 3 之后
SELECT count(*) FROM orders;          -- 即使只需 AS，也要排在会话 2 后面（FIFO）

text

-- pg_locks 现场：会话 2、3 都是 granted = f
 pid  |  relation  |        mode         | granted
------+------------+---------------------+---------
 1001 | orders     | AccessShareLock     | t
 1002 | orders     | AccessExclusiveLock | f
 1003 | orders     | AccessShareLock     | f

为什么 AS 之间不冲突

ACCESS SHARE 的语义是「只读」。既然多个读者都只读、不修改任何东西，它们之间当然没有必要互相等待——这是并发读的基石。

从实现角度看：SELECT 走 MVCC 快照，读的是「事务开始时可见的那个版本」，不需要阻塞别人写，也不需要被别人的写阻塞（RE 与 AS 兼容）。唯一无法容忍的是「表本身要变了」（AE），因为那时快照里的元组可能已经不存在了。

sql

-- 10 个并发会话同时读同一张表，全部瞬时获得锁
SELECT pid, mode, granted
FROM pg_locks
WHERE relation = 'orders'::regclass AND mode = 'AccessShareLock';

text

 pid  |       mode       | granted
------+------------------+---------
 2001 | AccessShareLock  | t
 2002 | AccessShareLock  | t
 ...  | AccessShareLock  | t

为什么 row_exclusive 与 access_share 不冲突

同理：UPDATE 通过 MVCC 产生新版本，读的人仍能读到旧版本。两者在行层面通过行锁协调（更新同一行才阻塞），在表层面不需要互斥。

SUE 的定位与设计意图

SHARE UPDATE EXCLUSIVE 是八种模式里最容易被忽视、也最值得专门理解的一个。它的设计意图可以概括为：「我需要独占这张表的一小段时间来做维护，但我绝不想影响业务 DML。」

看看它的三个关键性质：

性质	说明
与 `AS` / `RS` / `RE` 兼容	所以 VACUUM / ANALYZE 不会阻塞任何业务读写
与自己冲突	所以我们不会同时跑两个 VACUUM 或两个 CIC
与 `SHARE` 冲突	因为「读全表建索引」与「并发维护」对表的假设不兼容

sql

-- 典型并发场景：VACUUM 与 CREATE INDEX CONCURRENTLY 不能同时跑
-- 会话 1
VACUUM orders;

-- 会话 2（会被阻塞，因为 SUE 与 SUE 冲突）
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

text

-- 会话 2 阻塞在现场
 wait_event_type | wait_event
-----------------+------------
 Lock            | relation

CIC 失败会留下无效索引

CREATE INDEX CONCURRENTLY 如果失败（包括被锁等待超时打断），会留下一个 invalid 状态的索引。它不被查询使用，但会持续拖慢写入，必须手动 DROP INDEX 后再重建。所以 CIC 更要配 lock_timeout。

用矩阵解释跨表阻塞

矩阵不只用于「同一张表」。理解它之后，可以解释为什么看似无关的操作会互相阻塞。核心是：一个事务持有的锁，会一直持续到事务结束。

考虑这个场景：事务 T1 先更新 orders，再去读 products；事务 T2 一直在读 products 并准备更新它。如果 T2 拿到了 products 的 ROW EXCLUSIVE，T1 的读不会受阻（AS 与 RE 兼容）；但反过来，如果 T1 先对 products 加了 ROW SHARE（FOR UPDATE），而 T2 想 ALTER TABLE products，就会阻塞。

sql

-- 事务 T1：长事务，跨多表
BEGIN;
UPDATE orders SET status = 'done' WHERE id = 1;   -- orders: ROW EXCLUSIVE
SELECT * FROM products WHERE id = 5 FOR UPDATE;   -- products: ROW SHARE + 行锁
-- ... 应用在这里做了别的事（发消息、调接口），一直不提交

-- 事务 T2：想给 products 加列
ALTER TABLE products ADD COLUMN note text;        -- 需要 ACCESS EXCLUSIVE → 被 T1 阻塞

text

-- 现场：T2 卡在 products 上，阻塞者是 T1
 pid  | blocked_by | relation |        mode         | granted
------+------------+----------+---------------------+---------
 3002 | {3001}     | products | AccessExclusiveLock | f
 3001 |            | products | RowShareLock        | t

结论：「锁的传播路径」就是「事务的访问路径」。要缩短阻塞面，就要缩短事务、减少它在一段连续时间里锁过的对象数。

用矩阵做并发设计

矩阵最有价值的用法是事前设计：在写代码之前就判断「我选的这个操作会不会和别人冲突」。

设计时按下面几个问题自检：

这个操作用什么表锁模式？ 查「语句 → 表锁」速查表。
它和当前业务的高频操作冲突吗？ 查矩阵。
冲突时是短暂的还是会持续很久？ 取决于事务多长。
有没有替代写法？ 比如用 CREATE INDEX CONCURRENTLY 替代 CREATE INDEX。

sql

-- 例：想给高频写入的表加索引，评估两种方案
-- 方案 A：CREATE INDEX（SHARE）→ 阻塞 INSERT/UPDATE/DELETE，但允许 SELECT
CREATE INDEX idx_big ON big_table (col);

-- 方案 B：CREATE INDEX CONCURRENTLY（SUE）→ 不阻塞 DML，但耗时更长、可能失败留无效索引
CREATE INDEX CONCURRENTLY idx_big ON big_table (col);

text

-- 用矩阵判断影响面：
-- SHARE          冲突于 RE(INSERT/UPDATE/DELETE) + SUE + SHARE + SRE + E + AE
-- SHARE UPDATE EX 冲突于 SUE(自身) + SHARE + SRE + E + AE
-- → 方案 B 对业务 DML 零影响，是热表建索引的唯一正解

语句 → 表锁模式速查表

实际排查时最需要的就是这张表：拿到一条 SQL，立刻知道它在表上加什么锁。

语句	表级锁模式
`SELECT`	`ACCESS SHARE`
`SELECT ... FOR UPDATE` / `FOR NO KEY UPDATE` / `FOR SHARE` / `FOR KEY SHARE`	`ROW SHARE`
`INSERT` / `UPDATE` / `DELETE`	`ROW EXCLUSIVE`
`COPY`（读入）	`ROW EXCLUSIVE`
`CREATE INDEX`	`SHARE`
`CREATE INDEX CONCURRENTLY`	`SHARE UPDATE EXCLUSIVE`
`DROP INDEX`（非并发）	`ACCESS EXCLUSIVE`
`DROP INDEX CONCURRENTLY`	`SHARE UPDATE EXCLUSIVE`
`VACUUM` / `ANALYZE`	`SHARE UPDATE EXCLUSIVE`
`VACUUM FULL`	`ACCESS EXCLUSIVE`
`CLUSTER`	`ACCESS EXCLUSIVE`
`REINDEX`（非并发）	`ACCESS EXCLUSIVE`
`REINDEX CONCURRENTLY`	`SHARE UPDATE EXCLUSIVE`
`CREATE TRIGGER`	`SHARE ROW EXCLUSIVE`
`DROP TRIGGER`	`ACCESS EXCLUSIVE`
`REFRESH MATERIALIZED VIEW`	`EXCLUSIVE`
`REFRESH MATERIALIZED VIEW CONCURRENTLY`	`SHARE UPDATE EXCLUSIVE`
`ALTER TABLE`（大多数）	`ACCESS EXCLUSIVE`
`ALTER TABLE ... VALIDATE CONSTRAINT`	`SHARE UPDATE EXCLUSIVE`
`TRUNCATE` / `DROP TABLE` / `LOCK TABLE`	`ACCESS EXCLUSIVE`

行级锁的行则完全不同：

语句	行级锁模式
`SELECT ... FOR UPDATE`	`FOR UPDATE`
`SELECT ... FOR NO KEY UPDATE`	`FOR NO KEY UPDATE`
`SELECT ... FOR SHARE`	`FOR SHARE`
`SELECT ... FOR KEY SHARE`	`FOR KEY SHARE`
`UPDATE`（改主键/唯一键）	`FOR UPDATE`
`UPDATE`（未改键）	`FOR NO KEY UPDATE`
`DELETE`	`FOR UPDATE`
外键检查（被引用行）	`FOR KEY SHARE`

sql

-- 用 pg_locks 验证某条语句实际加了什么表锁
SELECT l.locktype, l.mode, l.granted, c.relname
FROM pg_locks l
JOIN pg_class c ON c.oid = l.relation
WHERE l.pid = pg_backend_pid();

text

 locktype |        mode        | granted |  relname
----------+--------------------+---------+------------
 relation | AccessShareLock    | t       | orders

实战示例

下面用矩阵逐一拆解三类真实的线上锁事故。

第 1 步：建立实验表

sql

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer text NOT NULL,
  status   text NOT NULL DEFAULT 'pending'
);

INSERT INTO orders (customer, status)
SELECT 'cust-' || g, CASE WHEN g % 2 = 0 THEN 'pending' ELSE 'done' END
FROM generate_series(1, 50000) AS g;

ANALYZE orders;

text

CREATE TABLE
INSERT 0 50000
ANALYZE

第 2 步：事故一——长期 `SELECT` 阻塞 `ALTER TABLE`

sql

-- 会话 1：一个「忘了提交」的 SELECT
BEGIN;
SELECT count(*) FROM orders WHERE status = 'pending';

-- 会话 2：上线 DDL
ALTER TABLE orders ADD COLUMN note text;

第三个会话观察：

sql

SELECT pid,
       pg_blocking_pids(pid)  AS blocked_by,
       c.relname,
       l.mode,
       l.granted
FROM pg_locks l
JOIN pg_class c ON c.oid = l.relation
WHERE NOT l.granted OR cardinality(pg_blocking_pids(l.pid)) > 0;

text

 pid  | blocked_by | relname |        mode         | granted
------+------------+---------+---------------------+---------
 4102 | {4101}     | orders  | AccessExclusiveLock | f
 4101 |            | orders  | AccessShareLock     | t

用矩阵解读：会话 2 请求 AccessExclusiveLock，会话 1 持有 AccessShareLock。查矩阵第 8 行第 1 列——冲突。所以阻塞是必然的，与数据量无关。

第 3 步：事故二——`CREATE INDEX` 阻塞写入

sql

-- 会话 1：大表建索引（SHARE）
CREATE INDEX idx_orders_status ON orders (status);

-- 会话 2：业务写入
INSERT INTO orders (customer, status) VALUES ('new-cust', 'pending');

sql

-- 观察
SELECT pid, mode, granted, wait_event
FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
WHERE l.relation = 'orders'::regclass;

text

 pid  |        mode        | granted | wait_event
------+--------------------+---------+------------
 4201 | ShareLock          | t       |
 4202 | RowExclusiveLock   | f       | relation

用矩阵解读：SHARE 与 ROW EXCLUSIVE 冲突（矩阵第 5 行第 3 列是 X）。所以 CREATE INDEX 一旦在热表上执行，所有写操作都会被阻塞，直到索引建完。

sql

-- 正确做法：热表一律用 CONCURRENTLY
DROP INDEX IF EXISTS idx_orders_status;
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

text

-- 此时 SELECT / INSERT / UPDATE / DELETE 均不受阻塞（SUE 与 AS/RS/RE 兼容）
CREATE INDEX

第 4 步：事故三——`TRUNCATE` 与 `FOR UPDATE` 的直接对撞

sql

-- 会话 1：显式长事务里锁行
BEGIN;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;   -- orders: ROW SHARE + 行锁

-- 会话 2：TRUNCATE（需要 ACCESS EXCLUSIVE）
TRUNCATE orders;

text

-- 会话 2 被阻塞
ERROR:  deadlock detected   -- 或在等待中

sql

-- 现场
SELECT pid, mode, granted
FROM pg_locks
WHERE relation = 'orders'::regclass
ORDER BY pid;

text

 pid  |        mode        | granted
------+--------------------+---------
 4301 | RowShareLock       | t
 4302 | AccessExclusiveLock| f

用矩阵解读：ROW SHARE 与 EXCLUSIVE / ACCESS EXCLUSIVE 冲突。所以「有任何一个事务对表做过 FOR UPDATE」，TRUNCATE / DROP TABLE / 破坏性 DDL 都必须等待。

第 5 步：把矩阵变成自动化体检

sql

-- 实时列出「谁在等谁、等的是什么锁、是否必然冲突」
SELECT
  w.pid                                    AS waiter,
  w.mode                                   AS waiting_for,
  b.pid                                    AS blocker,
  b.mode                                   AS holding,
  c.relname,
  now() - w.query_start                    AS waited
FROM pg_locks w
JOIN pg_locks b
  ON b.relation = w.relation AND b.granted AND NOT w.granted
JOIN pg_class c ON c.oid = w.relation
WHERE NOT w.granted
ORDER BY waited DESC;

text

 waiter |    waiting_for      | blocker |    holding     | relname |    waited
--------+---------------------+---------+----------------+---------+--------------
 4102   | AccessExclusiveLock | 4101    | AccessShareLock| orders  | 00:01:12.334

这个查询可以放进监控，配合 lock_timeout 一起使用，能在事故刚发生时就定位到根因。

注意事项

错误！以为「SELECT 永远不会阻塞任何东西」。

sql

-- 错误认知：SELECT 只是读，怎么会被阻塞 / 阻塞别人？
BEGIN;
SELECT count(*) FROM big_table;   -- 已持有 ACCESS SHARE，且不提交
-- 事实：它会让后续所有 DDL（需要 AE）无限等待

sql

-- 正确做法：只读事务也要及时提交/回滚，避免长期持锁
BEGIN;
SELECT count(*) FROM big_table;
COMMIT;

错误！在热表上用非并发 CREATE INDEX。

sql

-- 错误！SHARE 与 ROW EXCLUSIVE 冲突，建索引期间所有写入都被阻塞
CREATE INDEX idx_hot ON hot_table (col);

sql

-- 正确做法：热表用并发建索引
CREATE INDEX CONCURRENTLY idx_hot ON hot_table (col);

错误！以为 VACUUM 会阻塞业务 DML。

sql

-- 错误认知：VACUUM 很重，跑的时候业务会卡
-- 事实：VACUUM 持有 SHARE UPDATE EXCLUSIVE，与 ACCESS SHARE / ROW SHARE / ROW EXCLUSIVE 全部兼容
VACUUM ANALYZE orders;

sql

-- 真正会阻塞业务的是 VACUUM FULL（ACCESS EXCLUSIVE），热表上应避免
-- 需要回收空间优先考虑 pg_repack 或分区重建
VACUUM FULL orders;   -- 阻塞一切，包括 SELECT

错误！直接 LOCK TABLE 而不加超时。

sql

-- 错误！可能无限期等待，把自己也变成阻塞源
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;

sql

-- 正确做法：配 lock_timeout，快速失败
BEGIN;
SET LOCAL lock_timeout = '3s';
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE NOWAIT;
COMMIT;

错误！认为 SHARE 和 SHARE 会互相阻塞。

sql

-- 错误认知：两个 CREATE INDEX 会打架
-- 事实：SHARE 与 SHARE 相互兼容（矩阵中是空白）

不推荐：频繁使用 SHARE ROW EXCLUSIVE 类操作（如反复 CREATE TRIGGER）。 它与 ROW EXCLUSIVE 冲突，每次都会短暂阻塞写入；批量建触发器应当在低峰期并合并成一次。

最佳实践

把矩阵当作「排查第一步」：先查两边各持什么锁，再代入矩阵判断冲突是否必然。

sql

-- 一句 SQL 同时给出「等待方模式」「持有方模式」和表名
SELECT w.pid, w.mode AS waiting, b.mode AS holding, c.relname
FROM pg_locks w
JOIN pg_locks b ON b.relation = w.relation AND b.granted
JOIN pg_class c ON c.oid = w.relation
WHERE NOT w.granted;

热表建索引一律用 CONCURRENTLY，从矩阵上规避 SHARE vs ROW EXCLUSIVE 的冲突。

sql

CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

优先用 SUE 类操作替代 AE 类操作（并发建索引、并发刷新物化视图、VALIDATE CONSTRAINT）：

sql

-- 加外键：先 NOT VALID 再 VALIDATE，避免长时间 AE
ALTER TABLE orders ADD CONSTRAINT fk_cust FOREIGN KEY (customer)
  REFERENCES customers(name) NOT VALID;          -- AE 但极短
ALTER TABLE orders VALIDATE CONSTRAINT fk_cust;  -- SHARE UPDATE EXCLUSIVE，不阻塞 DML

所有线上 DDL 配 lock_timeout，让「必然冲突」变成「可控失败」：

sql

BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;
COMMIT;

不要长期持有 ACCESS SHARE，只读事务也要尽快提交：

sql

-- 短事务是避免「AE 被 AS 阻塞」的根本手段
BEGIN;
SELECT count(*) FROM orders;
COMMIT;

给高并发写入表避免 LOCK TABLE ... IN ACCESS EXCLUSIVE MODE，需要串行化时优先用行锁或顾问锁：

sql

-- 用顾问锁做跨行/跨表的逻辑串行化，不影响其他会话读表
SELECT pg_advisory_xact_lock(12345);

把矩阵速查表贴在团队文档里，让「写 DDL 前先判断锁影响」成为本能：

text

热表上禁止：CREATE INDEX（非并发）、VACUUM FULL、CLUSTER、REINDEX（非并发）、TRUNCATE
热表上推荐：CREATE INDEX CONCURRENTLY、REINDEX CONCURRENTLY、VACUUM、ANALYZE、VALIDATE CONSTRAINT

继续学习：SKIP LOCKED 与 NOWAIT

锁冲突矩阵 ​

基础概念 ​

冲突矩阵怎么读 ​

八种表级锁模式速记 ​

语法与用法 ​

完整的 8×8 冲突矩阵 ​

四种行级锁的冲突矩阵 ​

详细说明 ​

逐行解读 ACCESS SHARE 与 ROW SHARE ​

逐行解读 ROW EXCLUSIVE 与 SUE ​

逐行解读 SHARE / SRE / EXCLUSIVE / AE ​

为什么 AE 与所有模式冲突 ​

为什么 AS 之间不冲突 ​

SUE 的定位与设计意图 ​

用矩阵解释跨表阻塞 ​

用矩阵做并发设计 ​

语句 → 表锁模式速查表 ​

实战示例 ​

第 1 步：建立实验表 ​

第 2 步：事故一——长期 SELECT 阻塞 ALTER TABLE ​

第 3 步：事故二——CREATE INDEX 阻塞写入 ​

第 4 步：事故三——TRUNCATE 与 FOR UPDATE 的直接对撞 ​

第 5 步：把矩阵变成自动化体检 ​

锁冲突矩阵

基础概念

冲突矩阵怎么读

八种表级锁模式速记

语法与用法

完整的 8×8 冲突矩阵

四种行级锁的冲突矩阵

详细说明

逐行解读 ACCESS SHARE 与 ROW SHARE

逐行解读 ROW EXCLUSIVE 与 SUE

逐行解读 SHARE / SRE / EXCLUSIVE / AE

为什么 AE 与所有模式冲突

为什么 AS 之间不冲突

SUE 的定位与设计意图

用矩阵解释跨表阻塞

用矩阵做并发设计

语句 → 表锁模式速查表

实战示例

第 1 步：建立实验表

第 2 步：事故一——长期 `SELECT` 阻塞 `ALTER TABLE`

第 3 步：事故二——`CREATE INDEX` 阻塞写入

第 4 步：事故三——`TRUNCATE` 与 `FOR UPDATE` 的直接对撞

第 5 步：把矩阵变成自动化体检