PostgreSQL / 系统课时
理解表级锁与 DDL 冲突
理解表级锁与 DDL 冲突
表级锁决定了“这条语句能不能开始执行”,也是数据库发布最容易被低估的风险来源。本课把常见 DML、索引维护和 DDL 映射到实际锁模式,并用多会话实验复现阻塞链,最终能在变更前判断哪些操作会暂停读写。
学习目标
- 说清常见 SQL 会申请哪些关系级锁,以及它们之间的关键冲突。
- 用
pg_stat_activity与pg_blocking_pids()找出等待中的 DDL 和直接阻塞者。 - 判断普通建索引、并发建索引和强 DDL 对线上读写的影响。
常见操作到底拿什么表锁
PostgreSQL 的关系级锁不是“读锁/写锁”两种,而是多种模式。排查发布阻塞时,不必先背完整冲突矩阵,先抓住最常见的几组关系即可。
| 操作 | 常见关系级锁 | 关键影响 |
|---|---|---|
普通 SELECT |
ACCESS SHARE |
只与 ACCESS EXCLUSIVE 冲突 |
INSERT、UPDATE、DELETE、MERGE |
ROW EXCLUSIVE |
会被 SHARE、ACCESS EXCLUSIVE 等更强模式阻塞 |
CREATE INDEX |
SHARE |
与 ROW EXCLUSIVE 冲突,因此会阻塞并发写入 |
CREATE INDEX CONCURRENTLY |
SHARE UPDATE EXCLUSIVE |
通常可与普通 DML 并行,但会与同类维护和更强 DDL 冲突 |
多数重型 ALTER TABLE、TRUNCATE、DROP TABLE |
ACCESS EXCLUSIVE |
会阻塞普通查询和写入,是发布窗口的最高风险锁之一 |
ACCESS EXCLUSIVE 并不等于“只有 DDL 才会拿”,但很多结构变更都会请求它。更重要的是,锁通常持有到事务结束;把多个变更放进一个长事务,会把阻塞时间扩大到整个事务周期。

用等待链复现 DML 与 DDL 冲突
下面是多会话片段示例,需要在同一数据库打开三个 psql 会话。先创建实验表:
DROP TABLE IF EXISTS lock_lab;
CREATE TABLE lock_lab (
id bigint PRIMARY KEY,
balance numeric(12, 2) NOT NULL
);
INSERT INTO lock_lab VALUES (1, 100.00);预期输出:
DROP TABLE
CREATE TABLE
INSERT 0 1会话 A 开启事务并执行更新,但先不提交。UPDATE 会在表上持有 ROW EXCLUSIVE,同时还会持有行级相关锁。
BEGIN;
UPDATE lock_lab SET balance = balance + 1 WHERE id = 1;
SELECT pg_backend_pid() AS blocker_pid;预期输出:
BEGIN
UPDATE 1
blocker_pid
-------------
<PID>
(1 row)会话 B 执行需要强锁的结构变更:
ALTER TABLE lock_lab ADD COLUMN note text;预期结果:在会话 A 提交前,语句保持等待而不会立即返回;会话 A 执行 COMMIT 后,会话 B 完成并输出:
ALTER TABLE等待期间,在会话 C 查询直接阻塞关系:
SELECT
pid AS waiting_pid,
wait_event_type,
pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE state = 'active'
AND query LIKE 'ALTER TABLE lock_lab ADD COLUMN note text%';预期输出中的 PID 会因环境不同而变化,但关系应类似:
waiting_pid | wait_event_type | blocking_pids
-------------+-----------------+---------------
<PID> | Lock | {<PID>}
(1 row)pg_blocking_pids() 返回的是当前直接阻塞该会话的后端 PID,比只看 pg_locks.granted = false 更适合快速建立等待链。
索引维护为什么要区分 CONCURRENTLY
普通 CREATE INDEX 需要 SHARE 锁,它与业务写入常见的 ROW EXCLUSIVE 冲突,所以大表建索引可能把写流量卡在发布窗口里。CREATE INDEX CONCURRENTLY 采用更弱的关系级锁,并通过多阶段扫描避免长时间阻塞普通 DML,更适合在线系统。
CREATE INDEX CONCURRENTLY idx_lock_lab_balance
ON lock_lab (balance);预期输出:
CREATE INDEX把发布风险压到执行前
上线前先问三个问题:目标语句要拿什么关系级锁、当前是否存在长事务、这个锁会不会与线上主流请求冲突。对可能申请 ACCESS EXCLUSIVE 的 DDL,应先在预发布环境测量执行时间,并在生产设置可控的锁等待预算,避免一个等待中的结构变更把请求队列逐步堆高。
如果目标只是新增大表索引,优先评估 CREATE INDEX CONCURRENTLY;如果必须执行强 DDL,则把事务做短、避开高峰,并在执行前确认阻塞者和等待者。表级锁治理的核心不是“消灭锁”,而是让强锁出现得可预测、可观察、可快速退出。