Concurrency Control: Inventory, Flash Sale & Overselling

1000 phones and 10 lakh buyers: not a single extra phone should be sold, and the system should not crash either.

If two users buy the last item together, both see stock 1 and both orders go through; this is called overselling.
In a flash sale thousands of writes per second hit a single product row, and DB row lock contention slows everything down.
Overselling means cancelled orders, refunds, customer complaints and brand damage.
In bookings like IRCTC Tatkal, lakhs of people click together for limited seats, and fairness is needed too.
Race conditions do not show up in testing, only under real traffic, so they must be solved at the design level.

Pessimistic locking: SELECT stock FROM items WHERE id=1 FOR UPDATE; lock the row, then check and update. Safe but slow under high contention.
Optimistic locking: a version column; UPDATE items SET stock=stock-1, version=version+1 WHERE id=1 AND version=5; if 0 rows are updated, retry.
Atomic conditional update: UPDATE items SET stock=stock-1 WHERE id=1 AND stock>0; check + decrement in a single statement.
Redis atomic DECR / Lua: keep stock in Redis, check stock>0 then DECR in a Lua script; Redis is single-threaded so it is atomic.
Queue-based ordering: put all buy requests in Kafka/SQS and let a single consumer per product process them sequentially, giving FIFO fairness.
Reservation with TTL: hold the item in the cart for 10 minutes; if payment does not happen, release the stock back.
Stock sharding: split 1000 stock into 10 buckets of 100 each so the whole load does not land on one hot key.
Async DB sync: fast decrement in Redis, then final order and stock update in the DB through a queue (eventual consistency).

Wrong: read-then-write race

Overselling: 1 phone, 2 orders; cancellations, refunds, complaints. Doing GET and then DECR in Redis is the same mistake.

Right: atomic conditional update

1.UPDATE items SET stock = stock-1 WHERE id=1 AND stock > 0 (check + decrement together)
2.User A: 1 row updated, OK
3.User B: 0 rows updated, Sold Out

Options for low traffic or low conflict: Pessimistic (SELECT ... FOR UPDATE, safe but slow) and Optimistic (WHERE version=5, retry on 0 rows).

Flipkart Big Billion Day: 1000 phones, 10 lakh buyers

1.10 lakh users press Buy Now at 12:00
2.Waiting Room (Queue-it / Cloudflare) lets them in at a controlled rate
3.Redis Lua atomic: stock:phone123 = 1000; if stock>0 then DECR + token, else Sold Out (9.99 lakh users get an instant answer, DB stays safe)
4.Only the 1000 orders holding a token go into the Kafka orders topic (sequential)
5.Order Service: payment + confirm; MySQL final guard: UPDATE stock=stock-1 WHERE id=1 AND stock>0
6.Reservation TTL 10 min: on payment failure / timeout, Redis INCR returns stock to the pool

Result: 0 overselling, only ~1000 writes on the DB.

Tool	Type	How it is used
Redis (DECR, Lua scripts)	In-memory store	Flash sale stock counter; check-and-decrement is atomic in a Lua script, lakhs of ops per second.
MySQL / PostgreSQL row locks	Relational DB	Strong correctness for normal traffic with SELECT FOR UPDATE or a conditional UPDATE.
Apache Kafka / AWS SQS FIFO	Message queue	Serialize orders in a queue for ordered processing and protect the DB from spikes.
DynamoDB conditional writes	NoSQL DB	UpdateItem with ConditionExpression stock > 0, atomic and serverless scale.
Redisson / ZooKeeper locks	Distributed lock	A per-product lock when multi-step logic must be atomic, but a bottleneck under high traffic.
Virtual waiting room (Queue-it, Cloudflare Waiting Room)	Traffic gate	Hold users in a queue on the sale page and let them in at a controlled rate.

Point	Pessimistic (FOR UPDATE)	Optimistic (version)	Redis DECR / Lua	Queue-based
Throughput	Low, lock wait	Medium, retries on conflict	Very high, lakhs/sec	High, async
Correctness	Strong	Strong	Strong in Redis, DB sync is separate	Strong, sequential
High contention	Long lock queue	Retry storm	Handles it best	Good, absorbs the spike
User response	Immediate	Immediate or retry	Immediate	Delayed, pending status
Complexity	Low	Low-Medium	Medium, Redis-DB sync	High, async flow
Best for	Low traffic, bank transfer	Normal e-commerce, low conflict	Flash sale counter	Ticket booking, fairness

Pessimistic lock: when traffic is low and conflict is likely, e.g. account balance update.
Optimistic lock: normal product pages where conflict is rare.
Redis atomic DECR/Lua: flash sale where you need an instant yes/no under very high concurrency.
Queue-based: when fairness (first come first served) and DB protection matter, e.g. Tatkal, concert tickets.
Real systems use a combo: waiting room + Redis gate + queue + DB conditional update as the final guard.

Real world: Flipkart Big Billion Day phone flash sale (a hypothetical scenario)

At 12 o clock, 1000 phones are on a Rs 1 deal and 10 lakh users press Buy Now together.
First, a virtual waiting room lets users onto the sale page at a controlled rate.
Stock lives in Redis as stock:phone123 = 1000. Lua script: if stock>0 then DECR and return the user a reservation token, else Sold Out.
Only 1000 users get a token; the other 9.99 lakh get an instant Sold Out and their requests never reach the DB.
Orders with tokens go into Kafka; the order service does the final confirmation in the DB with UPDATE stock=stock-1 WHERE stock>0.
Each reservation lasts 10 minutes; on payment failure or timeout, stock is returned with INCR in Redis.
Result: 0 overselling, only ~1000 writes on the DB, the site stays stable.

Doing read-then-write in separate statements (SELECT stock then UPDATE) without a lock is the classic race condition.
Doing GET then DECR as two separate Redis commands; use atomic Lua or check the DECR result.
Sending all flash sale traffic straight to the DB exhausts the DB connection pool.
Interview tip: explain the optimistic vs pessimistic trade-off and the Redis + queue + DB final guard architecture.