Two transactions can read the same row, compute different changes, and then write in sequence. If each update replaces values derived from its earlier read, the later write can erase part of the earlier one without either transaction observing a database error.
A version column changes that interaction. The row carries a generation value alongside its domain fields, and an update is accepted only when the generation still matches the value observed by the writer. A stale writer no longer looks identical to a current writer at the storage boundary.
This mechanism is often called optimistic concurrency control, but the useful engineering property is more specific: a read dependency becomes part of the write predicate. The database can then distinguish a write based on current state from one based on state that has already been superseded.
A successful update can still lose information
Consider an account preference row containing an email setting, a locale, and a version:
account_id = 17
email_opt_in = true
locale = "en"
version = 8Request A reads version 8 and changes the locale. Request B also reads version 8 and changes the email setting. If both application paths later issue full-row updates without a concurrency predicate, this ordering is possible:
A reads: email=true, locale=en
B reads: email=true, locale=en
A writes: email=true, locale=fr
B writes: email=false, locale=enBoth SQL statements may succeed. The final row contains B’s email change but restores the locale value that B read before A committed. From the database engine’s perspective, the second statement can be a valid update. The lost information comes from application-level read-modify-write semantics, not necessarily from a failed statement or malformed transaction.
A transaction isolation level affects which states a transaction can observe and which concurrent histories the database permits. It does not follow that every isolation level automatically rejects every stale application write. Exact behavior depends on the database, isolation level, statement shape, and timing.
A version predicate expresses the application’s dependency directly:
UPDATE account_preferences
SET email_opt_in = false,
locale = 'en',
version = 9
WHERE account_id = 17
AND version = 8;If A has already advanced the row to version 9, B’s statement affects zero rows. The stale assumption is now observable.
The row count is part of the protocol
With versioned updates, an affected-row count of zero is not merely an implementation detail. It can represent a concurrency conflict.
A typical state transition has the form:
expected generation: 8
new generation: 9The write is valid only while the stored generation remains 8. Another successful writer consumes that generation by advancing it. Later writers carrying the old expectation cannot satisfy the predicate.
This is a compare-and-set operation expressed through ordinary database state. The comparison and mutation must be evaluated atomically by the database statement. Splitting them into a separate version check followed by an unconditional update recreates the race:
read version
check version in application
another writer commits
write without version predicateThe protected boundary is therefore the conditional mutation itself, not an earlier application check.
Applications also need to distinguish a missing entity from a version mismatch when that distinction matters to their API. A zero-row update alone may represent either case. Some designs perform an additional read after the failed update; others use database features or application invariants that make the cases separable. That extra classification is outside the atomic compare-and-set operation, so concurrent deletion or recreation must be considered if identifiers can be reused.
Versions represent generations, not time
A version value does not need to be a wall-clock timestamp. Its purpose is to identify a row generation in an order suitable for conflict detection.
An integer counter is straightforward because each accepted mutation can increment it:
UPDATE document
SET body = :body,
version = version + 1
WHERE id = :id
AND version = :expected_version;The writer does not need to calculate the next value independently when the database can increment the stored value. If the application needs the resulting generation, database-specific returning facilities can expose it as part of the statement.
Timestamps can also serve as concurrency tokens under suitable conditions, but they introduce properties that must be examined rather than assumed. Timestamp precision may permit multiple updates to share a represented value. Application-generated timestamps depend on clock behavior. Database-generated timestamps have engine-specific semantics. A token intended only for equality comparison does not gain much from pretending to be civil time.
Opaque tokens are another option. Their requirement is freshness across accepted generations, not numeric meaning. Some database systems expose native row-version metadata with system-specific guarantees. Such facilities should be treated according to their documented scope; a token that is unique within one table, database, or storage engine is not automatically a globally ordered revision identifier.
The central invariant is smaller: after a successful mutation changes the token, a writer holding the previous token must fail its conditional mutation.
Conflict detection is not conflict resolution
Rejecting a stale write preserves information by refusing to guess how concurrent intent should be combined. It does not decide what happens next.
A client may reload current state and present a conflict. An application may recompute its operation against the new state. A narrowly defined mutation may be safe to retry automatically. Some domain operations can be merged because they touch independent logical fields; others cannot because their meaning depends on the complete state that was originally read.
These outcomes are not interchangeable.
Suppose two requests both edit a text field. Reapplying the later request after a fresh read may still overwrite the earlier edit; it merely does so after observing the new generation. If the intended semantics require preserving both edits, a version check alone provides no merge algorithm.
The distinction is especially clear for commands based on predicates. Consider a request that reads an order in pending state and computes an action valid only for that state. If another transaction moves the order to cancelled, blindly retrying the original write against the new version can violate the command’s precondition. Re-execution must include the domain decision, not just the SQL statement.
A concurrency token therefore exposes a decision point. It converts a silent overwrite into a result the surrounding application can interpret according to domain semantics.
Narrow updates and version checks solve different problems
Updating only changed columns can reduce one form of accidental overwrite. If A updates only locale and B updates only email_opt_in, both statements can preserve the other’s field even when they were derived from the same initial row.
That does not make version checks redundant.
Two operations may target the same field. A command may depend on fields it does not modify. A derived value may be calculated from several columns and stored in only one. An invariant may span related rows. In each case, the set of written columns is not a complete description of the state on which the operation depends.
Conversely, adding a row version does not automatically protect invariants outside that row. If a decision reads several rows but the conditional update checks only one row’s generation, concurrent changes to the other rows may remain invisible to the predicate.
The concurrency boundary has to match the dependency boundary. A single row token is a good fit when the relevant state can be represented by that row’s generation. Wider invariants may require stronger transaction isolation, explicit locking, a shared aggregate version, database constraints, or another mechanism that makes the complete dependency visible at commit time.
HTTP validators can carry the same boundary
The same generation concept can cross an HTTP interface. A representation can expose an entity tag, and a modifying request can carry a conditional header that names the representation generation on which the mutation is based.
This can connect an API-level precondition to a database-level concurrency token, but the mapping requires care. HTTP entity tags identify representations, while a database version commonly identifies stored entity state. If content negotiation, authorization, projection, or other representation logic causes several representations to correspond to one database row, treating the two concepts as identical may be incorrect.
A service can still use a row generation to construct a validator when its representation semantics support that choice. The useful property is that the client sends its expectation back with the mutation rather than relying on an earlier read remaining current.
The boundary then becomes explicit across layers:
client representation generation
|
v
API precondition
|
v
database conditional updateIf any layer drops the condition and performs an unconditional mutation, the protection is weakened at that point.
Bulk operations need an explicit conflict model
Version predicates are simple for one entity and less obvious for batches.
A bulk request might carry an expected version for every row. The database can then condition each mutation on its corresponding generation, but the application still needs semantics for partial success. One stale row can abort the entire transaction, or independent rows can succeed while conflicts are reported separately. Both models are possible; neither follows automatically from using version columns.
Set-based SQL also changes the shape of conflict reporting. A statement that expects to update 500 rows but affects 497 reveals that some predicates failed, yet it may not identify the three conflicts unless the database statement or surrounding query returns enough detail.
The expected cardinality becomes an invariant of the operation. Treating a lower row count as ordinary success discards the signal that versioning was introduced to provide.
For large background jobs, a version token can also prevent stale computed results from replacing newer state. A worker can read entity version 31, perform an expensive calculation, and condition publication on version 31 still being current. The calculation may become obsolete while it runs; the final compare prevents obsolete output from being attached to a newer entity generation.
Deletion and recreation expose token scope
Deletion creates a subtle boundary for version schemes whose counters restart when a row is inserted.
Suppose an entity with identifier 17 and version 4 is deleted, then a new entity is later created with the same identifier and version 1. A stale request carrying assumptions about the old entity cannot match version 1, so that particular stale token is rejected. But if version values can eventually repeat, or recreation restores a prior version, equality alone may not distinguish entity incarnations.
Systems that permit identifier reuse can include an incarnation identifier, use tokens that do not repeat within the relevant scope, or define recreation so old references are invalid through another stable identity. The appropriate choice depends on the lifetime and uniqueness guarantees of identifiers.
This issue is related to the ABA shape seen in compare-and-set algorithms: equality says that the observed marker matches, but repeated marker values can hide an intervening state transition. A monotonically increasing counter that is never reset for the logical identity avoids that ambiguity until its numeric range is exhausted. Other token schemes can provide the same non-repetition property without numeric ordering.
The useful abstraction is a write precondition
Calling the mechanism a version column can make it sound like a schema convention. Its deeper role is to move an assumption into the mutation that depends on it.
Without the predicate, application code effectively says: write this state now, even though it was computed from an earlier snapshot.
With the predicate, it says: write this state only if the entity is still the generation I observed.
That difference is visible and testable. Two writers can be arranged to read the same generation; after one commits, the other conditional mutation must not report success. Tests can also verify the surrounding conflict policy without depending on timing accidents, because the stale generation is explicit data.
The mechanism remains intentionally limited. It does not serialize arbitrary multi-row decisions, merge concurrent intent, or make retries automatically valid. It gives one precise guarantee at one boundary: a mutation conditioned on an old generation cannot silently replace a newer generation when the database evaluates the comparison and update atomically.
That guarantee is small enough to reason about and strong enough to change the failure mode. Instead of accepting a stale overwrite as an ordinary successful write, the system receives a conflict that application semantics can address.