Two users can read the same database row, make different changes, and both believe their update succeeded. If the second write silently replaces the first, the application has a lost update.
This is easy to miss because each individual SQL statement can be valid. The bug appears only when multiple requests overlap in time.
One practical way to prevent this is optimistic locking: let readers proceed without holding a database lock, but make every write prove that the row is still the version the writer originally read.
The mental model is:
read row + version
|
v
do work outside the database
|
v
UPDATE ... WHERE id = ? AND version = ?
|
+-- one row updated -> your snapshot was current
|
+-- zero rows updated -> somebody changed it firstOptimistic locking does not eliminate conflicts. It turns silent overwrites into explicit conflicts that application code can handle.
See how a lost update happens
Suppose an application stores document titles:
CREATE TABLE documents (
document_id INTEGER PRIMARY KEY,
title TEXT NOT NULL
);Two requests read the same row:
database: title = "Quarterly Plan"
request A reads "Quarterly Plan"
request B reads "Quarterly Plan"Request A changes the title to:
"Quarterly Plan - Finance"Request B changes it to:
"Quarterly Plan - Final"If both execute an update that identifies only the row:
UPDATE documents
SET title = ?
WHERE document_id = ?;the database can accept both statements. If B runs after A, the final value becomes B’s title.
Nothing in that WHERE clause says, “update this row only if it is still the state I originally read.”
That missing precondition is the core problem.
Add a version column as the write precondition
A simple optimistic-locking design adds a monotonically increasing version number:
CREATE TABLE documents (
document_id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
version INTEGER NOT NULL DEFAULT 1
);A reader fetches both the data and its version:
SELECT
document_id,
title,
version
FROM documents
WHERE document_id = ?;Assume the result is:
document_id = 42
title = "Quarterly Plan"
version = 7The application keeps version = 7 together with the data it is editing.
When it writes, the version becomes part of the condition:
UPDATE documents
SET
title = ?,
version = version + 1
WHERE document_id = ?
AND version = ?;The final placeholder is the version originally read by the client.
If the row is still at version 7, the statement changes it to version 8.
If another writer already changed the row to version 8, the predicate version = 7 no longer matches and the stale update affects zero rows.
The affected-row count is part of the protocol
The SQL statement alone is not enough. Application code must inspect whether the update actually matched a row.
Conceptually:
affected rows = execute conditional UPDATE
if affected rows == 1:
success
else if affected rows == 0:
conflict
else:
invariant violationFor a primary-key update, more than one affected row should be impossible if the schema and predicate are correct.
PostgreSQL reports the number of rows updated in its UPDATE count command tag. Many database drivers expose the same information through an affected-row or row-count API.
A zero-row update is not a database error by itself. In an optimistic-locking protocol, your application deliberately interprets it as a concurrency conflict.
That distinction matters. Treating zero rows as ordinary success would reintroduce the bug at the application layer.
Walk through two competing writers
Start with:
title = "Quarterly Plan"
version = 7Both requests read version 7.
Request A executes:
UPDATE documents
SET
title = 'Quarterly Plan - Finance',
version = version + 1
WHERE document_id = 42
AND version = 7;It matches one row. The database now contains:
title = "Quarterly Plan - Finance"
version = 8Request B then executes its stale update:
UPDATE documents
SET
title = 'Quarterly Plan - Final',
version = version + 1
WHERE document_id = 42
AND version = 7;It matches zero rows because the current version is 8.
Request B has now learned something important: the data it edited is stale.
Instead of overwriting A’s work, it can reload, merge, retry, or ask the user what to do.
Keep the check and update in one SQL statement
A risky design separates the version check from the write:
SELECT version
FROM documents
WHERE document_id = 42;Application code sees version 7 and later runs:
UPDATE documents
SET title = 'Quarterly Plan - Final'
WHERE document_id = 42;Another writer can change the row in the gap between those statements.
The safe pattern makes the condition and mutation part of the same update:
UPDATE documents
SET
title = ?,
version = version + 1
WHERE document_id = ?
AND version = ?;The database evaluates the WHERE condition as part of executing that statement. Your application never relies on an earlier standalone check remaining true.
This is the same general idea as compare-and-swap: change a value only if the observed state still matches an expected state.
Increment the version in the database
Another subtle mistake is calculating the new version only in application code.
Avoid treating this as the important operation:
newVersion = oldVersion + 1The important operation is the conditional database write:
version = version + 1inside the same UPDATE that verifies the old version.
Keeping the increment in the database ties the version transition directly to the successful write. The statement says:
if current version is 7,
apply these changes and make it 8rather than relying on a separate application-side state transition.
Return the new version when the database supports it
Some databases can return values from the updated row.
In PostgreSQL, for example:
UPDATE documents
SET
title = $1,
version = version + 1
WHERE document_id = $2
AND version = $3
RETURNING version;If the statement returns a row, the update succeeded and the application immediately receives the new version.
If it returns no row, the optimistic-lock check failed.
This can be convenient because the write and retrieval of the new version stay in one round trip.
RETURNING support and exact syntax vary by database engine, so use the mechanism provided by the database and driver you actually run.
Decide what a conflict means to the user
Detecting a stale write is only the first half of the design. The application still needs a conflict policy.
Reload and ask the user
For interactive editing, the safest response is often to fetch the current row and show that it changed.
The user can then decide whether to:
- keep the newer database value;
- reapply their own changes;
- merge non-overlapping edits.
This is appropriate when overwriting somebody else’s work would be surprising or costly.
Retry automatically when the operation is repeatable
Some operations can safely retry:
read current row
compute desired change
attempt conditional update
if conflict:
read again
recompute
retryAutomatic retry is appropriate only when the operation can be recomputed from fresh state without changing its meaning.
For example, “increase the retry limit by one” can often be recomputed. “Set this form to exactly the values the user saw five minutes ago” usually should not be silently retried against new data.
Retries should also be bounded. Heavy contention can otherwise turn a conflict into an endless retry loop.
Reject the update
For APIs, returning a conflict response can be the clearest contract.
The caller then decides whether to reload or retry.
The exact transport-level representation depends on the API design; the database-level rule remains the same: zero matched rows means the expected version was no longer current.
Version the whole row or only part of it deliberately
A single row-level version means every protected update conflicts with every other protected update to that row.
Suppose one request changes display_name while another changes timezone. If both use the same row version, one can force the other to retry even though the fields do not overlap.
That is sometimes desirable: the row is treated as one consistency unit.
In other systems, it creates unnecessary conflicts.
Alternatives include:
- splitting independently edited data into separate rows;
- using narrower compare conditions for specific fields;
- merging non-overlapping changes in application code.
The right boundary depends on what must remain consistent together.
Do not add multiple version columns casually. More granular concurrency control can reduce false conflicts, but it also makes invariants and update logic harder to reason about.
Timestamps are usually weaker version tokens
It can be tempting to use updated_at as the optimistic-lock token:
WHERE updated_at = ?This can work in some systems, but an integer version is often easier to reason about.
Timestamp-based tokens introduce questions such as:
- what precision does the database store?
- can two updates receive the same timestamp?
- who generates the timestamp?
- are values normalized consistently between the driver and database?
A version counter has a narrower meaning:
1 -> 2 -> 3 -> 4Each successful protected write advances the token.
If your database provides a native row-version or transaction-token feature with documented semantics, that can also be appropriate. The important property is that the token changes reliably whenever a protected state transition succeeds.
Optimistic locking is different from SELECT FOR UPDATE
Optimistic and pessimistic concurrency control solve related problems with different trade-offs.
A pessimistic approach can lock the row before changing it:
BEGIN;
SELECT
document_id,
title
FROM documents
WHERE document_id = 42
FOR UPDATE;
-- compute and write the change
UPDATE documents
SET title = 'Quarterly Plan - Final'
WHERE document_id = 42;
COMMIT;In PostgreSQL, SELECT ... FOR UPDATE takes a row-level lock that blocks conflicting writers and lockers until the transaction ends.
That is useful when the decision and update must happen inside one short transaction and conflicts are common enough that waiting is preferable to retrying.
Optimistic locking instead allows both readers to proceed and detects the loser at write time.
A useful comparison is:
optimistic:
no long-lived row lock while user/app works
conflict discovered at write time
caller may need retry or merge
pessimistic:
conflicting writers wait on a lock
transaction holds database resources longer
useful when conflicts are frequent or retries are expensiveNeither strategy is universally better.
Do not hold a database transaction open for human think time
A common reason to prefer optimistic locking in web applications is that a user may open an edit page and submit it minutes later.
Holding a row lock for that entire period would be a poor design. It would keep a transaction open while the application waits on a human, increasing contention and consuming database resources.
Optimistic locking lets the application:
- read the row;
- end the database interaction;
- let the user edit;
- attempt a conditional update later.
The version token connects those two moments without requiring a lock to remain held between them.
Multi-row invariants need more than a row version
A version column protects the state represented by the row or rows included in the conditional write. It does not automatically protect arbitrary cross-row business rules.
Imagine a rule such as:
the total reserved quantity across several rows must not exceed inventoryChecking one row’s version may not detect changes to other rows that also affect the total.
For multi-row invariants, you may need stronger transaction isolation, explicit locking, atomic constraint techniques, or a different data model.
Optimistic locking is a precise tool for stale-write detection. It is not a substitute for understanding transaction boundaries and database constraints.
Deletes need the same stale-state decision
Updates are not the only operation that can race.
If a client read version 7 and later wants to delete that exact version of the row, the delete can also include the token:
DELETE FROM documents
WHERE document_id = ?
AND version = ?;A zero-row result again means the expected state no longer exists.
Whether the application should treat “already deleted” differently from “modified since reading” depends on the product semantics. If that distinction matters, reload or use additional information to classify the conflict.
Common mistakes
Updating by primary key but forgetting the version
This identifies the object but not the state the writer observed.
Use both:
WHERE document_id = ?
AND version = ?Ignoring a zero-row update
The conditional update is useful only if the application checks the result. Zero rows is the conflict signal.
Performing a check-then-write sequence
A standalone version check can become stale before the later update. Put the expected version in the mutation’s WHERE clause.
Retrying every conflict blindly
A retry changes the state against which an operation runs. Retry only when recomputing the operation from fresh data preserves its intended meaning.
Assuming a row version protects unrelated rows
It does not. Cross-row invariants require transaction-level reasoning beyond a token on one record.
When optimistic locking fits well
Optimistic locking is a strong fit when:
- conflicts are relatively uncommon;
- reads and writes are separated by application work or user think time;
- stale overwrites would be harmful;
- the caller can reload, merge, retry, or report a conflict;
- holding database locks across the full workflow would be impractical.
Pessimistic locking may be a better fit when competing writes are frequent, the critical section is short, and making one writer wait is cheaper or simpler than repeatedly aborting and retrying work.
Some systems use both approaches in different workflows.
Make stale state explicit
The most important idea in optimistic locking is not the version column itself. It is the decision to make the state you observed part of the write precondition.
A write changes from:
update row 42to:
update row 42 only if it is still version 7That small change closes the gap that allows a stale client to silently overwrite a newer value.
Read the version with the data, include it in the mutation’s WHERE clause, advance it in the same statement, and treat zero affected rows as a real concurrency conflict.
When conflicts are rare, that pattern provides a simple way to protect user and application changes without keeping database locks open while work happens elsewhere.