Two orders for the last unit in the same second

Two orders for the last unit in the same second

Picture a D2C brand running a festival drop: 300 units of a ₹4,200 brass lamp, each serialised, no restock promise. At 11:00:01 the 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.

This is not a hypothetical failure mode. It is the default behaviour of a checkout handler that was written for a store doing a few orders an hour and is now being hit by a few hundred a second.

The fix depends on where your inventory truth lives. For most Shopify-fronted D2C stores with a custom checkout, 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. Nothing in the logs looks wrong, either; both requests returned 200, and the problem only surfaces when the warehouse goes to pack a lamp that isn't there.

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.

Two details matter in the reservation approach. The release job has to be idempotent, because it will eventually run twice for the same row. And the 15-minute window should match the payment gateway's own timeout, or you end up releasing a unit that a customer is still in the middle of paying for.

For the stores that 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.

With the atomic update and reservation window in place, a 300-unit drop like the one above sells out in under a minute with no oversells and no double charges. The only cleanup is the occasional reservation released after a card fails, which is the system working as designed.