读已提交隔离级下INSERT ON CONFLICT DO NOTHING与SELECT竞态条件问询

基础问题：读已提交级别下INSERT后SELECT是否总能命中？ #

只要中间没有并发事务删除目标行，同事务内INSERT ... ON CONFLICT DO NOTHING执行完成后，后续的SELECT一定能查到目标行。
原理是PostgreSQL读已提交隔离级别的快照规则：每个独立查询启动时都会拉取当前最新的已提交数据快照。INSERT语句执行时如果触发冲突不写入，说明该行已经被其他事务插入并提交，后续新发起的SELECT查询使用的新快照可以看到这行数据，这也是拆分查询能解决CTE方案竞态的核心原因：CTE属于单个查询，全程共用查询启动时的快照，INSERT阶段碰到的并发提交的冲突行，同一个CTE内的SELECT看不到，因此会返回空。 #

方案验证与遗漏点说明 #

你调整后的拆分查询方案完全可以解决「并发问题1」（同一行并发插入时CTE返回空），没有逻辑遗漏。 #

并发删除场景的处理方案 #

针对「INSERT和SELECT间隙行被其他事务删除」的问题，处理方式如下：

第二种拆分查询写法 #

在 fallback 的SELECT语句中必须加FOR KEY SHARE行锁，代码修改为：

SELECT id FROM object WHERE scope = $1 AND name = $2 FOR KEY SHARE;

加锁后，其他事务的删除操作会被阻塞直到你的事务提交，避免你拿到ID后还没插入关联数据，行就被删掉的问题。FOR KEY SHARE仅阻塞删除和唯一键修改操作，锁粒度比FOR UPDATE更轻，不会影响其他事务对同一行的查询、插入冲突判定操作，并发性能更好。

第三种嵌套子查询写法 #

同样需要加FOR KEY SHARE，修改子查询部分即可：

INSERT INTO object_member (object_id, key, value)
SELECT (SELECT id FROM object WHERE scope = $1 AND name = $2 FOR KEY SHARE), UNNEST($2::text[]), UNNEST($3::int[]);

虽然子查询嵌套在INSERT语句内部，本质还是查询操作，不加锁的话同样会出现查询到ID后、INSERT完成前行被删除的问题。 #

补充优化建议 #

如果业务允许object表的行删除后，对应的object_member数据也同步删除，可以给object_member表加外键约束关联object.id，开启级联删除，就算出现极端的行被删除的情况，也不会产生悬挂的无效关联数据。

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