A profile currently contains:
Name: Alex
Phone: 555-1000
Version: 7
Two requests arrive almost simultaneously.
Request A changes the name from Alex to Alexander.
Request B changes the phone number from 555-1000 to 555-2000.
Both requests read the profile at Version 7.
Request A saves first.
The database now contains:
Name: Alexander
Phone: 555-1000
Version: 8
Request B still has the copy it originally read:
Name: Alex
Phone: 555-2000
Version: 7
If B writes that stale copy without checking the version, it can replace Alexander with Alex.
A valid update has been lost.
This is the lost update problem.
Why the Problem Happens
The dangerous pattern is:
Read profile
↓
Modify profile
↓
Write profile
The application is working with data that was current when it was read.
But the data may have changed before the write happens.
The database needs a way to determine whether the record is still the same version that the application originally read.
This is where optimistic locking can be used.
Creating the Version
Add a version column to the record.
For a newly created profile, the application or database can initialize it to a value such as:
Version: 1
After each successful update, the version increases:
1 → 2 → 3 → 4
The exact starting value is not important.
The important property is that successful changes produce a new version.
The version update must be coordinated with the data update so that a successful data change does not leave the version unchanged.
Preventing the Lost Update
Use the same profile example.
The profile is currently:
Name: Alex
Phone: 555-1000
Version: 7
Both requests read Version 7.
Request A wants to change the name.
Instead of updating the record unconditionally, it includes the version it originally read:
UPDATE users
SET name = 'Alexander',
version = version + 1
WHERE id = 42
AND version = 7;
The database currently has Version 7.
So the update succeeds.
The profile becomes:
Name: Alexander
Phone: 555-1000
Version: 8
Now Request B attempts to save its phone-number change.
B also originally read Version 7:
UPDATE users
SET name = 'Alex',
phone = '555-2000',
version = version + 1
WHERE id = 42
AND version = 7;
But the database now contains Version 8.
Therefore:
version = 7
is no longer true.
The update affects zero rows.
That is the concurrency signal.
B's copy is stale.
Instead of silently overwriting A's change, the application can:
Reload the latest profile
Merge B's phone-number change
Retry using the latest version
Return a conflict to the caller
For example, after reloading Version 8, the application could produce:
Name: Alexander
Phone: 555-2000
Version: 9
Both changes are preserved.
Why It Is Called Optimistic Locking
The application does not hold a database lock while the user is editing the profile.
Instead, it optimistically assumes that another request will not modify the record first.
At write time, it checks whether that assumption is still true.
If the version matches, the write succeeds.
If the version has changed, the application detects a conflict.
That is why this pattern is commonly called optimistic locking or optimistic concurrency control.
Optimistic Locking vs Atomic Updates
These concepts should not be treated as synonyms.
An atomic update can perform a state change directly against the current database value.
For example:
UPDATE posts
SET view_count = view_count + 1
WHERE id = 42;
This is useful for operations such as counters where the desired change can be expressed directly in the database.
Optimistic locking solves a different problem.
It detects whether the record changed after the application read it.
Both can help with concurrency, but they use different mechanisms.
Transaction and Isolation Considerations
Optimistic locking is also not the same thing as choosing a transaction isolation level.
Read Committed, Repeatable Read, Snapshot Isolation, and Serializable have different concurrency semantics, and exact behavior varies between database systems and configurations.
A transaction may still be necessary when several database operations must satisfy a consistency requirement.
Likewise, optimistic locking can be useful even when an operation is already executed inside a transaction.
The correct mechanism depends on the actual data access pattern.
Where It Is Used
Optimistic locking is useful when internet applications allow concurrent modification of the same resource.
Common examples include:
User profiles
Content management systems
Documents
Shopping carts
API resources
Configuration records
Subscription settings
It is particularly useful when conflicts are possible but relatively uncommon.
Common Mistakes
Adding a Version Column Without Checking It
A version field provides no protection if the update does not include the expected version in its condition.
Treating Zero Updated Rows as Success
If the expected version does not match, the application needs to recognize that the write did not succeed because the state changed.
Incrementing the Version Separately
The data change and version increment should be coordinated. Separating them into independent operations can create inconsistent state.
Assuming Optimistic Locking Solves Every Concurrency Problem
It protects a particular versioned resource.
It does not automatically solve consistency requirements involving multiple records, external services, or distributed workflows.
Ignoring High Contention
If many requests repeatedly update the same record, conflicts can become frequent.
Retries can increase database load and request latency.
A different data model or write strategy may be more appropriate for highly contended resources.
Practical Engineering Guidance
When designing a concurrent update, ask:
Does the request modify data based on a previous read?
Can another request change that record before this write?
Should a stale write be rejected?
What version should be stored and checked?
What should happen after a version conflict?
How will the system behave if the record becomes highly contended?
Optimistic locking is not about preventing concurrency.
It is about detecting stale writes before they overwrite newer state.
Key Takeaway
Optimistic locking uses a version value to determine whether a record changed after it was read.
If the expected version no longer matches, the stale update is rejected instead of silently overwriting newer data.
Common Questions / FAQ
1. How is the version number created?
A version column is added to the record and initialized when the record is created, commonly to 1. Successful updates increment it.
2. Why is the version included in the WHERE clause?
It makes the update conditional on the record still being the version that the application originally read.
3. What happens when the version does not match?
The update affects zero rows. The application can treat that as a concurrency conflict and decide whether to reload, merge, retry, or return an error.
4. Does optimistic locking use database locks?
It does not require the application to hold a database lock while it is working with the record. The database still applies its normal concurrency controls when executing the update.
5. Is optimistic locking the same as an atomic update?
No. Optimistic locking detects stale versions. An atomic update performs a database operation as an indivisible state change. They address different concurrency patterns.
If you have another technical perspective on optimistic locking or preventing lost updates, share it in the comments.
