In this chapter
We'll fix the flash sale's oversold-item bug for good — meeting pessimistic and optimistic locking, and the atomic UPDATE...WHERE pattern that closes the exact gap a race condition needs.
The Problem in Real Life
Mike wants the flash sale relaunched, correctly this time. Sarah realizes the fix isn't about writing smarter code — it's about making sure two people can never both be in the middle of "check, then act" for the same row at the same instant.
She pictures two staff members at two separate registers, both ringing up the store's last umbrella during a sudden downpour. Whoever actually gets there first should win — and the other one should find out immediately, not after both walk away thinking they sold it.
Whoever gets there first should lock the door behind them.
Sarah
Letting Everyone In vs. One at a Time
The bug lived in a gap between two statements
Closing that gap, not rewriting the logic inside either statement, is the actual fix.
Locking has a real cost
A transaction holding a lock makes every other transaction that wants the same row simply wait — too much locking, and a busy system slows to a crawl.
Zero rows affected isn't an error
An UPDATE...WHERE that matches nothing succeeds silently — the application has to explicitly check the affected-row count to know whether its write actually happened.
Not every database locks the same way
SQLite locks the whole database file at once; row-level locking exists in bigger databases, and matters a great deal once traffic grows.
What Do Locking and Atomicity Actually Fix?
Locking closes the exact gap chapter 1's race condition needed — it makes one transaction wait for another to finish touching a row before it's allowed to touch that same row itself. There are two genuinely different ways to get there.
Pessimistic locking explicitly locks a row the moment it's read, so nobody else can read or write it until the lock is released — most databases offer this as SELECT ... FOR UPDATE. Optimistic locking doesn't lock anything at all; instead, it collapses the check and the write into a single atomic statement, and simply checks afterward whether that statement actually did anything.
The Same Two Customers, Fixed
Customer A
UPDATE ... WHERE InStock > 0 runs
Customer B
The exact same statement, held until A's finishes
Customer A's write finishes
InStock now 0, one sale recorded
Customer B's statement finally runs
WHERE InStock > 0 now matches nothing — 0 rows affected
This is optimistic locking in practice: the check (InStock > 0) and the write happen inside one atomic UPDATE statement instead of two separate ones — the database guarantees nothing else can run in the middle of it.
UPDATE ProductsSET InStock = InStock - 1WHERE ProductID = 5 AND InStock > 0;
If two customers' requests both reach this exact statement for the same product, the database runs them one after another, never both at once. Whichever one runs second sees the already-decremented InStock and correctly matches zero rows — nothing left to sell, and the database never has to lock anything to guarantee it.
Notice what actually changed: the SQL isn't cleverer, and nothing about the customers' behavior changed either. What changed is that the check and the write can no longer be pulled apart by a second, overlapping request — a single statement is always atomic, and that's the entire fix.
SQLite itself locks at the level of the whole database file, not individual rows — coarser than the row-level locking a database like PostgreSQL offers, but more than enough to guarantee this exact statement is always safe. GreenMart's flash sale can run again.
Key Takeaway
Locking closes the gap a race condition needs — pessimistically, by explicitly locking a row until a transaction finishes, or optimistically, by collapsing the check and the write into one atomic statement and verifying afterward whether it actually took effect.
Why This Matters
This exact atomic UPDATE...WHERE pattern is one of the most reused fixes in real backend engineering — inventory, seat availability, rate limits, and account balances all lean on the same trick: collapse the check and the write into one statement the database itself can never interrupt.
GreenMart's inventory can no longer be oversold. The next chapter widens the lens from a single UPDATE to entire sequences of statements — and what happens when one of them fails partway through.