Version Columns Turn Lost Updates into Detectable Conflicts

A read-modify-write flow can overwrite another committed change even when every individual database statement succeeds. Two clients read the same row, compute different replacements, then write in sequence. Without a condition tying each write to the state it read, the later write can silently erase the earlier one.

A version column makes that dependency explicit. The client reads both data and version, then updates only if the stored version is still the one it observed. A changed version turns the race into a failed conditional update instead of a lost update.

An unconditional update forgets the read state

Suppose an account setting starts at version 7. Clients A and B both read it before either writes.

A reads value=X, version=7
B reads value=X, version=7

A computes value=Y
B computes value=Z

If both issue unconditional updates, B can commit after A and replace Y with Z. The database has no predicate showing that B’s computation depended on version 7.

The problem is not concurrent reads. It is a write that does not verify whether its input state remains current.

The version becomes part of the write predicate

A typical optimistic update includes the observed version in the WHERE clause and increments it in the same statement:

UPDATE settings
SET value = :new_value,
    version = version + 1
WHERE id = :id
  AND version = :expected_version;

A writes with expected_version = 7, updates one row, and moves the row to version 8. B then writes with the same expected version and updates zero rows.

A: version 7 -> 8  accepted
B: expects 7       conflict

The affected-row count is part of the protocol. Treating zero rows as success discards the conflict signal the predicate was designed to create.

Comparison and mutation must be one atomic operation

Reading the current version in one query and issuing an unconditional update in a later query does not provide the same guarantee. Another transaction can commit between those statements.

The database must evaluate the expected version as part of the mutation that changes the row. Equivalent mechanisms include compare-and-swap operations, conditional writes, or an ORM’s optimistic concurrency feature when it emits the required predicate.

The important property is atomicity at the storage boundary, not the spelling of the API.

Conflict detection does not choose a merge policy

A rejected update says that the state changed after the client read it. It does not say whether the new request should be discarded, retried, merged, or shown to a person.

A retry that simply rereads and reapplies a replacement can still destroy meaningful changes. Safe retry behavior depends on the operation. Incrementing a counter, replacing a profile document, and changing one field of a structured record have different merge semantics.

Application code should therefore treat an optimistic conflict as a distinct outcome rather than hiding it behind an unconditional retry loop.

The version must change for every protected mutation

A version predicate is only as complete as the mutation paths that maintain it. If one code path updates protected columns without incrementing the version, another client can hold an old snapshot whose version still appears current.

Database triggers, administrative scripts, background jobs, bulk updates, and alternate services all need the same concurrency contract when they modify the protected state.

A timestamp can serve as a version only when its precision, update rules, and comparison semantics make collisions impossible for the required workload. A monotonically incremented integer is often easier to reason about because every accepted mutation produces a distinct next value.

Version scope follows conflict scope

One version for an entire row means independent field edits conflict even when they could have coexisted. That conservative behavior is often acceptable and keeps the rule simple.

Larger documents may use finer-grained versions when unrelated sections can be updated independently. Finer scope reduces false conflicts but adds metadata and makes multi-field invariants harder to protect.

The version boundary should match the state that must be observed and replaced consistently.

Deletes need the same conditional contract

Deletion can race with modification just as updates can. A delete based on an earlier read should include the observed version when preserving intervening edits matters:

DELETE FROM settings
WHERE id = :id
  AND version = :expected_version;

Zero affected rows then means the record disappeared or changed before the delete committed. The application can distinguish those cases with additional state if its behavior requires that distinction.

Optimistic concurrency trades blocking for explicit conflicts

Version columns do not prevent two clients from working at the same time. They allow both to proceed and force the storage layer to accept only a mutation based on the current version.

That makes the pattern suitable when conflicts are relatively uncommon and holding a database lock across user interaction or remote work would be expensive. Under heavy contention, repeated conflicts can waste work, so serialized execution or a different data model may be a better fit.

The core contract remains small: read the version with the data, include that version in the mutation predicate, advance it atomically on success, and handle zero affected rows as a concurrency outcome. With those pieces intact, an overwrite race becomes visible before it can silently discard a committed change.