Spring JPA多线程并发执行Player新增/更新操作时唯一约束冲突问题排查及解决方案咨询

Let's start by breaking down why your current approach isn't working, then walk through the most effective fixes for this concurrency problem.

Why Your Current Setup Fails #

You’re spot-on about the root cause: when two threads attempt to upsert the same non-existent Player simultaneously, both pass the findById check (since the record doesn’t exist yet), then both try to INSERT. The second thread hits the primary key constraint violation because the first already inserted the record.

Here’s why your existing fixes don’t resolve this:

• @Lock(LockModeType.PESSIMISTIC_WRITE) on findById only locks an existing record. If the record isn’t there, there’s nothing to lock—so both threads proceed unblocked.
• Isolation.SERIALIZABLE prevents phantom reads and enforces sequential transaction execution, but it doesn’t stop two transactions from inserting the same primary key. Each transaction still sees an empty table for that ID, so both attempt the insert, leading to the error.

Recommended Solutions #

We’ll cover options from the most efficient/reliable to the least ideal:

1. Use Database-Native UPSERT (Best Option) #

The cleanest and most performant fix is to let the database handle the upsert atomically with its native syntax. This eliminates application-level concurrency issues because the database ensures the operation is either an insert or update in a single atomic step.

For H2 (which you’re using), use the MERGE statement. Update your PlayerRepository with a native query:

public interface PlayerRepository extends JpaRepository<Player, String> {
    @Modifying
    @Transactional
    @Query(value = "MERGE INTO PLAYER (ID, NAME) KEY(ID) VALUES (:playerId, :playerName)", nativeQuery = true)
    void upsertPlayer(@Param("playerId") String playerId, @Param("playerName") String playerName);

    // Keep your existing findById to fetch the updated record
    Optional<Player> findById(String playerId);
}

Then adjust your service method:

@Transactional
public Player addOrUpdatePlayer(String playerId, String playerName) {
    playerRepository.upsertPlayer(playerId, playerName);
    // Fetch the latest state after the atomic upsert
    return playerRepository.findById(playerId).orElseThrow();
}

This works because MERGE is atomic: the database checks if the ID exists, updates if it does, inserts if it doesn’t—no concurrency conflicts possible.

2. Distributed Locking (Great for Multi-Instance Apps) #

If you need cross-database compatibility or can’t use native upserts, use a distributed lock to ensure only one thread processes a specific playerId at a time. This is critical if your app runs on multiple servers (local synchronized blocks won’t work across instances).

For example, using Redis with Redisson:

@Autowired
private RedissonClient redissonClient;

@Autowired
private PlayerRepository playerRepository;

@Transactional
public Player addOrUpdatePlayer(String playerId, String playerName) {
    // Lock on the specific player ID to avoid blocking all upserts
    RLock lock = redissonClient.getLock("player:upsert:" + playerId);
    try {
        lock.lock(); // Blocks until the lock is acquired
        // Safe to run the original upsert logic now
        Optional<Player> playerOptional = playerRepository.findById(playerId);
        if (playerOptional.isPresent()) {
            Player player = playerOptional.get();
            player.setName(playerName);
            return playerRepository.save(player);
        } else {
            Player newPlayer = Player.builder()
                    .id(playerId)
                    .name(playerName)
                    .build();
            return playerRepository.save(newPlayer);
        }
    } finally {
        lock.unlock(); // Always release the lock to avoid deadlocks
    }
}

This lets different playerId upserts run in parallel while serializing requests for the same ID.

3. Table-Level Locking (Last Resort) #

Locking the entire Player table is possible, but it’s strongly discouraged because it cripples performance—all upserts, regardless of ID, will be serialized.

If you must use this approach, execute a native table lock query within your transaction:

@Autowired
private EntityManager entityManager;

@Transactional(isolation = Isolation.SERIALIZABLE)
public Player addOrUpdatePlayer(String playerId, String playerName) {
    // Lock the entire table (H2 syntax)
    entityManager.createNativeQuery("LOCK TABLE PLAYER WRITE").executeUpdate();
    
    // Proceed with your original upsert logic
    Optional<Player> playerOptional = playerRepository.findById(playerId);
    if (playerOptional.isPresent()) {
        Player player = playerOptional.get();
        player.setName(playerName);
        return playerRepository.save(player);
    } else {
        Player newPlayer = Player.builder()
                .id(playerId)
                .name(playerName)
                .build();
        return playerRepository.save(newPlayer);
    }
}

Only use this if all other options are unavailable—it will bottleneck your application’s write throughput for the Player table.

Final Notes #

Stick with the database-native UPSERT whenever possible: it’s the most efficient, reliable, and simplest solution. Distributed locking is a solid alternative for multi-instance setups, while table-level locks should be avoided unless absolutely necessary.

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