PostgreSQL中外连接可空字段的悲观锁实现方案咨询

问题背景 #

在PostgreSQL中尝试为包含外键的复杂实体实现悲观锁时，发现无法对带有外连接可空字段的查询使用SELECT FOR UPDATE。需要执行关联查询并锁定A、B、C表中的对应行，同时锁定引用所选行的B、C表插入操作。

表结构示例 #

create table A(
  id serial primary key,
  name character varying(20) not null
);

create table B(
  id serial primary key,
  a_id int references a(id) on delete cascade not null,
  name character varying(20) not null
);

create table C(
  id serial primary key,
  b_id int references b(id) on delete cascade not null,
  name character varying(20) not null
);

原查询（无法直接加锁） #

SELECT *
FROM A
  LEFT JOIN B ON A.id = B.a_id
  LEFT JOIN C ON B.id = C.b_id
WHERE A.id = 5;

报错的加锁尝试 #

SELECT *
FROM A
  LEFT JOIN B ON A.id = B.a_id
  LEFT JOIN C ON B.id = C.b_id
WHERE A.id = 5
FOR UPDATE; -- PostgreSQL中无法执行

可行替代方案 #

方案1：使用FOR UPDATE OF指定锁定表 #

通过明确指定要锁定的表（A、B、C），让PostgreSQL只尝试锁定实际存在的行，跳过左连接产生的NULL行对应的锁定操作，语句即可正常执行。

SELECT *
FROM A
LEFT JOIN B ON A.id = B.a_id
LEFT JOIN C ON B.id = C.b_id
WHERE A.id = 5
FOR UPDATE OF A, B, C;

该语句会锁定A表中id=5的行，以及所有关联存在的B、C行；对于左连接中不存在的B/C行，不会尝试锁定，因此可以顺利执行。同时，持有A行的排他锁会阻塞其他事务插入关联该A行的B记录，持有B行的锁会阻塞关联的C记录插入。

方案2：拆分查询优化锁定 #

如果需要更精准的锁定逻辑，可以拆分为主表锁定+关联表内连接锁定：

1. 先锁定A表目标行，确保阻塞关联B的插入：

SELECT * FROM A WHERE id = 5 FOR UPDATE;

2. 再通过内连接锁定所有关联的B、C行：

SELECT B.*, C.*
FROM B
JOIN C ON B.id = C.b_id
WHERE B.a_id = 5
FOR UPDATE;

这种方式仅需两次数据库请求，利用数据库的关联优化，效率高于应用层手动关联，同时满足锁定需求。

关键说明 #

• 针对A行的B插入操作，只要持有A行的FOR UPDATE锁即可阻塞，因为PostgreSQL外键约束在插入B时会检查A行状态，排他锁会拦截这类操作。
• 左连接中不存在的B/C行无实体记录，无需锁定，也无法锁定。

内容的提问来源于stack exchange，提问作者Vlad Kudoyar