# 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](https://systemdesignwithparam.hashnode.dev/fix-overselling-race-condition), one item was left and many buyers came. The fix was an atomic update, where the check and the change are one query: ```sql 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. ```plaintext 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. ![One row, a long line of buyers, and a counter that serves 200 per second](https://cdn.hashnode.com/uploads/covers/648efffb03546e51ad8d3047/a450ee8f-4667-4926-a5d1-232713e17488.png align="center") *Every buyer waits for the same row lock.* 10 lakh buyers at 200 per second: ```plaintext 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. ![Many buyers read stock 1 from a cache, and all go to the database](https://cdn.hashnode.com/uploads/covers/648efffb03546e51ad8d3047/1f38c470-6aa7-4d34-befd-364f5ef1734c.png align="center") *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. ![Funnel: 10 lakh buyers, Redis gate, queue, database rows](https://cdn.hashnode.com/uploads/covers/648efffb03546e51ad8d3047/cd8cd4a8-83fd-405a-b0b7-9492812e6453.png align="center") *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. ```sql 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. ![One item split into 10 rows of 100 units, each with its own lock](https://cdn.hashnode.com/uploads/covers/648efffb03546e51ad8d3047/7f76d70c-6a76-410d-972c-76d6e5919330.png align="center") *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. ```lua 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 | Fix | Stops the crash? | Why | | --- | --- | --- | | Bigger database | No | Writes to one row can't be split | | More app servers | No | A longer line for the same row | | Cache the stock count | No | A copy can't decide, and writes still reach the database | | Split stock across rows | Partly | About N× throughput, but one row can run dry early | | Redis gate | Yes | Rejects the losers before the database | | Queue | Yes | Turns a spike into a steady load | | Atomic SQL guard | Required | The source of truth if Redis is wrong | **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](https://systemdesignwithparam.hashnode.dev/bloom-filter-username-check), with a Bloom filter. [*Subscribe on YouTube*](https://www.youtube.com/@SystemDesignWithParam) *or follow this blog for the next one.* ## References - [Redis: atomic execution of Lua scripts](https://redis.io/docs/latest/develop/interact/programmability/eval-intro/) - [Redis: replication and asynchronous failover](https://redis.io/docs/latest/operate/oss_and_stack/management/replication/) - [PostgreSQL: explicit locking](https://www.postgresql.org/docs/current/explicit-locking.html) - [MySQL: locking reads](https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html)