Eliminating Race Conditions in High-Throughput Checkout: Row-Level Locking with PostgreSQL

1. The Anatomy of an Inventory Race Condition

During flash sales, seasonal discounts, or ticket drops, hundreds of concurrent web requests hit checkout endpoints in the same millisecond. If your database concurrency model is not fortified with row-level locks, multiple users will successfully pay for the exact same stock unit.

The resulting operational fallout is brutal: canceled orders, angry chargebacks, negative reviews, and manual customer support firefighting.

2. Why 'Read Then Write' Application Logic Fails

Most junior web developers write checkout verification like this:

// ❌ CRITICAL BUG: Classic TOCTOU (Time of Check to Time of Use) race condition
const product = await db.product.findUnique({ where: { id: productId } });

if (product.stock >= requestedQuantity) {
  // If 5 concurrent users execute this check simultaneously,
  // ALL 5 will pass because stock has not been updated yet!
  await initiatePayment();
  await db.product.update({
    where: { id: productId },
    data: { stock: product.stock - requestedQuantity }
  });
}

Between the moment the query reads product.stock and the moment update writes the new value, other concurrent requests sneak into the gap.

3. The Solution: SELECT ... FOR UPDATE

In PostgreSQL, wrapping the lookup inside a transaction with FOR UPDATE acquires an exclusive row-level lock on the specific stock record. Any subsequent concurrent checkout transactions attempting to inspect or modify this product must queue up until the lock is committed or rolled back:

BEGIN;

-- Lock the row exclusively for this transaction thread
SELECT id, stock, price 
FROM products 
WHERE id = :product_id 
FOR UPDATE;

-- Evaluate stock safety inside the exclusive lock
UPDATE products 
SET stock = stock - :quantity,
    updated_at = NOW()
WHERE id = :product_id 
  AND stock >= :quantity;

COMMIT;

4. Single-Statement Atomic Inventory Decrement

For maximum throughput without long-held transactions, PostgreSQL allows single-statement atomic operations utilizing database constraints:

-- Single atomic mutation with returning guard
UPDATE products
SET stock = stock - 1
WHERE id = $1 
  AND stock > 0
RETURNING id, stock;

If the affected rows count is 0, the inventory was already exhausted by an earlier request. Your backend can immediately abort the transaction without charging the customer's card.

5. Ephemeral Cart Holds with Redis Expiry

To give buyers 10 minutes to enter card details without permanently locking out other customers, we implement an ephemeral reservation pattern in Redis. If the buyer closes the tab, the Redis key expires automatically, releasing inventory back into the active pool with zero orphaned records.