Flash Sale: Inventory Under Contention

Here is the final exam for everything in this section, and it fits on a T-shirt: you have 100 units of something people want badly — sneakers, a console, concert tickets — and at exactly 12:00:00 you put them on sale. Demand doesn't trickle in. Ten thousand buyers arrive in the same second, every one of them trying to buy the same thing at the same instant. This is a flash sale, and it is the most hostile load a transactional system ever sees.

Everything comes down to a single row. Your inventory is a row — inventory(sku, stock) — and every purchase is one operation on it: decrement stock by 1. Normally concurrency is spread across thousands of different rows and conflicts are rare (that was the comfortable assumption behind Pessimistic vs Optimistic Locking). A flash sale does the opposite: it concentrates all contention onto one row. This is the hot-row problem, and you can't shard your way out of it, because there is only one thing everyone wants.

Two things are on the line at once, and the whole lesson is the fight between them:

• Correctness — never oversell. With 100 units you must sell at most 100. The invariant is stock >= 0, forever, under any amount of concurrency. Sell 149 and 49 customers get a confirmation for a unit that doesn't exist.
• Throughput — survive the burst. A scheme that's perfectly correct but forces ten thousand buyers through a single-file line turns a two-second sale into a two-minute outage.

The intuition most people carry in is that these trade off — that correct means slow, and you buy safety with a lock. The measured result of this lesson says that intuition is wrong, and watching exactly how it's wrong is the point. Here's the scoreboard we're going to earn:

Write the purchase the way anyone would the first time:

SELECT stock FROM inventory WHERE sku = 'sale';   -- read
-- (in the app)  if stock > 0:
UPDATE inventory SET stock = stock - 1 WHERE sku = 'sale'; -- act

Read the stock, check it's positive, decrement. It passes every test you write, because your tests run it one call at a time. Then the flash sale hits it with real concurrency and it oversells.

The bug is the gap between the read and the write. Under READ COMMITTED (your database's default), nothing stops a whole wave of buyers from running that SELECT in the same instant, all reading the same stock > 0, all passing the check, and all decrementing. This is the lost update from Isolation Levels & Their Anomalies and the check-then-act race from Pessimistic vs Optimistic Locking — except now it's not two threads, it's five hundred. Run it for real on Postgres (pgbench, 100 units, 500 buyers, 50 at a time) and the damage is not subtle:

strategy: naive read-modify-write
units sold ......  149      ← you had 100
final stock ....  -49      ← forty-nine orders you cannot fulfill
throughput .....  2150 tps

149 sold for 100 units. The stock went to −49. That is not an edge case you can shrug off with a few refunds — at flash-sale concurrency it's the default outcome, and it scales with how many buyers hit at once. Worse, the naive version isn't even fast (2150 tps); you got the wrong answer and paid for it. Every fix from here on has to close that read-to-write gap.

The gap oversells because buyers overlap, so the obvious fix is to stop them overlapping — let one buyer touch the row at a time. Two ways, both correct, both with a bill attached.

Pessimistic locking (Pessimistic vs Optimistic Locking): take the row's lock before you decide, with SELECT … FOR UPDATE.

BEGIN;
SELECT stock FROM inventory WHERE sku = 'sale' FOR UPDATE;  -- exclusive row lock
UPDATE inventory SET stock = stock - 1 WHERE sku = 'sale';    -- (if stock > 0)
COMMIT;

Now every buyer queues on that lock — no two can read-then-write at the same time, so there is no oversell, stock stops exactly at 0. But look at the throughput:

strategy: pessimistic FOR UPDATE
units sold ......  100      ✓ correct
final stock ....  0
throughput .....  1322 tps  ← the SLOWEST of the four

Correct, and the slowest of every strategy (1322 tps, barely half the atomic path we're about to see). Each buyer holds the lock from the SELECT all the way to the COMMIT — across a network round trip — while everyone else waits behind them. You bought correctness with latency, one buyer at a time.

SERIALIZABLE isolation reaches the same correctness from the other direction: it lets the transactions run optimistically and aborts the ones that conflict, with a serialization_failure (SQLSTATE 40001) the client must catch and retry. Correct — but under this stampede the abort rate is brutal:

strategy: SERIALIZABLE
processed ......  33 / 500
failed .........  467  (93.4% serialization failures)
throughput .....  290 tps

93.4% of buyers were aborted and have to try again — the retries are wasted work, and it's the slowest path by a mile (290 tps). Serializable didn't make correctness free; it moved the cost into a storm of retries. So: is correctness just expensive? No. We've been reaching for a bigger lock when the answer is a smaller operation.

Step back and notice what the naive version actually did wrong: it split one decision — is there stock, and if so take one — into a read, a check, and a write, with gaps between them. The fix isn't to wrap a lock around those three steps. It's to stop having three steps. Do the whole thing in one atomic statement and let the database run it:

UPDATE inventory SET stock = stock - 1
 WHERE sku = 'sale' AND stock > 0;
--  rows affected = 1  → you got a unit
--  rows affected = 0  → sold out, reject the buyer

The WHERE stock > 0 guard and the stock - 1 decrement are now a single atomic operation on the row — there is no gap for anyone to race into, because the check and the change happen together, inside the engine, on the same row lock. This is ACID, Precisely's Atomicity doing the whole job in one statement, and it's the optimistic / compare-and-set shape from Pessimistic vs Optimistic Locking pushed all the way into the write. The rows-affected count tells you if you won (1) or the item is gone (0). Measured, same stampede:

strategy: atomic conditional write
units sold ......  100      ✓ correct
final stock ....  0
throughput .....  3088 tps  ← the FASTEST of the four

Correct — and the fastest of all four (3088 tps, more than double the pessimistic lock). This is the punchline of the whole section, and it's genuinely surprising the first time: the safest option is also the quickest. Why? The pessimistic lock holds the row from the SELECT to the COMMIT, across a client round trip — milliseconds of holding a contended resource. The single UPDATE holds the row's lock only for the microseconds it takes the engine to do the write, then releases. Same correctness, a fraction of the time spent on the hot row. Under contention, the fix is usually a smaller, atomic operation — not a bigger lock.

The atomic write makes the count correct — you'll never sell a 101st unit. But there's a second, sneakier way a flash sale goes wrong, and the atomic decrement doesn't touch it: the same buyer getting two units.

A stampede is exactly where retries happen — a rage-click on a slow "Buy" button, a mobile reconnect, a load balancer re-sending a request it wasn't sure landed. Each of those can fire the purchase twice, and two atomic decrements are two perfectly valid decrements:

same order, submitted twice, no idempotency key:
  stock 100 → 98    ← one buyer, one order, TWO units gone

The count invariant held (stock never went negative), but the allocation is wrong: this buyer took two, so someone else gets none. That's a different guarantee — per-buyer exactly-once — and it needs the discipline from Idempotency as a Discipline: give the checkout a stable idempotency key (the order id) and gate the decrement on claiming it, in one transaction:

WITH claim AS (
  INSERT INTO orders(order_id) VALUES ('A') ON CONFLICT DO NOTHING RETURNING order_id
)
UPDATE inventory SET stock = stock - 1
 WHERE sku = 'sale' AND stock > 0 AND EXISTS (SELECT 1 FROM claim);

same order, submitted twice, WITH the key:
  stock 100 → 99    ← the second submit is a no-op. One order = one unit.

Two guarantees, two mechanisms: the atomic decrement keeps the total right, the idempotency key keeps each buyer right. A real flash sale needs both — and if buyers hold inventory before they pay (add-to-cart), you add a third: reserve with a TTL and release on timeout, the compensating move from Saga: The Data-Consistency Mechanics.

Numbers on a page are one thing; watching the stock counter tick to −49 in red is another. Below is the whole sale, live. Set the stock, the size of the stampede, and how many buyers hit at once, pick a strategy, and run it — the one inventory row takes the hit in real time and the scoreboard fills in.

Start with naive and watch it oversell, then drag concurrency down to 2 and watch the bug vanish — that's why it survives your tests and dies in production. Switch to pessimistic and notice the throughput bar shrink; switch to SERIALIZABLE and watch the retries pile up; then land on the atomic conditional and see the only row that's both correct and the longest throughput bar. Feel the tradeoff in your hands — it's the entire section in one screen.

The flash sale was a final exam, and every answer traced straight back to a lesson in this section:

• the oversell was a lost update under weak isolation (Isolation Levels & Their Anomalies);
• the strategies were pessimistic vs optimistic locking (Pessimistic vs Optimistic Locking);
• the fix was atomicity doing the check-and-change in one statement (ACID, Precisely);
• the double-click needed idempotency (Idempotency as a Discipline);
• the hold-then-confirm was a saga (Saga: The Data-Consistency Mechanics).

And under all of it sits one meta-lesson worth carrying out of this section: push correctness down to where the data lives, and make the critical operation as small and atomic as you can. The database, sitting next to the row, doing the check and the change in a single statement, beats any amount of clever coordination in your application code — it's correct, and it's faster.

One honest limit remains: even the perfect atomic write serializes on the one row. At the scale of a console drop or a Singles' Day, a single hot row is itself a ceiling, and real systems break it up — shard the counter into buckets (Google's sharded-counter pattern), decrement in memory with Redis DECR, or put buyers in a waiting room and admit them at a rate the inventory can serve (Ticketmaster, Shopify). Those are throughput moves, not correctness moves — the atomic decrement is still what keeps the count honest wherever it lives — and they are exactly where this course goes next, in the Problems course, where you'll design the whole flash-sale system, not just its hottest row. You now have the full transactions-and-concurrency toolkit; time to point it at real problems.