Imagine an online shopping application with 10 items in stock.
Two customers place an order at almost the same time.
Request A reads:
stock = 10
Request B also reads:
stock = 10
Both subtract 1 and save:
stock = 9
The final value is 9, even though two items were ordered.
One update has been lost.
This is a lost update.
Why Does It Happen?
The dangerous pattern is:
Read → Change in application → Write
Two requests can read the same old value before either one writes.
The problem becomes more common when many requests modify the same database row concurrently.
The Practical Solution
Instead of reading the stock first:
UPDATE products
SET stock = stock - 1
WHERE id = 42
AND stock > 0;
Then check the result.
1 row updated → update succeeded.
0 rows updated → the condition was not satisfied.
This pattern is useful for counters, inventory, quotas, and similar operations.
What About Complex Updates?
Suppose a user edits a profile.
You read the profile when:
version = 7
Another request changes it.
Now:
version = 8
Your request is working with stale data.
This is where optimistic locking helps.
The application saves the version it originally read and only updates the record if that version is still current.
If it is no longer current, the update fails instead of silently overwriting newer data.
The application can then reload the latest version or ask the client to retry.
When Should You Use Locks?
For operations that require serialized access, a transaction with an appropriate row lock can be useful.
But locks have a cost:
- More waiting
- More contention
- Higher latency
- Possible deadlocks
Keep locked transactions short.
One Important Mistake
Don't assume:
"It's inside a transaction, so lost updates cannot happen."
That is not always true.
The result depends on the database, isolation level, locking behavior, and the actual SQL pattern.
MVCC, Read Committed, Repeatable Read, Snapshot Isolation, and Serializable are not interchangeable concepts.
Practical Rule
Simple update → Atomic SQL
Stale data must be detected → Optimistic locking
Complex operation needs serialization → Short transaction + appropriate locking
Key Takeaway
Don't let concurrent requests silently overwrite each other.
Make simple updates atomic and make conflicts visible.
What approach would you use for concurrent updates? Share your technical perspective in the comments.
