Skip to content
Back to blog
7 min read

Preventing Overselling: Optimistic Locking and Idempotent Sales

Spring BootJPAConcurrencyPostgreSQL

Two cashiers in the same clothing store, one item left, both scan it at the same second. Both sales succeed. Stock is now -1, and someone has to explain to a customer why their purchase doesn't exist.

This is the bug I spent the most time on in Raaqib, and it's the kind that never reproduces locally. One user, one request at a time — it works perfectly right up until it doesn't.

The code that looks correct

@Transactional
public void recordSale(UUID stockItemId, int quantity) {
    StockItem item = stockRepository.findById(stockItemId).orElseThrow();

    if (item.getQuantity() < quantity) {
        throw new InsufficientStockException();
    }

    item.setQuantity(item.getQuantity() - quantity);
    stockRepository.save(item);
}

There's a check, there's a transaction, and it's still wrong. Interleave two calls:

  1. Thread A reads quantity = 1
  2. Thread B reads quantity = 1
  3. A checks 1 >= 1 and writes 0
  4. B checks 1 >= 1 — against the value it read — and writes 0

Two items sold, one item existed. This is a lost update, and it's the classic read-modify-write race.

@Transactional doesn't prevent it, which is the part that trips people up. The default isolation level in PostgreSQL is READ COMMITTED, which promises you won't read uncommitted data. It promises nothing about the value you read staying put while you think about it. The check and the write are two separate statements with a gap between them, and the gap is where the money goes.

Option 1: optimistic locking

Add a version column and let the database catch the conflict:

@Entity
public class StockItem {

    @Id
    private UUID id;

    private int quantity;

    @Version
    private Long version;
}

Now Hibernate writes UPDATE stock_item SET quantity = ?, version = 4 WHERE id = ? AND version = 3. If another transaction already bumped the version, zero rows match, and Hibernate throws OptimisticLockException (surfaced by Spring as ObjectOptimisticLockingFailureException). The second writer loses instead of silently overwriting.

The catch is what you do with the failure, and there's a trap:

@Transactional
public void recordSale(...) {
    try {
        // ...
    } catch (ObjectOptimisticLockingFailureException e) {
        recordSale(...); // wrong, twice over
    }
}

Two problems. The optimistic lock failure is usually detected at flush, which happens on commit — so by the time you can catch it, the transaction is already marked rollback-only and anything you do inside it is doomed. And a self-invocation doesn't go through the Spring proxy anyway, so it wouldn't start a new transaction even if the first one were healthy.

The retry has to sit outside the transaction boundary:

@Service
public class SaleFacade {

    private final SaleService saleService; // separate bean, @Transactional lives there

    @Retryable(
        retryFor = ObjectOptimisticLockingFailureException.class,
        maxAttempts = 3,
        backoff = @Backoff(delay = 50, multiplier = 2, random = true)
    )
    public SaleResult record(SaleCommand command) {
        return saleService.record(command); // fresh transaction per attempt
    }
}

Each attempt gets a new transaction and re-reads the row, so it sees the updated quantity and either succeeds or legitimately fails on insufficient stock. Jittered backoff matters — without it, retries from concurrent requests re-collide in lockstep.

Option 2: pessimistic locking

Lock the row on read and make the second transaction wait:

@Lock(LockModeType.PESSIMISTIC_WRITE)
@Query("select s from StockItem s where s.id = :id")
Optional<StockItem> findByIdForUpdate(@Param("id") UUID id);

That's SELECT ... FOR UPDATE. It's correct and it's simple, and it costs you: a held database lock for the length of the transaction, contention on hot rows, and deadlock risk if two transactions grab several rows in different orders. If you go this route, always lock in a deterministic order — sort by ID before locking a basket of items.

Option 3: let the database do the check

For a decrement, the cleanest answer is neither. Push the condition into the UPDATE itself so the read and the write become one atomic statement:

@Modifying
@Query("""
    update StockItem s
       set s.quantity = s.quantity - :qty
     where s.id = :id
       and s.quantity >= :qty
    """)
int decrementIfAvailable(@Param("id") UUID id, @Param("qty") int qty);

The return value is the number of rows updated. 1 means the stock was there and is now reserved. 0 means it wasn't — no exception, no retry, no lock held between statements. The database evaluates the predicate and the write under the same row lock, and the gap that caused the bug simply doesn't exist.

if (stockRepository.decrementIfAvailable(id, qty) == 0) {
    throw new InsufficientStockException(id);
}

I use this for the stock decrement itself and keep @Version on the entity for the multi-field edits — price changes, product details — where a conditional update doesn't express the intent and last-write-wins is genuinely wrong.

One caveat: a @Modifying query writes straight to the database and doesn't update entities already in the persistence context. If you touch the entity afterwards in the same transaction, you're looking at a stale copy. @Modifying(clearAutomatically = true, flushAutomatically = true) handles it, or just don't re-read the entity in that transaction.

The other half: retries from the client

Concurrency isn't the only source of duplicates. A cashier on a bad connection taps "confirm", sees nothing happen, and taps again. The first request landed. Now there are two sales.

The fix is idempotency, and the enforcement belongs in the database, not in an if. The client generates a key per sale attempt and reuses it on retry:

@Column(nullable = false, unique = true)
private UUID idempotencyKey;

try {
    return saleRepository.saveAndFlush(sale);
} catch (DataIntegrityViolationException e) {
    // Same key already recorded — return the original sale, don't create a second.
    return saleRepository.findByIdempotencyKey(sale.getIdempotencyKey()).orElseThrow();
}

Checking "does this key exist?" before inserting reintroduces exactly the race we started with, so the unique constraint does the work and the catch block handles the loser. The API returns the original sale with a 200 instead of a 201. From the cashier's side, tapping twice looks identical to tapping once — which is the whole point.

Testing it

None of this is provable with a normal integration test, because a single-threaded test never interleaves. So the test fires the real thing at a real Postgres via Testcontainers:

@Test
void concurrentSalesNeverOversell() throws Exception {
    UUID itemId = seedStock(10);

    int threads = 20;
    var pool = Executors.newFixedThreadPool(threads);
    var start = new CountDownLatch(1);

    List<Future<Boolean>> futures = IntStream.range(0, threads)
        .mapToObj(i -> pool.submit(() -> {
            start.await();
            try {
                saleFacade.record(new SaleCommand(itemId, 1, UUID.randomUUID()));
                return true;
            } catch (InsufficientStockException e) {
                return false;
            }
        }))
        .toList();

    start.countDown(); // release all threads at once

    long sold = 0;
    for (Future<Boolean> future : futures) {
        if (future.get(10, TimeUnit.SECONDS)) sold++;
    }

    assertThat(sold).isEqualTo(10);
    assertThat(stockRepository.findById(itemId).orElseThrow().getQuantity()).isZero();
}

The CountDownLatch is doing real work: without it the threads start staggered and mostly miss each other. Releasing them together is what makes the race actually happen. Run this against the naive version at the top of the post and it fails — sold comes out somewhere north of 10 and the quantity goes negative. That failing test was the most convincing thing I wrote all week.

The summary I'd give in an interview

A SELECT followed by an UPDATE is not atomic, and READ COMMITTED doesn't make it so. Three ways to close the gap: version the row and retry outside the transaction, lock the row and serialize, or fold the check into the update and let the database decide. Then make the operation idempotent, because the network will duplicate requests regardless of how good your locking is.

Have a question about this?

Get in touch