Database reasoning · 3 / 4
Prevent overselling inventory
Your challenge
Two customers buy the last item at the same time. A read-then-write implementation sells both. Fix it and explain the trade-offs.
Try it first. Write down your assumptions and explain your reasoning.
UPDATE inventory
SET stock = stock - 1
WHERE product_id = $1 AND stock > 0
RETURNING product_id, stock;1.Avoid a separate unchecked read
Reading stock=1 and later writing stock=0 allows both requests to believe they succeeded. Use an atomic conditional update that decrements only when stock is positive. Treat zero affected rows as sold out.
2.Keep related changes atomic
Insert the order and decrement stock inside one database transaction. If order insertion fails, roll back the stock change. A database check that stock is non-negative gives an additional invariant, but it does not replace the conditional update.
3.Separate reservation from payment
Slow external payments should not hold a row lock indefinitely. Create a reservation with a clear expiry policy and make expiration and confirmation transitions atomic and idempotent. Decide what happens when payment succeeds after expiry. A distributed lock alone does not solve database correctness or process crashes.
Take it one step further
- 1.How do reservations expire safely?
- 2.When would optimistic version checks help?
Self-review
Can you explain each point without looking at the solution?
- Atomic condition instead of unchecked read
- Order and decrement in one transaction
- Explicit payment and reservation failure handling