Postgres 9.6.3中INSERT WHERE NOT EXISTS竞态条件消除方案咨询

首先得明确你遇到的问题根源：你的原语句里，WHERE NOT EXISTS子查询的FOR UPDATE只会锁定已经存在的符合条件的行，但当并发请求同时进来时，一开始没有任何符合条件的行（6秒内的记录），所以多个事务都能通过NOT EXISTS的检查，然后同时执行INSERT，最终导致重复插入——这就是典型的“读-改-写”竞态。

下面给你几个可行的解决方案，按推荐程度排序：

1. 使用Advisory Lock（建议锁） #

这是最直接且性能影响较小的方案，核心思路是针对每个key加一个专属的锁，确保同一时间只有一个事务能检查并插入该key的记录。

具体实现步骤：

• 先用hashtext()函数把字符串key转换成整数（因为PostgreSQL的advisory锁只接受整数参数）
• 在事务中先获取锁，再执行检查和插入操作，事务结束后锁会自动释放（也可以手动释放）

示例代码：

BEGIN;
-- 获取针对当前key的advisory锁
SELECT pg_advisory_lock(hashtext('test'));

-- 再次检查是否有6秒内的记录（这时候只有当前事务能执行这个检查）
IF NOT EXISTS (
    SELECT 1 FROM scheduled_event_log 
    WHERE "key" = 'test' 
    AND age(now() AT TIME ZONE 'utc', timestamp_utc) < '6s'
) THEN
    INSERT INTO scheduled_event_log ("key") VALUES ('test');
END IF;

COMMIT; -- 事务提交后锁自动释放

这个方案的好处是：

• 只针对特定key加锁，不会影响其他key的并发操作
• 实现简单，不需要修改表结构

2. 切换到SERIALIZABLE隔离级别 #

PostgreSQL的SERIALIZABLE隔离级别会自动检测并发事务中的竞态条件，当发现可能导致不一致的情况时，会回滚其中一个事务并抛出serialization_failure错误。

你需要做的是：

1. 将事务的隔离级别设置为SERIALIZABLE
2. 在应用层捕获serialization_failure错误，并重试事务

示例代码：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;

INSERT INTO scheduled_event_log ("key") 
SELECT 'test' 
WHERE NOT EXISTS( 
    SELECT AGE(now() at time zone 'utc', timestamp_utc) 
    FROM scheduled_event_log 
    WHERE "key" = 'test' 
    AND age(now() at time zone 'utc', timestamp_utc) < '6s'
);

COMMIT;

注意：这个方案需要应用处理重试逻辑，而且SERIALIZABLE隔离级别会带来一定的性能开销，适合并发量不是特别高的场景。

3. 优化原语句的锁策略（不推荐） #

你可能会想能不能通过调整原语句的锁来解决，但原语句的问题在于没有行可以锁定时的竞态，所以可以尝试先“占位”一个行？比如用SELECT ... FOR UPDATE查询所有该key的行（不管时间），但如果该key从来没有插入过，还是没有行可以锁定，所以这个方案只能解决“已有旧记录但6秒内无新记录”的竞态，无法解决首次插入的竞态，因此不推荐。

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