Flash sale system design: why the atomic update dies at 10 lakh buyers

It's midnight on a Big Billion sale. The iPhone drops, 1000 units, and 10 lakh people press Buy in the same second.

The site goes down. Not because the code is wrong, but because the code is right.

This post shows why the fix from Day 2 hits a wall at this scale, and how to get past it.

https://youtube.com/shorts/w1yBCTJDAYI

TL;DR

• The atomic update from Day 2 never oversells, but every buyer queues on one row. At 5 ms per update, that's about 200 purchases per second.
• 10 lakh buyers would need about 83 minutes. Timeouts and retries kill the database long before.
• Bigger database, more servers and a cached stock count don't fix it.
• The real fix: only 1000 buyers can win, so reject the other 99.9% before they reach the database.
• Layers, in order: split the stock across rows, a Redis gate, a queue. Keep the atomic SQL as the final guard.

Where Day 2 left us

On Day 2, one item was left and many buyers came. The fix was an atomic update, where the check and the change are one query:

UPDATE items
SET stock = stock - 1
WHERE id = 42 AND stock > 0;

If 1 row changed, the buyer got the item. If 0 rows changed, it's sold out. Nothing is oversold.

That is correct. Now run it for 10 lakh buyers.

The problem

1. One row is one queue

A row lock lets one writer in at a time. Everyone else waits for it, like one billing counter with a very long line.

Throughput is how many purchases finish per second. With one row, it equals 1 divided by how long the lock is held.

lock held for 5 ms  ‚Üí  1000 ms / 5 ms  =  200 purchases per second

The lock is held for the update, the order insert and the commit. Adding more app servers doesn't change that, because they all wait on the same row.

Every buyer waits for the same row lock.

10 lakh buyers at 200 per second:

1,000,000 / 200 = 5,000 seconds ≈ 83 minutes

The sale of 1000 units is over in seconds, but the database keeps working through a line it will never finish.

2. The whole site goes down

The database has a connection pool: a fixed set of open connections, say 100, that the app shares.

Each buyer takes a connection and then waits for the row. Within a second, all 100 connections are waiting on one row. Login, search and every other product page now have no connection left. The whole site stalls, not only this item.

3. Retries make it worse

Waiting requests hit their timeout and fail. Users press Buy again, and apps retry automatically. A retry storm is when those retries add load to a system that is already down.

Load goes up as the database gets slower, and that is how it dies.

Fixes that sound right

1. Bigger database, read replicas

A read replica is a copy of the database that serves reads. This is a write problem on one row, and writes can't be split across machines. A bigger machine holds the lock for a little less time, and that's all.

2. More app servers

More servers only means more buyers waiting in the same line.

3. Cache the stock count

A cache is a fast copy of a value. The idea is: check the cache first, and only go to the database if it says there's stock.

The cache says stock = 1 until it refreshes, so a crowd reads 1 at the same moment. Reading a copy and then buying is the same check-then-act gap from Day 2, only wider.

A copy can't decide a purchase.

You end up in one of two places:

• If the cache decides the purchase, you oversell.
• If the atomic SQL still decides, nothing is oversold, but every buyer still reaches the database. The cache removed no write load, and that was the problem.

A cache is a copy and can't make the decision. The purchase has to be decided in one place, in one step. That is exactly what the Redis gate below does.

The real fix: stop the losers early

Only 1000 buyers can win. The other 999,000 requests are doomed, so they should never touch the database.

Don't make the database faster. Make most requests never reach it. Add these layers one at a time, and only when you need them.

Each layer rejects more buyers before the next one.

1. Split the stock across rows

Instead of 1 row with 1000 units, keep 10 rows with 100 units each. Each buyer picks a row at random and runs the same atomic update on it.

UPDATE item_stock
SET stock = stock - 1
WHERE item_id = 42 AND shard = 7 AND stock > 0;

• ‚úÖ 10 rows give about 10√ó the throughput, around 2,000 purchases per second.
• ‚ùå One row can hit 0 while others still have stock. Retry on another row before saying sold out.

Ten rows mean ten lines instead of one.

2. A Redis gate

Redis keeps the data in memory, so it answers far faster than a row lock. It runs a Lua script as one atomic step, so nobody can slip in between the check and the decrement.

local stock = tonumber(redis.call('GET', KEYS[1]))
if stock and stock > 0 then
  redis.call('DECR', KEYS[1])
  return 1   -- go ahead to the database
end
return 0     -- sold out, reply right away

Before the sale, load the counter with 1000. Now only the first 1000 buyers get a 1 and move on. Everyone else gets "sold out" straight from Redis, and the database never sees them.

This is different from the cached copy. There, a buyer reads a copy and then acts. Here, Redis makes the decision itself, in one step.

3. A queue

A queue holds work until a worker is ready for it. Buyers who passed the gate don't write to the database directly. They go into a queue, and workers process it at a steady rate. The user sees "Processing your order".

• ‚úÖ The database sees a steady load, however big the spike is.
• ‚ùå The user waits for the result. Cap the queue size, and reject buyers once it's full.

4. Keep the atomic SQL as the final guard

Don't remove WHERE stock > 0 because Redis already checked. Redis copies its data to a replica asynchronously, meaning the replica can lag behind. If Redis fails over to that replica, it can forget the latest decrements and let extra buyers through.

The database is the source of truth. Redis is only the gate in front of it.

Try it

Drag the sliders. Start with 1 row, then split the stock, then add the Redis gate.

https://codepen.io/systemdesignwithparam/pen/VYpEoGY

Beyond the short

A few things I couldn't fit in the video, and interviewers like to ask them.

What if Redis crashes mid-sale?

Rebuild the counter from the database: stock minus the pending orders. While it rebuilds, send buyers to a "please wait" page. Because the SQL guard is still there, the worst case is that some buyers are rejected too early. Nothing is oversold.

Is it first come, first served?

Not exactly. With random shard picks and parallel workers, order isn't guaranteed. If fairness matters, use one ordered queue, so you accept lower throughput. State the trade-off in the interview.

Payment: reserve, pay, release

Don't call payment while holding the stock. Reserve the unit for a few minutes, take the payment, then confirm. If payment fails or the time runs out, release the unit back so the next buyer can have it.

A waiting room

A waiting room lets buyers into the site gradually, say a few thousand at a time. The spike never reaches your servers at full size.

Interview follow-ups

• Redis fails over and loses a decrement. How do you still avoid overselling?
• 1000 units split over 10 rows, and 3 rows sell out first. What does the next buyer see?
• How do you stop one user from grabbing 5 units with bots?

All the options

A flash sale isn't a database speed problem. It's a problem of letting too many doomed requests in.

This is Day 3 of my system design shorts. Day 4 is live: how Instagram checks 1 billion usernames and tells you in milliseconds if yours is free, with a Bloom filter.

Subscribe on YouTube or follow this blog for the next one.

References

• Redis: atomic execution of Lua scripts
• Redis: replication and asynchronous failover
• PostgreSQL: explicit locking
• MySQL: locking reads