Two orders for the last unit in the same second

Two orders for the last unit in the same second

A D2C client runs a Diwali drop: 300 units of a ₹4,200 brass lamp, each serialised, no restock promise. At 11:00:01 IST their server logs show two `POST /api/checkout` requests in the same second. Both read `stock = 1` from Postgres. Both pass the `stock > 0` guard. Both decrement. Both charge the card via Razorpay. One lamp, two paying customers.

The fix depends on where your inventory truth lives. For most Shopify-fronted stores we build for Indian D2C clients, the answer is: don't trust the application layer with stock at all.

The naive guard and why it fails

The checkout handler usually looks like this:

const product = await db.query('SELECT stock FROM products WHERE id = $1', [id]);
if (product.stock <= 0) return res.status(409).json({ error: 'out_of_stock' });
await db.query('UPDATE products SET stock = stock - 1 WHERE id = $1', [id]);
await chargeCard();
await createOrder();

Under Read Committed (Postgres default), both transactions see `stock = 1`. The `SELECT` is a snapshot. By the time the `UPDATE` runs, the other transaction has already decremented, but your `UPDATE` doesn't re-check the row — it just subtracts. You get `stock = -1` and two orders.

Adding `FOR UPDATE` on the `SELECT` serialises the two transactions, so the second one blocks until the first commits. That works, but it turns your checkout into a queue. Under a 2000-req/sec drop, you're holding row locks for the duration of a Razorpay call — 800ms to 3 seconds. Connection pool exhausts, request timeouts cascade, and the lamp sells out to nobody.

What we actually do

We move the stock check into a single atomic `UPDATE` and only proceed if it affected a row:

const result = await db.query(
  'UPDATE products SET stock = stock - 1 WHERE id = $1 AND stock > 0 RETURNING stock',
  [id]
);
if (result.rowCount === 0) {
  return res.status(409).json({ error: 'out_of_stock' });
}

The row-level lock is held for milliseconds, not for the duration of a payment gateway round-trip. If `rowCount` is zero, the unit is gone — we refund nothing because we haven't charged yet. The charge happens after the reservation succeeds.

The tradeoff: if the Razorpay call fails after reservation, you hold a unit for an abandoned checkout. We handle this with a 15-minute `reserved_until` timestamp instead of a straight decrement. A cron job releases expired reservations back to sellable stock. The customer who lost the race sees a real `409` instead of a spinner.

For the clients who need it — flash sales, limited drops, festival traffic — this is the difference between overselling and not. For a steady-state store doing 50 orders a day, the naive guard is fine. We don't add complexity where the failure mode shows up once a year.

The Diwali drop sold out in 47 seconds. Zero oversells, zero double-charges, one refunded abandoned cart where a card failed after reservation.