PostgreSQL 11.16中FOR UPDATE锁引发事务冲突与重复键错误排查

你遇到的这个重复键错误，核心原因是**FOR UPDATE SKIP LOCKED锁定的是emails表的行，而非email_usage表的潜在冲突行**，加上PostgreSQL默认的READ COMMITTED隔离级别的特性，导致多个Worker进程可能同时通过NOT EXISTS检查，最终尝试插入同一(email, user_id)组合。

为什么会出现这个问题？ #

当多个Worker并行执行你的SQL时：

1. 每个Worker的SELECT语句会锁定emails中符合条件的行（并跳过已被锁定的行），但此时email_usage中还没有对应的记录；
2. 在READ COMMITTED隔离级别下，每个Worker的NOT EXISTS查询只能看到已提交的email_usage数据——其他Worker还未提交的INSERT操作对当前事务不可见；
3. 因此多个Worker可能同时通过NOT EXISTS检查，都认为这个(email, user_id)组合未被使用，随后执行INSERT，当它们先后提交时，第二个及之后的事务就会触发唯一键冲突错误。

而改用SERIALIZABLE隔离级别时，PostgreSQL会检测到这些事务之间的读写依赖（都读取了email_usage的状态并尝试写入），因此抛出SerializationFailure错误，这是该级别下的预期行为，但会导致大量重试，不适合高并发的工作队列场景。

可行的解决方案 #

方案1：使用ON CONFLICT DO NOTHING处理冲突

最直接的修复方式是给INSERT语句加上ON CONFLICT子句，让数据库自动忽略重复的插入操作，同时通过RETURNING判断是否成功获取到任务：

BEGIN;

INSERT INTO email_usage (email, user_id)
SELECT email, some_user_id FROM emails ea
    WHERE ea.domain = 'some_domain.com'
    AND NOT EXISTS(
        SELECT 1 FROM email_usage eu
        WHERE ea.email = eu.email
        AND eu.user_id = some_user_id
    )
FOR UPDATE SKIP LOCKED
LIMIT 1
ON CONFLICT (email, user_id) DO NOTHING
RETURNING email, user_id;

COMMIT;

• 如果RETURNING返回了一行数据，说明当前Worker成功获取到了这个邮件任务，可以开始处理；
• 如果没有返回任何行，说明该(email, user_id)组合已经被其他Worker抢先插入，当前Worker只需重新执行一次这个SQL即可。

这种方式不需要修改隔离级别，在READ COMMITTED下就能稳定工作，且性能开销很小。

方案2：先锁定email_usage的潜在行（可选）

如果你希望从根源上避免竞争，可以先尝试锁定email_usage中不存在的行（利用PostgreSQL的SELECT ... FOR UPDATE可以锁定不存在的行的特性，通过一个虚表关联）：

BEGIN;

-- 先尝试锁定潜在的(email, user_id)行，避免其他Worker同时操作
SELECT 1 FROM (SELECT some_user_id AS user_id, ea.email FROM emails ea
    WHERE ea.domain = 'some_domain.com'
    AND NOT EXISTS(
        SELECT 1 FROM email_usage eu
        WHERE ea.email = eu.email
        AND eu.user_id = some_user_id
    )
) AS candidate
LEFT JOIN email_usage eu ON candidate.email = eu.email AND candidate.user_id = eu.user_id
WHERE eu.email IS NULL
FOR UPDATE SKIP LOCKED
LIMIT 1;

-- 再次检查并插入（防止在锁定期间其他Worker已插入）
INSERT INTO email_usage (email, user_id)
SELECT email, some_user_id FROM emails ea
    WHERE ea.domain = 'some_domain.com'
    AND NOT EXISTS(
        SELECT 1 FROM email_usage eu
        WHERE ea.email = eu.email
        AND eu.user_id = some_user_id
    )
LIMIT 1
ON CONFLICT DO NOTHING
RETURNING email, user_id;

COMMIT;

不过这个方案比方案1复杂，且实际效果和方案1差异不大，通常优先选择方案1。

总结 #

你的核心问题是锁定的对象和冲突检查的对象不一致，通过ON CONFLICT DO NOTHING可以优雅地处理并发插入冲突，同时保持工作队列的高并发性能。不需要依赖SERIALIZABLE隔离级别，避免了不必要的重试开销。

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