How Spring Boot Transactions Fail Under Concurrency: Prevent Overselling with Java and SQL

Category: Java & Backend · Level: intermediate/advanced · Prerequisites: Spring Boot, SQL transactions, Java concurrency · Experiment: reproducible concurrent inventory purchase tests

An e-commerce service has one product left in stock. Two buyers click “Purchase” almost simultaneously. Each request reads stock = 1, each decides stock is available, and both proceed. A @Transactional annotation appears on the service, so why can overselling still happen?

Because a database transaction is not automatically a business-invariant guarantee. A transaction gives an atomic unit of work under a chosen isolation level, but different transactions can still interleave in ways that violate the application’s assumptions. We will show the broken read–modify–write pattern, an atomic conditional SQL update, pessimistic row locking, and a concurrent integration test in Spring Boot.

1. The race in a read–modify–write workflow

The naive service does:

@Transactional
public boolean unsafePurchase(int itemId) {
    Integer stock = jdbc.queryForObject(
        "SELECT stock FROM inventory WHERE id=?", Integer.class, itemId);
    if (stock == null || stock <= 0) return false;
    jdbc.update("UPDATE inventory SET stock=? WHERE id=?", stock - 1, itemId);
    return true;
}

Possible interleaving under a common READ COMMITTED setting:

Time	Request A	Request B
1	reads stock = 1
2		reads stock = 1
3	decides to buy	decides to buy
4	writes stock = 0
5		writes stock = 0
6	returns success	returns success

The database shows stock zero, but two purchases were accepted for one unit. This is an instance of a lost-update-style race; the precise anomaly and behavior vary by SQL shape, engine and isolation level. Note that a CHECK(stock >= 0) constraint would not detect the duplicate success when both writers set stock to zero. The inventory value alone is not a complete accounting of accepted orders.

2. Fix #1: atomic guarded SQL update

Use one statement to check and decrement stock:

@Transactional
public boolean atomicPurchase(int itemId) {
    int rows = jdbc.update(
        "UPDATE inventory SET stock=stock-1 WHERE id=? AND stock>0", itemId);
    return rows == 1;
}

The eligibility condition and state change are evaluated within the database update. Concurrent updates against the same row are serialized/reevaluated according to the engine’s concurrency semantics; only one can decrement the last unit. For many inventory counters, this is simpler and more efficient than a separate read followed by an unconditional write.

The number of updated rows is part of the business contract. rows == 0 means there was no eligible stock at the time of the update; it need not mean the product never existed. Your API should distinguish the reason for rejection only if the extra query is necessary and safe.

3. Fix #2: pessimistic locking

Another approach locks the row before making the decision:

@Transactional
public boolean lockedPurchase(int itemId) {
    Integer stock = jdbc.queryForObject(
        "SELECT stock FROM inventory WHERE id=? FOR UPDATE",
        Integer.class, itemId);
    if (stock == null || stock <= 0) return false;
    return jdbc.update(
        "UPDATE inventory SET stock=stock-1 WHERE id=?", itemId) == 1;
}

The row lock protects the read–write decision as long as the entire critical section is inside one real database transaction. It is critical that the annotated method is invoked through the Spring transaction proxy; self-invocation within the same class may bypass transactional advice. Avoid remote HTTP calls, sleeps or user interaction while holding the lock, or throughput will collapse under contention.

SELECT ... FOR UPDATE syntax and lock modes vary by database. The companion demonstration uses H2 for convenience, but production behavior needs validation on your actual database engine and isolation level.

4. Optimistic locking: a third valid option

In an entity-based JPA application, a @Version field enables optimistic concurrency control. Two requests may read the same version, but the second commit or flush can fail with an optimistic-lock conflict. Your service must define whether to retry, reject, or return a conflict. A retry needs a new transaction and should not blindly repeat external side effects such as charging a payment card.

Optimistic locking tends to work well under low contention. Pessimistic locking can simplify strongly contended decisions, but creates blocking and deadlock risk. A guarded SQL update is often preferable when the invariant is expressed in one statement and you do not need a complex read–modify–write workflow.

5. Runnable Spring Boot project

The download includes a minimal spring-boot-starter-jdbc Maven project, an H2 in-memory inventory table with one unit, and JUnit tests that start two concurrent tasks using an explicit latch:

ExecutorService pool = Executors.newFixedThreadPool(2);
CountDownLatch start = new CountDownLatch(1);
Future<Boolean> a = pool.submit(() -> { start.await(); return service.atomicPurchase(1); });
Future<Boolean> b = pool.submit(() -> { start.await(); return service.atomicPurchase(1); });
start.countDown();
int successes = (a.get() ? 1 : 0) + (b.get() ? 1 : 0);
assertEquals(1, successes);

To run it with JDK 21+ and Maven:

mvn test

Both atomicPurchase and lockedPurchase have concurrent success-count assertions. The unsafe method is included as a discussion example, not used in a flaky nondeterministic test. To demonstrate the failure deterministically, place synchronization barriers between the read and write of two separate transactions and force both reads before either write. This is also a reminder that “my concurrent test passed once” is not proof that an algorithm is safe.

6. Database isolation is part of the design

READ COMMITTED generally prevents dirty reads, but does not prohibit all business-level races. REPEATABLE READ behavior differs among PostgreSQL and MySQL/InnoDB; never infer concurrency semantics from the isolation-level name alone. SERIALIZABLE can prevent additional classes of anomalies but may abort conflicting transactions that must be retried safely.

A particularly dangerous strategy is changing isolation level globally until a test turns green. Higher isolation may reduce concurrency, cause serialization failures, and still leave mistakes around side effects and idempotency. Prefer expressing the invariant atomically (or with explicit locking) and verify its behavior on the target database.

7. Deadlocks and transaction duration

Even correct locking can deadlock when two transactions acquire multiple row locks in opposite orders. Acquire locks in a consistent global order where practical. Keep transactions short. Set lock and statement timeouts, classify retryable errors based on your database’s SQL states, and use bounded exponential backoff. Treat idempotency and external side effects explicitly—do not automatically retry an uncertain payment request without a provider-level idempotency key.

A correctly rolled-back transaction cannot undo a successful external API call. If you need to publish an event after persisting an order, consider a transactional outbox: write the order and event record atomically, then publish asynchronously with an idempotent consumer.

8. Business invariants need database constraints

Beyond stock decrement, store orders under unique IDs, enforce nonnegative inventory with a constraint, and consider a unique constraint on an idempotency key per customer/request. Constraints are a final line of defense against duplicate persistence; application-only checks are subject to races.

For an order-creation endpoint, define exactly what happens when the same client retries after a timeout. The system should return the original result (or an agreed in-progress response) for the same idempotency key, not create a second order or charge twice. Inventory correctness and payment correctness are related but distinct invariants.

9. How to validate in production-like conditions

The H2 test is a rapid functional demonstration, not proof that PostgreSQL or MySQL will behave identically. Repeat tests with a production database through Testcontainers. Add high contention, transaction rollback, lock-timeout, deadlock, and process-restart scenarios. Check API consistency, accepted-order count and stock invariants, not just absence of SQL exceptions. Instrument lock wait duration, pool utilization and retry counts.

Takeaway: @Transactional protects a unit of work, not your assumptions about what a concurrent request will observe. Build invariants into atomic SQL or appropriate locking and test those invariants under contention. Continue with Java Concurrency Interview Topics or assess your backend reasoning through Algonique Java Skill Assessment.

Frequently asked questions

Does @Transactional prevent race conditions? No. It establishes transaction boundaries; correctness depends on isolation, statement semantics, locks and constraints.

Is SELECT FOR UPDATE always better? No. It may be helpful for read–modify–write workflows but adds contention and can deadlock. A single guarded update is often simpler.

Does optimistic locking prevent all overselling? It can prevent conflicting versions from both committing, provided exceptions are handled and the invariant spans the correct entities and transactions.

Is H2 enough to test concurrent purchases? No. Use it for fast examples; run integration tests against the actual database engine before claiming production-grade guarantees.