Race Conditions in Concurrent Stock Updates: Causes, Tests, and Fixes

The classic “read‑check‑write” pattern looks safe when a single request processes an inventory item: the application reads the current stock value, checks that it is greater than zero, computes a new value (e.g., stock‑1), and writes that value back. In isolation the invariant “stock never becomes negative” holds.

When two callers execute the same three‑step sequence concurrently, the steps can interleave. Consider an initial state stock = 1. The interleaving may occur as follows:

Request A: READ  → stock = 1
Request B: READ  → stock = 1
Request A: CHECK → stock > 0 (true)
Request B: CHECK → stock > 0 (true)
Request A: CALCULATE → newStock = 0
Request B: CALCULATE → newStock = 0
Request A: WRITE → stock = 0
Request B: WRITE → stock = 0

Both requests observe the same original value, both pass the availability check, and both write the same replacement. The final database row shows stock = 0, but two logical “decrements” have been reported as successful—a classic lost‑update race window.

• Business invariant violated: successful decrements may exceed the available quantity.
• Application‑level checks no longer guarantee correctness under contention.
• Only the final state is visible; the intermediate double‑success is hidden.

Eliminating the window requires moving the decision logic into the write operation so that the read, check, and update happen atomically. An example SQL statement that achieves this is:

UPDATE inventory_products
SET stock = stock - 1
WHERE id = $1 AND stock > 0
RETURNING stock;

Here the condition stock > 0 and the mutation stock = stock - 1 are evaluated in a single statement, guaranteeing that a decrement occurs only if the row still satisfies the invariant at the moment of update.

Another robust approach is to lock the row before reading, using SELECT … FOR UPDATE. The lock forces any concurrent transaction to wait until the first transaction completes its read‑check‑write cycle, preventing overlapping reads of the same value.

• Atomic conditional update (single statement) – minimal lock contention.
• Row‑level locking with FOR UPDATE – explicit coordination of multi‑step logic.
• Database‑enforced constraints (e.g., unique indexes for booking) – ensure invariants even if application checks race.

Both techniques preserve the invariant “successful decrements ≤ available stock” without relying on fragile timing or ad‑hoc sleeps, providing a deterministic concurrency guarantee suitable for enterprise systems.

UPDATE inventory_products
SET stock = stock - 1
WHERE id = $1 AND stock > 0
RETURNING stock;

When an application reads a row, decides whether the operation is allowed, and then writes a new value, the decision point exists outside the database transaction. As the evidence shows, two concurrent requests can both read stock = 1, both pass the stock > 0 check, and both write stock = 0. The write‑side does not see the other request’s update, producing a lost‑update and violating the invariant “successful decrements ≤ available stock”.

Embedding the condition directly in the UPDATE statement removes this application‑level race window. The statement becomes a single atomic operation that the database evaluates and applies in one step:

UPDATE inventory_products
SET    stock = stock - 1
WHERE  id = $1
  AND  stock > 0
RETURNING stock;

Key characteristics of this pattern are:

• Single‑statement mutation: The database computes stock - 1 internally; the application never supplies a pre‑calculated value.
• Condition as part of the write: The WHERE stock > 0 clause guarantees that the decrement occurs only when the invariant holds.
• Immediate feedback: The RETURNING clause supplies the new stock value or returns no rows, allowing the application to know whether the decrement succeeded without a separate read.

Under contention, the database serialises competing updates at the row level. If two transactions attempt the same update, one succeeds (its WHERE clause matches) and the other sees zero rows returned, indicating that the stock was already exhausted. No interleaving read‑check‑write sequence exists, so the race window is eliminated.

Practical implementation steps for engineers:

1. Wrap the atomic UPDATE … RETURNING in a transaction that only contains this statement (or rely on the statement’s implicit atomicity if no other modifications are needed).
2. Check the result set: a non‑empty row means the decrement succeeded; an empty set means the stock was insufficient.
3. Log or surface the “out‑of‑stock” condition to the caller without performing a separate SELECT.

This approach aligns with security and reliability standards such as OWASP’s recommendation to enforce business rules at the data‑store boundary and NIST’s guidance on minimizing attack surface by reducing unnecessary application logic. By moving the condition into the UPDATE, the invariant is enforced at the write boundary, guaranteeing consistency even under high concurrency.

PostgreSQL implements row‑level locking through the SELECT … FOR UPDATE clause. When a transaction issues this statement, the targeted row is locked in exclusive mode, preventing any other transaction from acquiring a conflicting lock on the same row until the first transaction commits or rolls back. This mechanism is useful for the classic read‑check‑update pattern where the business invariant (e.g., “stock must never become negative”) must be enforced under contention.

The typical flow with a row lock looks like this:

1. Begin a transaction. All subsequent statements execute within the same atomic boundary.
2. Lock the row. SELECT stock FROM inventory_products WHERE id = $1 FOR UPDATE; acquires an exclusive lock on the row identified by $1.
3. Validate the invariant. The application reads the stock value and checks stock > 0.
4. Apply the change. If the check passes, execute UPDATE inventory_products SET stock = stock - 1 WHERE id = $1;.
5. Commit. The lock is released, allowing other transactions to proceed.

Because the lock is held from step 2 through step 5, a concurrent transaction attempting the same SELECT … FOR UPDATE will block at the lock acquisition point. It cannot read the stale value, perform its own check, and issue an update while the first transaction is still in progress. This eliminates the “lost‑update” window illustrated in the evidence where two requests read the same stock value, both pass the check, and both write back the same result.

Practical example:

BEGIN;
SELECT stock FROM inventory_products WHERE id = 42 FOR UPDATE;
-- Assume result is 3
UPDATE inventory_products SET stock = stock - 1 WHERE id = 42;
COMMIT;

Key considerations when choosing row locking:

• Use it when the decision logic cannot be expressed in a single SQL statement (e.g., complex business rules that require multiple reads).
• Be aware that long‑running transactions increase lock contention and may lead to deadlocks; keep the critical section as short as possible.
• Combine row locking with appropriate isolation levels (e.g., READ COMMITTED) to avoid unnecessary snapshot overhead.

Row‑level locking is one of several strategies—atomic conditional updates or unique constraints are alternatives—each suited to a specific invariant. Selecting the right mechanism depends on the shape of the invariant and the performance characteristics of the workload.

When the business invariant is expressed as a uniqueness rule rather than a numeric counter, the same race‑condition pattern appears. A booking is identified by the tuple (branch_id, service_date, slot_time) and the table definition contains a UNIQUE(branch_id, service_date, slot_time) constraint. The invariant is therefore “only one row may exist for a given branch, date, and slot.”

In a naïve implementation the application performs a pre‑check followed by an insert:

SELECT 1 FROM bookings
WHERE branch_id = $1 AND service_date = $2 AND slot_time = $3;

-- if no row is returned
INSERT INTO bookings (branch_id, service_date, slot_time, customer_id)
VALUES ($1, $2, $3, $4);

Under low load this works, but when many concurrent requests target the same slot the two steps can interleave. Both transactions may read “no row” before either insert becomes visible, leading each to attempt the same INSERT. The database’s unique index detects the conflict and aborts one transaction with SQLSTATE 23505 (unique‑violation). This error is not a failure of the database; it is the enforcement point for the invariant.

• Invariant enforcement point: The unique constraint guarantees that at most one row can be persisted for the given combination.
• Concurrency outcome: Successful inserts ≤ 1; all other attempts receive SQLSTATE 23505.
• Application handling: Treat the 23505 error as an expected concurrency result, translate it into a business‑level “slot already booked” response, and optionally retry with an alternative slot.

Alternative implementations mirror the inventory example:

• Atomic insert with conflict handling: Use INSERT … ON CONFLICT DO NOTHING RETURNING id to let the database decide atomically.
• Explicit row lock: Select the row with FOR UPDATE before checking availability, which serialises competing transactions.

Both approaches move the decision point from application code into the database transaction, eliminating the window where two processes can both believe the slot is free. The key lesson is that pre‑checks alone do not protect business invariants under concurrency; a storage‑level constraint (or an equivalent atomic statement) must be the final guard, and the application must be prepared to handle the resulting 23505 conflict as a normal part of high‑throughput booking workflows.