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 的表级锁冲突关系

用等待链复现 DML 与 DDL 冲突

下面是多会话片段示例,需要在同一数据库打开三个 psql 会话。先创建实验表:

sql
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);

预期输出:

text
DROP TABLE
CREATE TABLE
INSERT 0 1

会话 A 开启事务并执行更新,但先不提交。UPDATE 会在表上持有 ROW EXCLUSIVE,同时还会持有行级相关锁。

sql
BEGIN;
UPDATE lock_lab SET balance = balance + 1 WHERE id = 1;
SELECT pg_backend_pid() AS blocker_pid;

预期输出:

text
BEGIN
UPDATE 1
 blocker_pid
-------------
 <PID>
(1 row)

会话 B 执行需要强锁的结构变更:

sql
ALTER TABLE lock_lab ADD COLUMN note text;

预期结果:在会话 A 提交前,语句保持等待而不会立即返回;会话 A 执行 COMMIT 后,会话 B 完成并输出:

text
ALTER TABLE

等待期间,在会话 C 查询直接阻塞关系:

sql
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 会因环境不同而变化,但关系应类似:

text
 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,更适合在线系统。

sql
CREATE INDEX CONCURRENTLY idx_lock_lab_balance
ON lock_lab (balance);

预期输出:

text
CREATE INDEX

把发布风险压到执行前

上线前先问三个问题:目标语句要拿什么关系级锁、当前是否存在长事务、这个锁会不会与线上主流请求冲突。对可能申请 ACCESS EXCLUSIVE 的 DDL,应先在预发布环境测量执行时间,并在生产设置可控的锁等待预算,避免一个等待中的结构变更把请求队列逐步堆高。

如果目标只是新增大表索引,优先评估 CREATE INDEX CONCURRENTLY;如果必须执行强 DDL,则把事务做短、避开高峰,并在执行前确认阻塞者和等待者。表级锁治理的核心不是“消灭锁”,而是让强锁出现得可预测、可观察、可快速退出。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

理解表级锁与 DDL 冲突

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通