Shopify · Inventory Reservation at Scale
How Shopify stops the last item selling to two people at once — and why the fix evolved from a Redis counter to per-unit rows with SKIP LOCKED. Run a flash sale and watch each design break.
Note: this is an illustrative client-side model — the numbers teach the why, they aren't from a real database.
Step 1 of 7
One unit, two buyers, the same millisecond
A flash sale drops. 2,000 people hit checkout for 50 units.
Checkout has to hold stock while payment clears, so the last unit never sells twice. That hold is the entire problem, and four designs solve it four different ways.
- Sold correctly
- —
- Oversold
- —
- Checkouts / sec
- —
- p99 wait (ms)
- —
- Reservation rows
- —
Step 2 of 7
Design 1: hold it in Redis, sell it in MySQL
The hold is a counter in Redis, which is single threaded, so two decrements can never overlap. The real inventory lives in MySQL.
It is fast. Fifty buyers win, the rest see sold out, and nobody waits.
// Redis: atomic, so no two buyers take the same unit
DECR stock:{sku} // -> 49, 48, 47 ...
// MySQL: the actual sale, in a different database
INSERT INTO orders ...one shared counter
- Sold correctly
- 50
- Oversold
- —
- Checkouts / sec
- 950
- p99 wait (ms)
- 40
- Reservation rows
- 1
Fast and correct, as long as nothing crashes. The hold and the sale live in two different databases.
Step 3 of 7
Now crash between the two writes
The payment cleared. Redis dropped the hold. Then the process died before MySQL recorded the sale.
Nothing rolls back, because nothing holds the two writes together. Two databases cannot commit as one.
DECR stock:{sku} // committed in Redis
💥 process dies
INSERT INTO orders // never ran
// no transaction spans both systemsWhat you have learned so far
- Redis is atomic per key, so the hold itself never double-spends.
- Sold correctly
- 50
- Oversold
- 1
- Checkouts / sec
- 950
- p99 wait (ms)
- 40
- Reservation rows
- 1
💥 Oversold. The unit was released in one database and never recorded in the other. No retry fixes a write that already committed somewhere else.
Step 4 of 7
Design 2: move the hold next to the inventory
Put the hold in the same MySQL row as the stock. The hold and the sale now commit in one transaction, so no crash can split them.
Correctness is fixed. Oversold is zero and stays zero.
BEGIN; SELECT qty FROM inventory WHERE sku = ? FOR UPDATE; -- everyone waits here UPDATE inventory SET qty = qty - 1 ...; INSERT INTO orders ...; COMMIT;
What you have learned so far
- Redis is atomic per key, so the hold itself never double-spends.
- Two databases cannot commit together, so a crash between them oversells.
one row, one lock, everybody queues
- Sold correctly
- 50
- Oversold
- 0
- Checkouts / sec
- 14
- p99 wait (ms)
- 8000
- Reservation rows
- 1
Correct, and unusably slow. MySQL hands that row lock out one buyer at a time, so the last buyer waits behind 1,999 others.
Step 5 of 7
Design 3: one row per unit, and skip the locked ones
Give every reservable unit its own row. Buyers lock different rows, and SKIP LOCKED lets a query hop past rows someone else holds instead of waiting behind them.
Commits run in parallel again, and throughput comes back.
SELECT id FROM reservations WHERE sku = ? AND state = 'free' LIMIT 1 FOR UPDATE SKIP LOCKED; -- hop past held rows
What you have learned so far
- Redis is atomic per key, so the hold itself never double-spends.
- Two databases cannot commit together, so a crash between them oversells.
- One transaction fixes correctness, but one row serialises every buyer.
rows grow without limit →
- Sold correctly
- 50
- Oversold
- 0
- Checkouts / sec
- 900
- p99 wait (ms)
- 60
- Reservation rows
- 380,000+
Fast and correct, but a hot item sprouts hundreds of thousands of mostly idle rows, and scanning for a free one keeps getting slower.
Step 6 of 7
Design 4: cap the pool, refill in the background
Cap reservation rows at 1,000 per item. When the pool runs low a background job tops it up, single flight, so only one refill runs no matter how many requests notice.
Parallel, bounded, and because the hold still sits beside inventory, the reservation and the sale commit together.
-- bounded pool, refilled off the hot path SELECT id FROM reservations WHERE sku = ? AND state = 'free' LIMIT 1 FOR UPDATE SKIP LOCKED; -- single-flight top-up when free rows < threshold
What you have learned so far
- Redis is atomic per key, so the hold itself never double-spends.
- Two databases cannot commit together, so a crash between them oversells.
- One transaction fixes correctness, but one row serialises every buyer.
- Row per unit plus SKIP LOCKED restores parallelism, at the cost of unbounded rows.
pool capped at 1,000 rows, refilled in the background
- Sold correctly
- 50
- Oversold
- 0
- Checkouts / sec
- 940
- p99 wait (ms)
- 48
- Reservation rows
- 1000
Correct, parallel and bounded. This is roughly where real inventory systems land.
Four designs, four trade-offs
Each fix created the next problem. That progression is the actual lesson.
| Design | Correct? | Throughput | Rows | Why it fails |
|---|---|---|---|---|
| Redis + MySQL | No | ~950/s | 1 | Two databases cannot commit together |
| One MySQL row | Yes | ~14/s | 1 | Every buyer queues on the same row lock |
| Row per unit | Yes | ~900/s | Unbounded | Hot items sprout hundreds of thousands of rows |
| Capped pool | Yes | ~940/s | 1,000 | Needs a background refill, single flight |
Go deeper
Build systems like this with a mentor reviewing your code
These trade-offs are what senior interviews actually probe. Crio fellowship programs are project based, so you build the thing rather than watch a lecture about it.
Run this simulator free
Sign in once to unlock every simulator. Takes 20 seconds.
Questions people ask about this
What does SELECT ... FOR UPDATE SKIP LOCKED actually do?+
It locks the rows the query returns, and skips any row another transaction already locked instead of waiting for it. That turns a queue of buyers waiting on one row into many buyers taking different rows in parallel. It is the standard way to hand work out of a database table to competing workers.
Why not just use a transaction across Redis and MySQL?+
There is no shared transaction between two separate systems. A two-phase commit protocol can coordinate them, but it adds a coordinator, blocking on failure, and a lot of operational weight. Moving the hold into the same database as the sale is simpler and removes the failure mode entirely.
Why does one row collapse throughput so badly?+
A row lock is held until the transaction commits. If every checkout updates the same row, the database can only process them one at a time, so throughput is capped at roughly one divided by the transaction time, and the wait for the last buyer grows with the length of the queue.
What is a single-flight refill?+
When many requests notice the pool is low, only one of them performs the refill while the rest wait for that result. Without it, a popular item triggers hundreds of simultaneous refills that all insert rows, which is the stampede the cap was meant to prevent.
Are these numbers real measurements?+
No. They are an illustrative client-side model chosen to show the shape of each trade-off. The relationships are what matter: correctness under crash, throughput under contention, and row growth for a hot item.
