使用INSERT...SELECT...WHERE EXISTS插入时，并发删除引用行致FK校验失效问题

我之前也碰到过一模一样的问题，核心是PostgreSQL的事务隔离机制和外键约束检查的时机差异导致的。先把完整的问题场景还原清楚，再一步步拆解解决方案：

完整问题重现 #

Session 1 #

-- 创建测试表
CREATE TABLE a (x int PRIMARY KEY);
CREATE TABLE b (x int, FOREIGN KEY (x) REFERENCES a);

-- 插入初始数据
INSERT INTO a VALUES (1);

-- 开启事务
BEGIN;

-- 执行批量插入，试图通过EXISTS过滤无效外键
INSERT INTO b (x)
SELECT target_x
FROM (SELECT 1 AS target_x) AS tmp
WHERE EXISTS (SELECT 1 FROM a WHERE x = tmp.target_x);

Session 2（在Session 1执行INSERT后、提交前运行） #

BEGIN;
-- 删除被引用表的行
DELETE FROM a WHERE x = 1;
COMMIT;

回到Session 1提交事务 #

COMMIT;

这时候你会收到类似ERROR: insert or update on table "b" violates foreign key constraint "b_x_fkey"的错误——明明已经用WHERE EXISTS做了存在性检查，却还是触发了外键约束。

问题原因分析 #

这是PostgreSQL**读已提交（Read Committed）**默认隔离级别的特性导致的：

1. WHERE EXISTS子句中的查询是快照读，它只能看到事务开始前或其他事务已提交的数据，所以Session 1的EXISTS检查会认为行a.x=1仍然存在。
2. 而PostgreSQL的外键约束检查是当前读，在事务提交时（或语句执行的关键节点）会读取最新的已提交数据，这时候Session 2的删除已经生效，所以外键约束验证失败。

简单说：EXISTS检查和最终的外键验证之间存在时间窗口，并发事务可以在这个窗口内修改被引用的数据，导致检查结果和实际数据状态不一致。

解决方案 #

方案1：使用FOR UPDATE锁定被引用行 #

在EXISTS的子查询中添加FOR UPDATE，这样在检查行存在的同时，会锁定该行，阻止其他事务删除或修改，直到当前事务结束：

INSERT INTO b (x)
SELECT target_x
FROM (SELECT 1 AS target_x) AS tmp
WHERE EXISTS (SELECT 1 FROM a WHERE x = tmp.target_x FOR UPDATE);

• 优点：从根源上避免了并发修改，确保检查和插入的一致性，不需要重试。
• 缺点：会增加锁竞争，如果被引用表的行被频繁修改，可能会影响并发性能。

方案2：提升事务隔离级别到可重复读（Repeatable Read） #

将Session 1的事务隔离级别改为可重复读，这样整个事务内的所有查询都会使用同一个快照，不会看到其他事务在事务开始后提交的修改：

-- 开启事务时指定隔离级别
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

INSERT INTO b (x)
SELECT target_x
FROM (SELECT 1 AS target_x) AS tmp
WHERE EXISTS (SELECT 1 FROM a WHERE x = tmp.target_x);

COMMIT;

• 优点：不需要加锁，对并发性能影响较小。
• 缺点：如果其他事务在Session 1的事务期间修改了被引用行，Session 1提交时会触发ERROR: could not serialize access due to concurrent update，需要在应用层处理重试逻辑。

方案3：捕获外键异常并跳过（适合小批量插入） #

如果插入的批量数据量不大，可以用PL/pgSQL编写循环，逐个插入并捕获外键约束异常，跳过无效行：

DO $$
DECLARE
    rec record;
BEGIN
    FOR rec IN SELECT 1 AS target_x LOOP
        BEGIN
            INSERT INTO b (x) VALUES (rec.target_x);
        EXCEPTION WHEN foreign_key_violation THEN
            -- 跳过外键无效的行，可添加日志记录
            CONTINUE;
        END;
    END LOOP;
END $$;

• 优点：不需要锁或修改隔离级别，逻辑简单。
• 缺点：逐行插入效率低，不适合大规模批量操作。

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