The Last-Item Race Condition: Why Two Customers Both 'Win' the Same Order

Picture a storefront — call it RedBite — running a midnight flash sale. One SKU, the Sony WH-1000XM5 headphones, has exactly 1 unit left. At 00:00:00, two real customers, Priya and Arjun, both have the product page open and both tap “Buy Now” within 20 milliseconds of each other.

RedBite’s checkout API returns 200 OK to both of them. Both get an order confirmation email. RedBite now owes a unit of stock it doesn’t have.

This isn’t a hypothetical — it’s the default behavior of the obvious way to write a checkout endpoint. Let’s build RedBite’s checkout wrong first, see exactly where it breaks, then fix it three different ways with real trade-offs.

Step 1: read the stock

Every checkout starts the same way — ask the database how many units are left:

const { rows } = await pool.query(
  'SELECT stock FROM items WHERE id = $1',
  [itemId]
);

For the headphones, rows[0].stock is 1. Priya’s request runs this query. A few milliseconds later, before Priya’s request has gotten any further, Arjun’s request runs the exact same query — and also gets back 1. Postgres isn’t lying to either of them; stock really is 1 at the moment each of them asks. The database has no idea these two questions are part of the same decision.

Step 2: check it

if (rows[0].stock < 1) {
  throw new Error('Out of stock');
}

Priya’s 1 passes. Arjun’s 1 also passes. Both requests independently conclude “yes, sell it to me” — because as far as either one knows, it’s the only request in flight.

Step 3: write it

await pool.query(
  'UPDATE items SET stock = stock - 1 WHERE id = $1',
  [itemId]
);
await pool.query(
  'INSERT INTO orders (item_id, user_id) VALUES ($1, $2)',
  [itemId, userId]
);

Priya’s request decrements stock to 0 and inserts her order. Arjun’s request — which already decided “proceed” back in Step 2, using stale information — decrements stock to -1 and inserts his order too. RedBite now has two paid orders for one pair of headphones.

Where it actually breaks

final stock = -1. Priya AND Arjun both charged.

That gap between the SELECT and the UPDATE is the entire bug. Nothing in the code ever told Postgres “don’t let anyone else touch this row while I’m deciding.” RedBite now has to refund one of them and send an awkward “sorry, actually out of stock” email — not a great look during a flash sale.

Fix 1: pessimistic locking — make Arjun’s request wait

The fix: tell Postgres to lock the headphones row the instant you read it, so whichever request gets there second has to wait its turn instead of racing.

First, wrap the read in a transaction and add FOR UPDATE:

await client.query('BEGIN');
const { rows } = await client.query(
  'SELECT stock FROM items WHERE id = $1 FOR UPDATE',
  [itemId]
);

Say Priya’s request reaches this line first. Postgres locks the headphones row for her transaction. When Arjun’s request runs the same SELECT ... FOR UPDATE a few milliseconds later, it doesn’t get a stale 1 back — it blocks, sitting there waiting, because Postgres knows someone else already has a hold on that exact row.

Priya’s transaction finishes the job — same check and write as before, now inside the lock:

if (rows[0].stock < 1) {
  await client.query('ROLLBACK');
  throw new Error('Out of stock');
}
await client.query(
  'UPDATE items SET stock = stock - 1 WHERE id = $1',
  [itemId]
);
await client.query(
  'INSERT INTO orders (item_id, user_id) VALUES ($1, $2)',
  [itemId, userId]
);
await client.query('COMMIT');

The moment Priya’s COMMIT lands, Arjun’s blocked SELECT ... FOR UPDATE finally returns — and now it reads stock = 0, not 1. His check fails honestly, and he gets a real “out of stock” response instead of a fake success.

Trade-off: correct, and it’s one BEGIN/FOR UPDATE/COMMIT away from the buggy version. But picture RedBite’s biggest sale day: 500 people hitting “Buy Now” on the same headphones in the same second. Every one of those 500 requests queues up behind this one row lock, one at a time. Fine for “last unit of a flash-sale item” — exactly the case this fixes. A bad default for RedBite’s regular checkout, where most items aren’t this contended.

Fix 2: optimistic locking — let both requests race, catch the loser

Instead of making Arjun’s request wait up front, bet that most items on RedBite aren’t down to the last unit, so most requests won’t actually collide. Read without a lock, and only commit the write if nothing changed underneath you.

Add a version number to the row:

ALTER TABLE items ADD COLUMN version INT NOT NULL DEFAULT 0;

Priya and Arjun both read the row the old, lock-free way — both see stock = 1, version = 7:

const { rows } = await pool.query(
  'SELECT stock, version FROM items WHERE id = $1',
  [itemId]
);
const { stock, version } = rows[0];
if (stock < 1) throw new Error('Out of stock');

Here’s the trick — the write only succeeds if version is still 7:

const result = await pool.query(
  `UPDATE items
     SET stock = stock - 1, version = version + 1
   WHERE id = $1 AND version = $2`,
  [itemId, version]
);

Say Priya’s write lands first. It matches version = 7, so it succeeds — rowCount is 1, and version becomes 8. Arjun’s write runs a moment later, still holding the old version = 7 it read earlier. The row is already at version = 8, so his WHERE clause matches zero rows. rowCount is 0 — Postgres is telling Arjun’s code “someone beat you to it,” without any locking at all:

if (result.rowCount === 1) {
  await pool.query(
    'INSERT INTO orders (item_id, user_id) VALUES ($1, $2)',
    [itemId, userId]
  );
} else {
  // someone else updated it first — retry with fresh data, or
  // tell Arjun honestly that it's gone
}

Trade-off: no queueing, scales well across RedBite’s whole catalog where contention on any single item is rare. But for the one headphones listing everyone’s fighting over, you now get a pile of failed retries instead of an orderly queue — worse than the lock for that specific item, better for the other 9,999 SKUs that aren’t getting hammered.

Fix 3: atomic Redis decrement — for when the queue itself is the bottleneck

If RedBite’s flash sale gets big enough that even the Postgres lock queue becomes the slow part, move the hot check out of Postgres entirely. Let Redis own the live counter, and keep Postgres as the durable record of what sold.

const remaining = await redis.decr(`stock:${itemId}`);

DECR is atomic inside Redis itself — there’s no separate read step for Priya’s and Arjun’s requests to race on, because there’s nothing to read. Redis serializes the two DECR calls internally. Say Priya’s hits first: the counter goes 1 -> 0, and her request sees remaining = 0 — she got it. Arjun’s DECR runs microseconds later: 0 -> -1, and his request sees remaining = -1.

Anyone who sees a negative number lost the race, and gets their unit handed back immediately:

if (remaining < 0) {
  await redis.incr(`stock:${itemId}`); // give Arjun's unit back
  throw new Error('Out of stock');
}
await pool.query(
  'INSERT INTO orders (item_id, user_id) VALUES ($1, $2)',
  [itemId, userId]
);

Trade-off: this is the fastest of the three by a wide margin — but now RedBite has two sources of truth. If the process crashes between Priya’s DECR succeeding and her order actually landing in Postgres, Redis says “sold” and Postgres has no record of it. That’s a unit of real inventory that’s quietly vanished. Needs a reconciliation job comparing Redis’s sold count against Postgres orders on a schedule — not optional, not something to skip because the demo works.

Which one RedBite should actually ship

For most of RedBite’s checkout flow: start with SELECT ... FOR UPDATE. It’s correct by construction, and the “queue on one row” cost only bites on items that are already in the exact scenario it’s defending against — genuine last-unit contention, like the headphones.

Reach for optimistic locking on items that get read constantly but written rarely — most of the catalog, any day that isn’t a flash sale. Reach for the Redis counter only once you’ve actually measured Postgres row-locking as the bottleneck on a sale this size — not before, because it trades a correctness guarantee for throughput, and that trade should be a measured decision, not a default.

Got a different fix you’ve shipped for this, or a contention bug this doesn’t cover? Drop it in #system-design on Discord.