PostgreSQL 14.2中UPSERT事务竞态条件的优雅处理方案咨询

你遇到的是PostgreSQL ON CONFLICT UPSERT在高并发下的典型竞态问题：两个事务同时尝试插入同一唯一键（user_id+created_on）的记录，第一个提交成功，第二个因唯一约束冲突失败。下面结合jOOQ和Spring Boot，给出几个更优雅的解决思路：

一、优先用重试机制（官方推荐） #

PostgreSQL的ON CONFLICT本身是原子操作，但极端并发场景下，偶尔会因事务提交时序问题抛出23505（唯一约束冲突）错误。此时最简单有效的方式是给UPSERT加重试逻辑，让失败的事务稍作等待后重新执行。

示例代码：

@Transactional
public void upsertUser(User user) {
    int retryCount = 3;
    while (retryCount > 0) {
        try {
            dslContext.insertInto(USERS)
                .set(USERS.USER_ID, user.getUserId())
                .set(USERS.CREATED_ON, user.getCreatedOn())
                .set(USERS.COLUMN1, user.getColumn1())
                // 其他字段赋值
                .onConflict(USERS.USER_ID, USERS.CREATED_ON)
                .doUpdate()
                .set(USERS.COLUMN1, user.getColumn1())
                // 其他更新字段
                .execute();
            return;
        } catch (DataAccessException e) {
            // 捕获PostgreSQL唯一约束冲突错误
            if (e.getRootCause() instanceof PSQLException 
                && "23505".equals(((PSQLException) e.getRootCause()).getSQLState())) {
                retryCount--;
                if (retryCount == 0) throw e;
                // 短暂等待避免立即重试再次冲突
                try { Thread.sleep(100); } catch (InterruptedException ie) { Thread.currentThread().interrupt(); }
            } else {
                throw e;
            }
        }
    }
}

这个方案优势是实现简单，不需要额外锁机制，对正常流程的性能几乎无影响，只有冲突时才触发重试，适合大多数场景。

二、用事务级Advisory Lock串行化操作 #

如果冲突频率极高，重试也无法缓解，可以用PostgreSQL的事务级advisory lock，让同一唯一键的UPSERT操作串行执行，从根源避免冲突。

核心思路是基于user_id和created_on生成唯一锁ID，在执行UPSERT前获取锁，事务结束后自动释放：

@Transactional
public void upsertUser(User user) {
    // 生成唯一锁ID（确保同一user_id+created_on对应同一锁）
    long lockId = Math.abs(Objects.hash(user.getUserId(), user.getCreatedOn()));
    
    // 获取事务级advisory锁，会自动等待直到拿到锁
    dslContext.select(field("pg_advisory_xact_lock(?)", Boolean.class, lockId))
        .fetchOne();
    
    // 执行UPSERT，此时不会有并发冲突
    dslContext.insertInto(USERS)
        .set(USERS.USER_ID, user.getUserId())
        .set(USERS.CREATED_ON, user.getCreatedOn())
        .set(...)
        .onConflict(USERS.USER_ID, USERS.CREATED_ON)
        .doUpdate()
        .set(...)
        .execute();
}

pg_advisory_xact_lock会绑定当前事务，事务提交/回滚时自动释放锁，无需手动管理，比手动释放锁更安全。缺点是会降低并发度，同一唯一键的操作必须串行，适合冲突极频繁的场景。

三、预锁定现有行（补充方案） #

如果大部分场景是更新而非插入，可以先查询并锁定现有行，不存在再插入。但注意：如果行不存在，并发插入仍可能触发冲突，所以最好结合重试：

@Transactional
public void upsertUser(User user) {
    int retryCount = 3;
    while (retryCount > 0) {
        try {
            // 尝试锁定现有行
            UserRecord existing = dslContext.selectFrom(USERS)
                .where(USERS.USER_ID.eq(user.getUserId())
                    .and(USERS.CREATED_ON.eq(user.getCreatedOn())))
                .forUpdate()
                .fetchOne();
            
            if (existing == null) {
                // 无现有行则插入
                dslContext.insertInto(USERS)
                    .set(USERS.USER_ID, user.getUserId())
                    .set(USERS.CREATED_ON, user.getCreatedOn())
                    .set(...)
                    .execute();
            } else {
                // 有现有行则更新
                dslContext.update(USERS)
                    .set(USERS.COLUMN1, user.getColumn1())
                    .set(...)
                    .where(USERS.USER_ID.eq(user.getUserId())
                        .and(USERS.CREATED_ON.eq(user.getCreatedOn())))
                    .execute();
            }
            return;
        } catch (DataAccessException e) {
            if (e.getRootCause() instanceof PSQLException 
                && "23505".equals(((PSQLException) e.getRootCause()).getSQLState())) {
                retryCount--;
                if (retryCount == 0) throw e;
                try { Thread.sleep(100); } catch (InterruptedException ie) { Thread.currentThread().interrupt(); }
            } else {
                throw e;
            }
        }
    }
}

这个方案适合更新占比高的场景，能减少UPSERT的冲突概率，但插入占比高时效果不如重试或advisory lock。

四、额外优化建议 #

• 保持默认的READ COMMITTED隔离级别，这是PostgreSQL对ON CONFLICT支持最稳定的级别。
• 简化UPSERT语句，避免在DO UPDATE中使用复杂子查询或函数，减少锁持有时间。
• 监控pg_stat_user_errors中的unique_violation计数，根据冲突频率选择合适方案：低频率用重试，高频率用advisory lock。

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