# 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.

```java
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:

![Buyers A and B both read stock 1, and both place an order](https://cdn.hashnode.com/uploads/covers/648efffb03546e51ad8d3047/8ac1e07e-a6cb-4b8d-98a1-d0b79f4a88d0.png)
_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:

```sql
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`

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

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

![Server 1 and server 2 have separate locks, and both reach the same database row](https://cdn.hashnode.com/uploads/covers/648efffb03546e51ad8d3047/d72c616f-e68d-49f7-bb41-06f2ff1b6c5a.png)
_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.

```sql
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.

![Buyer A holds the lock, and the rest wait in line](https://cdn.hashnode.com/uploads/covers/648efffb03546e51ad8d3047/1f86f5d8-4ff1-4586-a55e-076a60bfaffc.png)
_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.

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

Then look at how many rows changed:

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

![Buyer A changes 1 row and gets the order, buyer B changes 0 rows and sees out of stock](https://cdn.hashnode.com/uploads/covers/648efffb03546e51ad8d3047/76f87be1-085e-422d-9cb6-ccd98d6d8c41.png)
_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:

```sql
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.

![Buyers A and B both hold a shared lock, and each waits for the other](https://cdn.hashnode.com/uploads/covers/648efffb03546e51ad8d3047/3476d117-ab71-418e-860b-a1209f66aeee.png)
_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

```sql
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

```sql
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](https://www.youtube.com/@SystemDesignWithParam) or follow this blog so you don't miss it._

## References

- [PostgreSQL: transaction isolation](https://www.postgresql.org/docs/current/transaction-iso.html)
- [PostgreSQL: SELECT FOR UPDATE](https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE)
- [MySQL: transaction isolation levels](https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html)
- [MySQL: locking reads](https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html)
- [MySQL: deadlocks](https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlocks.html)
- [PostgreSQL: deadlocks](https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-DEADLOCKS)
- [MySQL: CHECK constraints](https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html)