PostgreSQL / 系统课时
理解外键引发的锁等待
理解外键引发的锁等待
外键校验不只是一次普通索引查询:子表写入需要确认父键在当前并发下仍可被引用,父表删除或修改被引用键也要防止产生悬空引用。本课用父子表和两个会话复现等待,拆清行锁关系、最终约束结果与排查方法。
学习目标
- 理解子表外键检查为什么会在父表行上形成锁依赖。
- 复现父键更新、父行删除与子表写入之间的双向等待。
- 用
pg_stat_activity和pg_blocking_pids找到阻塞链,并缩短外键相关锁等待。
外键检查不是纯读操作
当插入或更新子表外键值时,PostgreSQL 会在被引用父行上执行等价于 FOR KEY SHARE 的保护:它允许父行的普通非键字段继续更新,但会与删除父行或修改被引用键值所需的更强行锁冲突。这样可以保证“外键检查通过”到事务结束之间,父键不会悄悄消失或改掉。
因此,不要把现象简化成“父表只要有 UPDATE,子表 INSERT 就会被挡住”。普通非键更新通常取得 FOR NO KEY UPDATE,它与 FOR KEY SHARE 不冲突;真正需要重点关注的是父行 DELETE、被引用键更新,以及长期未结束的事务。

建立最小父子表
下面显式使用默认的 ON DELETE NO ACTION,并为子表外键列建立索引。父表主键本身已有唯一索引;子表外键列不会因为声明外键而自动创建索引。
DROP TABLE IF EXISTS child;
DROP TABLE IF EXISTS parent;
CREATE TABLE parent (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE child (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
parent_id integer NOT NULL REFERENCES parent(id) ON DELETE NO ACTION,
note text NOT NULL
);
CREATE INDEX child_parent_id_idx ON child(parent_id);
INSERT INTO parent (id, name) VALUES (1, 'parent-1');预期输出(首次运行时可能额外出现对象不存在的 NOTICE):
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
INSERT 0 1子表索引不会消除行锁冲突,但父表执行删除或被引用键更新时,通常需要检查是否存在匹配的子行。索引能避免对子表做不必要的大范围扫描,从而缩短检查耗时和事务持锁时间。
父键变更为什么会卡住子表写入
在会话 A 修改被引用主键,但保持事务打开:
BEGIN;
-- 修改被外键引用的键值,会与子表外键检查需要的 KEY SHARE 冲突。
UPDATE parent SET id = 2 WHERE id = 1;预期输出:
BEGIN
UPDATE 1在会话 B 尝试插入仍引用旧键值 1 的子行。为避免演示无限等待,在事务内设置两秒锁超时:
BEGIN;
SET LOCAL lock_timeout = '2s';
INSERT INTO child (parent_id, note) VALUES (1, 'wait-for-parent');
ROLLBACK;预期输出:
BEGIN
SET
ERROR: canceling statement due to lock timeout
ROLLBACK这里首先发生的是锁等待,不是立即判定外键不存在:会话 B 必须等会话 A 的键值修改得到提交或回滚结论,才能知道 parent_id=1 最终还能不能被引用。若会话 A 提交为 id=2,插入随后会因找不到父键而失败;若会话 A 回滚,旧父键恢复,插入可以成功。
现在在会话 A 执行 ROLLBACK,再由会话 B 重试:
INSERT INTO child (parent_id, note) VALUES (1, 'after-rollback');预期输出:
INSERT 0 1子表写入也会挡住父行删除
锁依赖可以反过来观察。先清掉刚才的子行,再在会话 A 新增一条引用父行的记录并保持事务打开:
DELETE FROM child;
BEGIN;
-- 外键检查成功后,对被引用父行的保护会持续到事务结束。
INSERT INTO child (parent_id, note) VALUES (1, 'hold-reference');预期输出:
DELETE 1
BEGIN
INSERT 0 1此时会话 B 删除父行,需要取得与 FOR KEY SHARE 冲突的行锁,所以会先等待:
BEGIN;
SET LOCAL lock_timeout = '2s';
DELETE FROM parent WHERE id = 1;
ROLLBACK;预期输出:
BEGIN
SET
ERROR: canceling statement due to lock timeout
ROLLBACK会话 A 执行 COMMIT 后,引用关系已经真正存在。会话 B 再次删除父行时,不再只是等待问题,而会得到外键约束错误:
DELETE FROM parent WHERE id = 1;预期结果类似:
ERROR: update or delete on table "parent" violates foreign key constraint "child_parent_id_fkey" on table "child"
DETAIL: Key (id)=(1) is still referenced from table "child".如果约束使用 ON DELETE CASCADE、SET NULL 等动作,等待解除后的最终行为会不同,但并发事务仍需要协调父子行的引用关系。
从阻塞链定位到治理动作
线上出现“明明只是 INSERT 子表却卡住”时,先查正在等待锁的会话及其阻塞者,不要只看子表 SQL:
SELECT
a.pid,
a.wait_event_type,
a.wait_event,
pg_blocking_pids(a.pid) AS blocking_pids,
left(a.query, 120) AS query
FROM pg_stat_activity AS a
WHERE a.datname = current_database()
AND a.wait_event_type = 'Lock';预期结果示意:
pid | wait_event_type | wait_event | blocking_pids | query
-------+-----------------+------------+---------------+-----------------------------------------
<PID> | Lock | <VALUE> | {<PID>} | INSERT INTO child (parent_id, note) ...
(1 row)<PID> 和 <VALUE> 会随现场变化。拿到阻塞 PID 后,应继续核对阻塞事务正在执行什么、事务已经持续多久,以及它是否正在删除父行或修改被引用键。治理重点通常是缩短这类事务、避免把交互等待放在事务内部,并给常被父表反查的外键列建立合适索引。
外键导致的锁等待本质上是在保护“引用检查的结论”不被并发事务立即推翻。排查时先判断是在等锁还是已经进入约束失败,再沿阻塞 PID 找到真正持锁的父表或子表事务,才能避免误把正常的完整性保护当成数据库异常。