Race-Condition: How a Single SQL Line Eliminated 100 Lines of Retry and Lock Code

Allan Bontempo — Wed, 27 May 2026 01:06:58 +0000

hat "rare" bug that looks intermittent but is actually deterministic. It is just waiting for your pods to scale.

I recently dealt with a textbook concurrency issue in a Java application running on multiple parallel instances, and the solution turned out to be much simpler than the workarounds we had piled on top of it. This is a walkthrough of the problem, the failed attempts, and the final fix, with code and diagrams so you can apply the same pattern.

The Setup

The application stack:

. Java with Quarkus and Hibernate (Panache)
. Oracle as the relational database
. Deployed on Kubernetes with 4 parallel pods consuming from a JMS queue
. Each message triggers the creation of a record inside a parent entity, with a sequential number that resets per parent

To keep things generic, think of it as "items inside an order":

. Each order can have multiple items
. Each item has a sequential number within the order (1, 2, 3, ...)
. The numbering restarts at 1 for every new order
. The primary key is composite: (order_id, item_number)

The application had been running this way for years with a single instance. When the deployment scaled to four pods, primary key violations started showing up in production, intermittent and hard to reproduce.

The Root Cause: SELECT MAX + 1

Here is the original code (simplified):

public Long nextItemNumber(Long orderId) {
    return em.createQuery(
            "select max(i.itemNumber) from Item i where i.orderId = :orderId",
            Long.class)
        .setParameter("orderId", orderId)
        .getSingleResultOrNull()
        .map(last -> last + 1L)
        .orElse(1L);
}

A textbook SELECT MAX + 1 pattern. The problem: SELECT acquires no lock. Two pods can query at exactly the same moment, get the same value, and both try to insert the same number.

This worked for years with a single instance because there was no concurrency. The bug was always there. It just never had a chance to manifest.

First Attempt: Pessimistic Lock + Retry

The first fix tried to add coordination between the pods using a pessimistic lock on the parent row, plus a retry on the duplicate exception.

@Retry(maxRetries = 4, delay = 5, retryOn = DuplicateItemException.class,
       delayUnit = ChronoUnit.SECONDS)
@Transactional
public Item createItem(Long orderId, ItemPayload payload) {
    // 1) Lock the parent row
    em.createNativeQuery("""
        SELECT * FROM orders
        WHERE id = :id
        FOR UPDATE WAIT 5
        """)
        .setParameter("id", orderId)
        .getSingleResult();

    // 2) Get next number
    Long nextNumber = nextItemNumber(orderId);

    // 3) Insert the item
    try {
        return persist(Item.builder()
            .orderId(orderId)
            .itemNumber(nextNumber)
            .payload(payload)
            .build());
    } catch (PersistenceException e) {
        if (isUniqueViolation(e)) {
            throw new DuplicateItemException(e);
        }
        throw e;
    }
}

It reduced the frequency of failures, but did not eliminate them. Under high concurrency:

. WAIT 5 would time out, throwing LockTimeoutException
. Retries piled up, with 5-second delays adding latency
. After exhausting all retries, the operation still failed
. Code complexity increased: a custom exception, retry annotation, unique-violation detection logic, error handling

The fundamental problem was still there: SELECT MAX + 1 is a "read then write" pattern, and the lock was only mitigating the symptom, not removing the race.

The Solution: Atomic UPDATE ... RETURNING

The final solution was to add a counter column on the parent table and use an atomic UPDATE ... RETURNING to generate the next number in a single operation.

Schema change

ALTER TABLE orders
ADD last_item_number NUMBER(4,0) DEFAULT 0 NOT NULL;

-- Backfill for existing rows
UPDATE orders o
SET o.last_item_number = (
    SELECT NVL(MAX(i.item_number), 0)
    FROM items i
    WHERE i.order_id = o.id
);
COMMIT;

Semantics:

. The column stores the last number used (not "the next to use")
. New orders start at 0 (no items created)
. Each UPDATE increments and returns the new value, which is the number to assign

Code change

@Transactional
public Long nextItemNumber(Long orderId) {
    return ((Number) em.createNativeQuery("""
            UPDATE orders
            SET last_item_number = last_item_number + 1
            WHERE id = :orderId
            RETURNING last_item_number INTO :next
            """)
        .setParameter("orderId", orderId)
        .getSingleResult()).longValue();
}

That is the whole thing. Single statement, atomic by design.

Why It Works: The Mechanics of UPDATE Locking

There are three properties that make this pattern bulletproof:

1. UPDATE acquires an automatic X-lock

When you UPDATE a row in Oracle (and most relational databases), the engine automatically acquires an exclusive lock on that row. Two concurrent transactions targeting the same row serialize by design, without any explicit FOR UPDATE.

2. The lock is re-evaluated after waiting

When Pod B unblocks (because Pod A committed), Oracle re-evaluates SET counter = counter + 1 using the committed value. So Pod B sees 4 (committed by A) and produces 5. It does not use the value it saw before being blocked.

3. RETURNING is atomic with the update

The value returned is the post-update value, computed in the same statement. There is no window between "increment" and "read the new value" where another transaction could interfere.

Compare this with the SELECT MAX + 1 pattern, which separates the read and the write into two steps with no protection between them.

	`SELECT MAX + 1`	`UPDATE counter + 1 RETURNING`
Acquires lock	No	Yes, automatic
Serializes?	No	Yes, on the row
Two pods can get same value	Yes	No
Detects conflict	At INSERT (constraint violation)	Never (no conflict possible)

Bonus Layer: Eliminating Redis Dependency

The same application had another counter being generated through Redis (INCR on a key formatted as "{order_id}/{item_number}"). This was used for a secondary sequential number inside each item.

That counter had similar problems:

. No transactional atomicity with the database: if the INSERT rolled back, the Redis INCR did not roll back. Result: gaps in the sequence (burned numbers).
. Manual reconciliation logic: a periodic check tried to detect divergence between Redis and the database and fix it. The reconciliation had bugs and missed certain edge cases.
. Critical dependency in the hot path: if Redis was unavailable, the consumer was stuck even when the database was healthy.

The same counter pattern applied to the secondary table eliminated all of that:

ALTER TABLE items
ADD last_subitem_number NUMBER(3,0) DEFAULT 0 NOT NULL;

@Transactional
public Integer nextSubItemNumber(Long orderId, Long itemNumber) {
    return ((Number) em.createNativeQuery("""
            UPDATE items
            SET last_subitem_number = last_subitem_number + 1
            WHERE order_id = :orderId AND item_number = :itemNumber
            RETURNING last_subitem_number INTO :next
            """)
        .setParameter("orderId", orderId)
        .setParameter("itemNumber", itemNumber)
        .getSingleResult()).intValue();
}

Redis is now used only for caching reference data, not for generating unique IDs in the transactional path.

What Got Removed

After applying the pattern in both layers, the following code disappeared:

. DuplicateItemException class
. @Retry annotation on the creation method
. Custom isUniqueViolation detection logic
. Try/catch wrapping the INSERT to convert exceptions
. Redis dependency for sequential generation (still used for caching)
. Reconciliation method that compared Redis state with the database (a routine with edge cases that needed maintenance)
. Configuration properties related to Redis TTL for these counters
. Approximately 100 lines of error handling and ceremony

The code that remained is more declarative and easier to reason about. The race condition is gone, not by mitigation but by design.

When NOT to Use This Pattern

This is not a silver bullet. The pattern works well when:

. The number you are generating belongs to a clear "parent" entity that already exists in the database
. The numbering is scoped to that parent (resets per parent)
. The transaction boundary fits naturally around the increment and the insert
. You can afford serialization on the parent row (concurrent creates within the same parent serialize)

It does not fit when:

. The ID needs to be generated before any database row exists (consider snowflake, UUID v7, or external sequence)
. You need globally unique IDs across all entities (use a sequence, not a counter)
. The serialization overhead on the parent row is unacceptable for your workload (rare, but possible in extreme scenarios where you would need application-level sharding anyway)

For the common case of "sequential numbers inside a parent entity in a multi-instance application," the counter column with atomic UPDATE is hard to beat.

Takeaways

A lot of race conditions in legacy systems trace back to SELECT MAX + 1 inherited from single-instance days. The cost of patching with explicit locks and retries is high and never fully resolves the underlying issue.

A well-placed counter column with UPDATE ... RETURNING is:

. Simpler: one SQL statement replaces a multi-step routine
. Safer: serialization is guaranteed by the database, not by application logic
. Faster: no retry latency, no lock timeout cascades
. More transparent: the intent is clear in the code

Relational databases were designed for this pattern decades ago. Before reaching for distributed locks, queues, or eventual consistency, it is worth checking whether the database already has the tools to solve the problem natively. In many cases, it does.

References

. Oracle documentation on UPDATE ... RETURNING INTO: docs.oracle.com
. Oracle row-level locking behavior: Concepts Guide
. Martin Kleppmann on the limits of distributed locks (Redlock): martin.kleppmann.com
. MicroProfile Fault Tolerance specification: microprofile.io

If you have dealt with similar concurrency issues in your projects, what was your solution? Did you go with a database-native approach or a distributed lock?

DEV Community: Allan Bontempo