Two customers buy the last item at the same moment and both orders are confirmed. What happened, and how do you prevent it?

Summary

A lost update: both requests read stock = 1, then each wrote back a value computed in application code. Fix it with an atomic conditional update (UPDATE … SET stock = stock - 1 WHERE id = ? AND stock >= 1 and check affected rows), a row lock, or a version check.

Longer Answer

Read-modify-write in the application (product.stock -= 1; save()) splits the check and the write. Under load, two transactions read the same value before either writes. Neither Read Committed nor MySQL's Repeatable Read prevents this, because the plain SELECT takes no lock.

Options, in order of preference:

1. Atomic conditional update: the database checks and decrements in one statement; zero affected rows means sold out.
2. SELECT … FOR UPDATE when the decision needs several reads.
3. Optimistic version check (WHERE version = ?) when conflicts are rare.
4. Add CHECK (stock >= 0) as a last line of defence, and load-test with concurrent requests on the same item.

Key Points

• Read-modify-write loses updates
• Atomic conditional UPDATE + affected rows
• Locks or versions for multi-step logic
• CHECK constraint as a safety net

Deep dive: Lost Updates