1 Item Left, 1000 Buyers, Sold 3 Times: How to Fix Overselling?

A sale is running on Amazon. One iPhone is left. 1000 people place an order at the same time.

We confirm 3 orders. There is no crash and no error.

This post shows why it happens, which fixes don't work, and which one does.

https://www.youtube.com/watch?v=vVSmx2NRjyg

---

TL;DR

• Checking the stock and then changing it are two steps. Two buyers can land in the gap.
• A transaction, synchronized and an idempotency key all sound right, but none of them closes this gap.
• SELECT FOR UPDATE stops the overselling, but it makes every buyer wait.
• The best fix: an atomic update, where the check and the change are one query.
• A shared lock doesn't help: it causes a deadlock.

---

The problem

The basic implementation looks like this. Check the stock. If it's above 0, reduce it and place the order.

int stock = db.getStock(itemId);          // check
if (stock > 0) {
    db.setStock(itemId, stock - 1);       // change
    createOrder(user);
}

It works perfectly when you test it, because you are the only one clicking Buy.

Now two users send requests at the same time:

Both buyers saw 1 before anyone wrote 0.

Stock says 0, but there are 2 orders. This is a race condition: the result depends on whose request runs when.

---

Fixes that sound right

1. Use a transaction

A transaction means all or nothing. It never says one at a time.

On default settings, PostgreSQL uses READ COMMITTED and MySQL uses REPEATABLE READ. In both, a plain SELECT takes no lock, so both buyers still read 1:

BEGIN;
SELECT stock FROM items WHERE id = 42;   -- A and B both read 1
UPDATE items SET stock = 0 WHERE id = 42;
COMMIT;                                  -- both commit, no error

Both transactions succeed, so you still get 2 orders.

2. Use synchronized

public synchronized void buy(long itemId, User user) { ... }

This does solve it, if you have one server. synchronized is a lock inside one JVM.

Add a second server, and the lock no longer covers every buyer.

3. Use an idempotency key

An idempotency key is a unique ID sent with a request. If the same ID comes again, the server doesn't repeat the work.

So it does stop one user placing the same order many times, like a double-click.

But here we have 1000 different users. That's 1000 different keys, and every request looks new.

---

A fix that works, but is slow: SELECT FOR UPDATE

Idea: take a lock on the row when you read it. Every other buyer waits until you finish.

BEGIN;
SELECT stock FROM items WHERE id = 42 FOR UPDATE;  -- others wait here
-- your app checks stock > 0
UPDATE items SET stock = stock - 1 WHERE id = 42;
INSERT INTO orders ...;
COMMIT;                                            -- now the next buyer goes

Requests for that row now run one at a time, so there is no way to create extra orders.

The lock stays held while your app talks to the database.

The catch: your system gets slow. Each buyer holds the lock across several round trips between the app and the database, and everyone else waits.

---

The best fix: atomic update

Idea: put the check inside the update, so both happen as one step.

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

Then look at how many rows changed:

int rows = db.update(sql, itemId);
if (rows == 1) {
    createOrder(user);    // stock was above 0
} else {
    outOfStock();         // rows == 0
}

There is no gap left between the check and the change.

If A and B arrive together, the database makes B wait for A's row lock. It then checks stock > 0 again, finds 0 and changes nothing.

Your app never holds a lock while it decides anything, so the wait is much shorter than with SELECT FOR UPDATE.

---

Beyond the short

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

Why not use a shared lock?

FOR SHARE looks like a lighter version of FOR UPDATE. It isn't safe here:

BEGIN;
SELECT stock FROM items WHERE id = 42 FOR SHARE;   -- A and B both get this
UPDATE items SET stock = stock - 1 WHERE id = 42;  -- both wait here

A shared lock lets many readers in. But an UPDATE needs an exclusive lock, and it can't get one while another buyer holds a shared lock.

This is a deadlock: each buyer waits for the other, forever.

The database spots this and aborts one transaction with an error. Nothing is oversold, but buyers get errors under load, so your code must retry.

Carts can deadlock too

Row locks can also deadlock when an order has more than one item, and this includes the atomic update:

Time	Buyer A	Buyer B
t1	locks iPhone	locks case
t2	waits for case	waits for iPhone

Fix: always update items in the same order, like sorted by ID. Then B waits at the iPhone and never holds the case.

If any update changes 0 rows, roll back the whole transaction, so you don't sell half a cart.

Keep the order in the same transaction

BEGIN;
UPDATE items SET stock = stock - 1 WHERE id = 42 AND stock > 0;
INSERT INTO orders ...;   -- only if 1 row changed
COMMIT;

If the server crashes between two separate steps, the unit is gone and nobody has an order. The row lock stays until COMMIT, so keep this transaction short.

Never call payment inside it

Payment can take 2 seconds. If you hold the row lock during that call, the next buyer waits 2 seconds, and that item sells once every 2 seconds at best.

Add a safety net

ALTER TABLE items ADD CONSTRAINT stock_not_negative CHECK (stock >= 0);

If a bug somewhere skips the WHERE, the database rejects the write instead of selling stock you don't have. PostgreSQL enforces this, and so does MySQL from 8.0.16.

---

All the options

Fix	Stops overselling?	Why
Transaction	No	All or nothing, not one at a time
synchronized	Only on one server	The lock lives inside one JVM
Idempotency key	No	Stops repeat requests, not different users
SELECT FOR UPDATE	Yes	Slow, because every buyer waits
Atomic update	Yes	The check and the change are one step

Overselling isn't a stock problem. It's the gap between the check and the change.

This is Day 2 of my system design shorts. Next question: 1000 products left, and 1 million people order at the same time. Even if one update takes just 5 ms, how long does that take, and how do you fix it?

Hint: every buyer queues on one row. At 5 ms each, that's at most 200 updates per second, so most buyers should never reach that row.

That's Day 3. Subscribe on YouTube or follow this blog so you don't miss it.

References

• PostgreSQL: transaction isolation
• PostgreSQL: SELECT FOR UPDATE
• MySQL: transaction isolation levels
• MySQL: locking reads
• MySQL: deadlocks
• PostgreSQL: deadlocks
• MySQL: CHECK constraints