Twenty thousand people, a hundred phones

01The drop

The streamer holds up a phone. “A hundred of these, half price, starting now.” In the chat, twenty thousand thumbs are already hovering over Buy. The backend is nothing exotic: an API, a connection pool, one Postgres, and a perfectly correct UPDATE that decrements the stock only while some is left. Three seconds later the dashboard is red. Not for the phone page, for everything: login, search, the product pages nobody was even trying to buy.

This is the same family of bug as the clinic booking post and the last concert ticket, with one twist: those posts asked “how do I stop two people getting the same item?” and ended with one atomic statement. That statement is still right here. What changes is the ratio. Twenty thousand people are fighting over a hundred items, so nineteen thousand nine hundred of them are guaranteed to lose, and the design question becomes how cheaply the system can say no. I first met this as an interview question about a live-commerce platform, and the answer I wish I had given that day is the rest of this post.

requests per item

20,000 : 100

buyers who will hear “sold out”

99.5%

for 100 winners to clear one row lock at ~20 ms each

≈ 2 s

tiers: gate in RAM, truth in Postgres

2

02Why the correct query dies

Here is the statement, the same shape as in the other posts:

UPDATE sale_stock
SET    sold = sold + 1
WHERE  sale_id = 42 AND sold < total
RETURNING sold;
-- 1 row  -> you got one
-- 0 rows -> sold out

Nothing is wrong with it. The trouble is the physics underneath. An UPDATE takes a row lock and keeps it until commit, and a row lock admits one writer at a time. All twenty thousand requests target the same row (sale_id = 42), so while there is stock left the database lines them up and serves them single file. If each write held the lock for about a millisecond (a rough figure, it depends on your disk and replication settings), that row would sustain roughly a thousand writes per second, and a bare UPDATE would sell all hundred phones in a tenth of a second. A real checkout is not bare: the same transaction inserts the order, writes a ledger line, makes a few round trips from the app, and may wait for a synchronous replica before it commits. Say that stretches the hold to 20 ms. The hundred winners now need two seconds to get through the line, and all twenty thousand requests arrive while it is still moving. Clients time out, people retry, and the line grows. (Once the last phone is committed, Postgres re-checks the WHERE against the new row version, so the losers stop waiting on the lock. If the code instead locks first with SELECT ... FOR UPDATE and checks the stock in the app, every one of the twenty thousand takes its turn on the lock, and the line is twenty seconds long even at 1 ms.)

Now the part that kills the other pages. The app talks to Postgres through a connection pool, say a hundred connections. The first hundred requests grab all of them, and every one of those connections is now parked waiting for the same lock. Request 101 onwards cannot even start: it waits for a free connection. A user trying to log in needs a connection too, and there are none left. One hot row has turned into a whole-site outage. The view counter post drew this chain for a different workload; the picture is the same.

03Say no before the database

Look at the traffic honestly. Of twenty thousand requests, a hundred will succeed and the rest are doomed from the moment the hundredth phone is claimed. Yet in the naive design every one of them takes the same expensive trip: through the app, into the pool, onto the lock queue while the phones last, back out as “sold out”. Picture a doorman at a concert hall with a hundred wristbands. He does not let the crowd into the box office and ask the clerk to check the ledger for each person. He counts wristbands at the door, and when they are gone he says so, loudly, to everyone behind.

That doorman is a gate in front of the database. It needs two properties: it must be fast (answer in well under a millisecond, from memory), and it must be atomic (a hundred phones means exactly a hundred winners, not a hundred and three). Redis fits: its commands run on a single thread, so one command cannot interleave with another.

The gate: one Lua script

A single DECR would already be atomic. But a real sale also needs “one phone per person”, and that is two keys: a stock counter and a set of buyers. Doing GET, then DECR, then SADD from the application would be the read-modify-write cycle from the concert ticket post all over again. The fix is the same idea moved into Redis: a Lua script runs as one uninterrupted unit, so nothing can happen between its steps.

-- KEYS[1] = {sale:42}:stock    KEYS[2] = {sale:42}:buyers
-- ARGV[1] = user id             ARGV[2] = qty
if redis.call("SISMEMBER", KEYS[2], ARGV[1]) == 1 then return -1 end   -- already bought
local stock = tonumber(redis.call("GET", KEYS[1]) or "0")
if stock < tonumber(ARGV[2]) then return 0 end                         -- sold out
redis.call("DECRBY", KEYS[1], ARGV[2])
redis.call("SADD", KEYS[2], ARGV[1])
return 1                                                               -- reserved

import Redis from "ioredis";
import { randomUUID } from "node:crypto";
const redis = new Redis(process.env.REDIS_URL!);
redis.defineCommand("reserve", { numberOfKeys: 2, lua: RESERVE_LUA });   // loaded once, called by SHA

type Reserve = { status: "ok"; reservationId: string } | { status: "sold_out" | "dup" };

export async function tryReserve(saleId: number, userId: string): Promise<Reserve> {
  const tag = {sale:${saleId}};                                        // hash tag: both keys land on one cluster slot
  const r = await (redis as any).reserve(${tag}:stock, ${tag}:buyers, userId, 1);
  if (r !== 1) return { status: r === 0 ? "sold_out" : "dup" };
  return { status: "ok", reservationId: randomUUID() };                  // goes into the queue message
}

04Let winners trickle into Postgres

Only a hundred requests survive the gate. They are now an ordinary workload, but they still should not rush the database in one burst, and the user does not need the order written before the page answers. So each winner drops a message on a queue (SQS, Kafka, anything durable) and gets back “you got it, finish paying within ten minutes”. A small pool of workers drains the queue at a pace the database likes.

The worker does the real bookkeeping, and it re-checks the stock in Postgres rather than trusting Redis. This looks redundant and is the most important line of the design. Redis is the gate, a fast and slightly forgetful doorman. Postgres is the ledger. If the gate ever lets one person too many through (more on how in a moment), the conditional update is what stops an oversell from becoming a real order.

CREATE TABLE sale_orders (
  reservation_id uuid   PRIMARY KEY,        -- minted at reserve time, travels in the queue message: the idempotency key
  sale_id        int    NOT NULL,
  user_id        bigint NOT NULL,
  qty            int    NOT NULL,
  status         text   NOT NULL DEFAULT 'PENDING_PAYMENT',
  created_at     timestamptz NOT NULL DEFAULT now()
);
-- at most one LIVE order per buyer per sale; an EXPIRED row must not block buying again
CREATE UNIQUE INDEX one_live_order ON sale_orders (sale_id, user_id)
  WHERE status IN ('PENDING_PAYMENT', 'PAID');

BEGIN;
INSERT INTO sale_orders (reservation_id, sale_id, user_id, qty)
VALUES ('0b9e…', 42, 1001, 1)
ON CONFLICT (reservation_id) DO NOTHING;    -- 0 rows: a retried message, stop here and ack it
                                            -- 23505 on one_live_order: a duplicate buyer slipped past the gate,
                                            --   ROLLBACK and run release.lua (that unit went to nobody)
UPDATE sale_stock
SET    sold = sold + 1
WHERE  sale_id = 42 AND sold < total;       -- 0 rows: the gate leaked. ROLLBACK, answer "sold out",
                                            --   and do NOT give the unit back to the gate
COMMIT;                                     -- plus the ledger row, in this same transaction

The primary key on reservation_id makes the worker safe to run twice. Queues deliver at least once, so the same message will arrive again after a crash, and the second delivery must change nothing. With ten workers instead of twenty thousand clients, the lock on sale_stock is contended by ten, not by the whole internet. Why a fresh id per reservation and not (sale_id, user_id)? My first version used the pair as the key, and it has a quiet bug: after an expired hold is released the same person can reserve again, and the worker would read the new message as a retry of the old one. It acks, no order is written, and the unit sits at the gate for nobody. The partial unique index keeps the real rule (one live order per buyer) without that trap. And since the gate only said “you got it” after the enqueue succeeded, a buyer can still hear “sold out” a moment later if the ledger refuses the order. That is honest, and far better than the opposite.

05Where the two tiers disagree

Two stores means two truths, and the design is only as honest as its answer to “what if they diverge?”. Walk through the failures; each has a plain defence.

The Redis row deserves a sentence. Redis replicates asynchronously, so a failover can promote a replica that has not yet seen the last few DECRBYs. That is the same trade-off the leader failover post is about, and the reason this design never lets Redis be the final word on an order. A gate that is occasionally too generous costs you a handful of apologies. A gate that is the only record costs you a hundred and three phones sold.

Giving stock back, exactly once

Returning stock has its own trap. If the expiry sweeper and the payment-failed handler both fire for the same buyer, a careless INCR returns the item twice and the gate now believes in a phone that does not exist. The cure uses the buyers set as a receipt: only the caller whose SREM actually removed something may put the stock back.

-- KEYS[1] = {sale:42}:stock    KEYS[2] = {sale:42}:buyers
-- ARGV[1] = user id             ARGV[2] = qty
if redis.call("SREM", KEYS[2], ARGV[1]) == 1 then   -- only the first release finds the receipt
  redis.call("INCRBY", KEYS[1], ARGV[2])
  return 1
end
return 0                                             -- already released, do nothing

One more piece, easy to forget: Redis is not the only place holding the unit. The order row and the sold counter in Postgres still count it. So the sweeper goes ledger first, gate second, and the ledger step doubles as the race referee when a late payment arrives at the same moment as the expiry:

BEGIN;
UPDATE sale_orders SET status = 'EXPIRED'
WHERE  reservation_id = '0b9e…' AND status = 'PENDING_PAYMENT';         -- 0 rows: already paid, stop
UPDATE sale_stock SET sold = sold - 1 WHERE sale_id = 42;              -- run only if the line above hit 1 row
COMMIT;
-- then call release.lua; its SREM keeps a duplicate sweep from returning the unit twice.
-- If the process dies between COMMIT and release.lua, the row is already EXPIRED and the
-- sweeper will not pick it again: retry release for EXPIRED rows until it succeeds.

The payment side must play by the same rule. Its handler marks the order PAID with the same conditional WHERE status = 'PENDING_PAYMENT'; if that hits 0 rows because the hold already expired, the money has arrived for an order that no longer exists, and the only honest outcome is a refund. One more cost of the hash tag in the gate: it pins a whole sale to one Redis shard. For a gate this small that is fine, but it means the hot SKU is a single-node problem, not a cluster-wide one.

06When not to build this

A Redis gate plus a queue is a lot of moving parts, and most shops never need it. The honest ladder, cheapest first:

07Closing: make “no” cheap

When ninety-nine percent of the traffic is going to lose, the system’s real job is to say no cheaply and say yes carefully.

Duy Anh, notes on flash sales

A candidate who answers “put the stock in Redis” has half of it. The half that earns the job is the follow-up: what happens when Redis and Postgres disagree, how do you return stock once and only once, and why does the worker still check the database? Those three answers are the difference between a design that survived a drop and a slide that looked good in an interview. Next time a drop is announced, I would rather be the doorman than the box office.

• Many buyers, few items? Refuse the losers before they reach the database, in memory, atomically (a Lua script).
• Winners go through a queue and a conditional UPDATE; the database stays the only ledger.
• Every reservation has a deadline, and releasing stock must be idempotent: the receipt is the SREM.
• Use the lowest rung that survives your real peak. Most systems stop at the first.

Interview questions to practice

1. 20,000 users buy 100 items in one second with a single conditional UPDATE. Explain, with numbers, why the whole site goes down.
2. Why does the reserve step need a Lua script rather than GET then DECR in the app? What constraint does Redis Cluster add?
3. Redis fails over and loses the last few decrements. How does your design avoid overselling?
4. How do you return stock for an unpaid reservation exactly once when two code paths can both trigger it?

Written by Vũ Duy Anh, backend engineer in Hà Nội. Questions or corrections are welcome by email.