REPEATABLE_READ隔离级+PESSIMISTIC_WRITE锁下批量插入后行锁异常问询

Let's break down your problem and work through the root causes and fixes step by step.

Core Scenario Recap #

• You're using PESSIMISTIC_WRITE locks to prevent concurrent access to the same database row across multiple instances/users
• Your transactions use the default REPEATABLE_READ isolation level
• The exception occurs when: the table is empty → an async batch insert (in a separate transaction) populates new rows → after the insert commits, users try to read and lock the new rows, triggering an error

Why This Happens #

The root cause ties directly to the snapshot behavior of the REPEATABLE_READ isolation level:
When a transaction starts under REPEATABLE_READ, it creates a snapshot of the database state. For the entire lifecycle of that transaction, it will only see data that existed at snapshot creation time—even if other transactions commit new data later.

Here's a typical problematic sequence:

1. A user's read-lock transaction starts (when the table is still empty)
2. The async batch insert runs, commits, and successfully adds new rows
3. The user's transaction tries to read and lock the target row, but its snapshot still shows an empty table. Since the row doesn't exist in the snapshot, the lock operation fails, throwing an exception.

Additionally, PESSIMISTIC_WRITE locks rely on the row existing within the current transaction's visibility scope. Even if the row exists in the actual database, if it's not visible to the transaction's snapshot, the lock can't be applied.

Practical Solutions #

Choose the option that best fits your business requirements:

1. Switch to READ_COMMITTED Isolation Level

If your business doesn't require strict repeatable reads, adjust the isolation level for the user's read-lock transactions to READ_COMMITTED. This level lets transactions see the latest committed data on each query, so they'll pick up the newly inserted rows and lock them successfully.

Example (Spring JPA):

@Transactional(isolation = Isolation.READ_COMMITTED)
public void processRowWithLock(Long rowId) {
    // Will see the latest committed rows from the async insert
    TargetEntity entity = entityManager.find(TargetEntity.class, rowId, LockModeType.PESSIMISTIC_WRITE);
    // Execute business logic
}

2. Force Snapshot Refresh (For REPEATABLE_READ Scenarios)

If you must keep REPEATABLE_READ, you can explicitly refresh the transaction's snapshot when a row isn't found initially, forcing it to see the latest committed data.

Example:

@Transactional(isolation = Isolation.REPEATABLE_READ)
public void processRowWithLock(Long rowId) {
    TargetEntity entity = entityManager.find(TargetEntity.class, rowId);
    if (entity == null) {
        // Clear the transaction's cached snapshot to re-read from the database
        entityManager.clear();
        entity = entityManager.find(TargetEntity.class, rowId, LockModeType.PESSIMISTIC_WRITE);
    }
    // Execute business logic
}

Note: Frequent use of clear() can impact performance, so evaluate this against your traffic patterns.

3. Ensure User Transactions Start After Batch Insert Completes

If your workflow allows, wait for the async batch insert transaction to fully commit before starting the user's read-lock transaction. Use async task callbacks or blocking waits to sync the execution:

// Async batch insert task
CompletableFuture<Void> batchInsertTask = CompletableFuture.runAsync(() -> {
    batchInsertService.populateEmptyTable();
});

// Wait for insert to finish before triggering user's lock logic
batchInsertTask.join();
userService.processRowWithLock(targetRowId);

4. Use Database-Specific Lock Syntax

If your database supports it (MySQL 8.0+, PostgreSQL, etc.), use SELECT ... FOR UPDATE NOWAIT or SKIP LOCKED to handle lock failures gracefully and explicitly check for missing rows:

SELECT * FROM target_table WHERE id = ? FOR UPDATE NOWAIT;

In ORMs, implement this via native SQL queries and catch lock-specific exceptions (like MySQL's LockWaitTimeoutException) to handle edge cases.

Additional Notes #

• Verify async insert transactions are fully committed: Async tasks can have delayed commits due to thread pool behavior or transaction propagation rules—double-check that the insert is finalized before user transactions run.
• Avoid long transactions: Long-running REPEATABLE_READ transactions are more prone to snapshot visibility issues; keep user transactions as short as possible.

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