A database client can read a row, spend time computing a change, then issue an UPDATE after another transaction has already changed the same row. If the final statement identifies the row only by its primary key, the later write can replace state derived from the intervening transaction without any visible conflict.
A version column changes that boundary. The client reads both the application state and a revision value, then includes that revision in the update predicate. The database accepts the write only while the stored revision still matches the state the client observed.
The predicate carries the observation into the write
Consider an account preference row with a monotonically increasing version column:
SELECT theme, locale, version
FROM user_preferences
WHERE user_id = 42;Suppose the query returns version 7. The later mutation can make that observation part of its condition:
UPDATE user_preferences
SET theme = 'dark',
version = version + 1
WHERE user_id = 42
AND version = 7;If no competing transaction changed the row, the statement matches it and advances the version to 8. If another committed writer already advanced the version, the predicate no longer matches. The stale statement updates zero rows instead of overwriting the newer state.
The mechanism is therefore not a lock held from the initial read to the final write. It is a conditional state transition evaluated when the database executes the UPDATE.
The affected-row count is part of the concurrency contract
The application must inspect the result of the conditional statement. A zero-row result is not equivalent to successful persistence. It means the expected state was absent when the write predicate was evaluated.
That distinction needs to survive abstraction layers. An ORM that exposes a convenient save() operation still has to translate the version mismatch into a detectable concurrency outcome. Treating zero affected rows as ordinary success discards the signal that makes the version check useful.
The application can then choose a policy appropriate to the mutation. It may return a conflict to the caller, reload current state and recompute, or abandon the operation. Automatic retry is valid only when the operation can be recomputed against fresh state without changing its intended semantics.
A version must change on every protected mutation
The comparison works only if every write covered by the concurrency contract changes the token. If one code path modifies protected columns without advancing version, a client holding the older token cannot detect that mutation.
This creates an ownership requirement around the row. SQL issued by application code, background jobs, administrative tools, triggers, and other writers must agree on which mutations participate in versioning.
The increment does not need to encode elapsed time. Its purpose is to distinguish successive protected states. An integer counter is convenient because the transition can occur in the same statement as the data update. Other tokens can serve the same role if the database and application maintain equivalent atomic comparison semantics.
Read isolation and version checking solve different problems
A version predicate does not replace transaction isolation. Isolation defines visibility and interaction among database operations. The version predicate defines an application-level precondition for a particular mutation.
Under a common read-then-write flow, the initial read may happen in one transaction or request and the update much later in another. No database lock spans that interval. The version value is the durable observation carried across the gap.
Inside a larger transaction, isolation rules still apply to every statement. A database may also detect conflicts itself at a given isolation level. The application must handle those database errors separately from a zero-row version mismatch; they arise from different mechanisms even when both prevent a stale result from committing.
Version columns protect the row represented by the predicate
A single row version is naturally scoped to state stored in that row. It does not automatically protect an invariant involving several rows.
Suppose a decision depends on two records, but the final statement checks the version of only one. A concurrent transaction can change the other record without invalidating that predicate. The conditional update can then succeed even though the multi-row assumption used by the application is stale.
Cross-row invariants need a boundary that covers all relevant state: stronger transaction isolation, explicit locking, a shared aggregate version, a constraint expressible by the database, or another mechanism suited to the invariant. Adding a version field to one participating row does not expand its comparison scope.
Blind increments can avoid a read-modify-write cycle
Not every update needs optimistic version checking. Some mutations can be expressed atomically from current database state:
UPDATE counters
SET value = value + 1
WHERE id = 9;This statement does not calculate a replacement value from an earlier client-side snapshot. The database evaluates the increment against the row it updates, so the classic stale read followed by an absolute overwrite is absent from this operation.
A version check becomes relevant when acceptance depends on the caller having observed a particular prior state. Applying version tokens mechanically to every update can add conflict handling without protecting an actual snapshot-dependent decision.
Deletion needs the same precondition when stale deletes matter
A stale client can also delete a row after another writer has changed it. If that intervening change should invalidate the deletion decision, the version belongs in the delete predicate:
DELETE FROM user_preferences
WHERE user_id = 42
AND version = 7;Again, zero affected rows is the concurrency signal. This keeps update and delete semantics aligned: both operations are accepted only against the state revision the caller claims to have observed.
Soft-delete schemes require the same analysis. If deletion is represented by an UPDATE, its version transition must participate in the same protected mutation rules as other state changes.
The token closes a narrow but useful race
Version columns convert a stale overwrite from a silent replacement into an explicit failed precondition at the database boundary. Their strength comes from a small set of conditions: the token is read with the state, checked atomically in the mutation predicate, advanced by every protected write, and the affected-row result is treated as meaningful.
That boundary is intentionally narrow. It does not serialize arbitrary workflows, protect unrelated rows, or make retries semantically safe. It gives one mutation a precise claim: apply this change only if the row is still the revision on which the change was based.