A row can be read correctly, modified correctly, and still be written incorrectly. The problem appears when another transaction changes the same logical record between the read and the write. A plain UPDATE often has no memory of the state on which the new values were based, so the later writer can replace an earlier change without detecting the race.
A version column turns that hidden assumption into a predicate. The update says, in effect, that the write is valid only while the row remains at the version that the caller observed. The database then evaluates the state check and the mutation as one atomic statement.
This is optimistic concurrency control at a narrow but useful boundary. It does not lock a row for the entire interval between reading and editing. Instead, it permits concurrent work and rejects a write when its premise has become stale.
The predicate is the concurrency mechanism
Consider an accounts table with a monotonically increasing version column:
CREATE TABLE accounts (
id BIGINT PRIMARY KEY,
display_name TEXT NOT NULL,
version BIGINT NOT NULL
);A process reads one row:
SELECT id, display_name, version
FROM accounts
WHERE id = 42;Suppose the result contains version 12. The process later wants to change the display name. An unconditional statement loses the connection between the read and the write:
UPDATE accounts
SET display_name = 'Ari'
WHERE id = 42;A guarded update retains that connection:
UPDATE accounts
SET display_name = 'Ari',
version = version + 1
WHERE id = 42
AND version = 12;The significant detail is not the numeric counter by itself. It is the equality predicate in the WHERE clause. If the row is still version 12, the statement can change it and advance the version. If another committed write has already advanced the row to version 13, the predicate matches no row.
The affected-row count therefore becomes part of the operation’s result. One changed row means the expected version matched. Zero changed rows means either the row no longer exists or its version no longer matches, unless those cases are distinguished by other constraints or a follow-up read.
Splitting the check from the update breaks the mechanism:
SELECT version FROM accounts WHERE id = 42;
UPDATE accounts
SET display_name = 'Ari',
version = version + 1
WHERE id = 42;Another transaction can commit between those statements. The useful property comes from making the expected version part of the mutation predicate evaluated by the database.
A version represents an observed state
The counter is often described as a revision number, but its practical meaning is more specific: it identifies a state that a caller observed and used as the basis for a proposed write.
Two writers can start from the same row:
Writer A reads version 12
Writer B reads version 12
Writer A updates version 12 -> 13
Writer B attempts version 12 -> no matchWriter B has not necessarily produced invalid data. Its proposal is stale relative to the current row. The application now has an explicit conflict instead of a silent overwrite.
That distinction matters because optimistic concurrency does not decide how competing intentions should be combined. It detects that the premise for a write is no longer current. Conflict resolution remains an application concern.
For a profile field, the caller might reload current state and ask for a fresh edit. For an automated process, recomputing the proposed mutation from current state may be appropriate. For a domain operation such as reserving capacity, the operation may need to be evaluated again because the relevant invariant could have changed.
A blind retry with the same expected version is generally pointless. Once version 12 has become 13, repeating a predicate that requires 12 will continue to fail unless the data model permits version reuse, which a monotonic scheme normally avoids.
The guarded write must include the full logical change
Version checking is strongest when the guarded statement covers the state transition that depends on the observed row.
Suppose an order has both status and shipping_address, and a transition to dispatched is based on the address read with version 8. Updating only the status under a version guard can be correct if the address is immutable at that point or is otherwise protected by the same domain rule. If the address can change independently without advancing the same version, the version no longer represents all state relevant to the transition.
The design question is therefore not merely where to add a counter. The counter’s scope has to match the state whose concurrent modification would invalidate the proposed operation.
A single row version works naturally when one row is the concurrency unit. An aggregate stored across several rows needs a different arrangement if changes to any member should invalidate an operation. Common options include advancing a version on an aggregate root, using a transaction that checks all relevant revisions, or moving the invariant into a database constraint or serialization boundary.
No version column can detect changes that do not participate in its revision protocol.
Version increments belong to the successful mutation
The version should advance only as part of a successful guarded write. Keeping the increment in the same UPDATE avoids a separate race and gives each accepted state a distinct revision.
This form is preferable to calculating the next value in application memory:
UPDATE accounts
SET display_name = 'Ari',
version = version + 1
WHERE id = 42
AND version = 12;The database derives the next value from the row that satisfied the predicate. The application does not need to assume that 13 is still the next revision at statement execution time.
Some systems use timestamps, hashes, or opaque revision tokens instead of integers. Those can serve the same broad purpose if each relevant accepted change produces a token that stale writers cannot accidentally match. Integer counters are attractive because their equality semantics are simple and monotonic progression is easy to inspect.
Wall-clock timestamps require extra care. Clock resolution, clock sources, and update behavior can make equality-based revision tracking less direct. A timestamp is suitable only when the database and schema guarantee the uniqueness and change behavior required by the concurrency protocol.
Transactions change the surrounding boundary, not the predicate
A version-guarded statement is atomic as a statement, but an application operation may contain more than one statement. If a successful row update must be committed together with related database changes, those writes still need an appropriate transaction.
For example:
BEGIN;
UPDATE accounts
SET display_name = 'Ari',
version = version + 1
WHERE id = 42
AND version = 12;
-- Continue only when exactly one row was changed.
INSERT INTO audit_entries(account_id, action)
VALUES (42, 'display_name_changed');
COMMIT;If the guarded update reports a conflict, the transaction should not continue as though the state transition succeeded. The related write belongs to the accepted transition and should be rolled back or omitted.
The version predicate also does not replace database isolation. Isolation defines which effects transactions can observe and how concurrent execution is constrained. A version column adds an explicit application-level precondition to a particular write. The two mechanisms can coexist.
At stronger isolation levels, the database may reject some conflicting schedules itself. At weaker levels, a version predicate can still protect the specific lost-update pattern it encodes. The exact interaction depends on the database engine, isolation level, statement form, and transaction schedule.
Partial updates need deliberate semantics
Optimistic concurrency can become overly restrictive when every edit shares one version even though fields are operationally independent.
Imagine a customer record containing a preferred language and a billing note. Two callers edit different fields from version 20. If both updates require version 20, the first accepted write advances the row, and the second receives a conflict even though the field changes could have coexisted.
That outcome is not inherently wrong. A row-wide version declares that any concurrent row change invalidates every proposal based on the previous row state. This is a conservative policy.
If independent edits should compose, the data model can express a narrower concurrency boundary. Separate tables, field-specific revisions, patch semantics with explicit preconditions, or domain operations that update only relevant state can reduce false conflicts. Each alternative also adds complexity. The correct granularity follows the invariants that must remain coherent, not a general preference for fewer conflicts.
Conversely, allowing field-level updates without checking related state can admit combinations that no caller actually evaluated. A narrow predicate is safe only when the omitted concurrent changes cannot invalidate the operation.
Conflict is a domain-visible outcome
Treating a version mismatch as an ordinary infrastructure exception hides useful information. The database has reported a specific fact: the expected state is no longer current.
An application boundary can represent that result explicitly:
UpdateResult =
Applied(newVersion)
| Conflict(currentVersion)
| MissingThe exact shape varies by language and persistence layer, but the distinction is valuable. Conflict is not equivalent to a connection failure, syntax error, or deadlock. It can be expected under concurrent access and often calls for a different response.
Separating Missing from Conflict may require another read after a zero-row update, or a database feature that returns enough information to classify the outcome. That extra distinction is useful only when callers need different semantics for deletion and concurrent modification.
For HTTP APIs, a storage-level version can also back a protocol validator, but the two concepts should not be conflated. An HTTP entity tag identifies a selected representation according to HTTP semantics; a database version identifies application state according to the persistence model. They can be mapped when their scopes align, but neither automatically defines the other.
Deletes need the same stale-state protection
Deletion is also a state-changing operation. A caller that read version 12 and later issues an unconditional delete can remove version 13, even though version 13 contains a change the caller never saw.
A guarded delete preserves the same precondition:
DELETE FROM accounts
WHERE id = 42
AND version = 12;Again, the affected-row count carries the concurrency result. This is especially relevant when deletion represents a domain decision based on current attributes rather than a purely administrative removal.
Soft deletion follows the same model when it is implemented as an update:
UPDATE accounts
SET deleted_at = CURRENT_TIMESTAMP,
version = version + 1
WHERE id = 42
AND version = 12;The concurrency property comes from the expected-version predicate, not from whether the operation is called an update or a delete.
Version columns expose the premise of a write
Optimistic concurrency with a version column is compact because it moves one important assumption into data that the database can test atomically. A caller no longer says only, “store these values.” It says, “store these values if the state I observed is still current.”
That conditional form is the central property. The counter, affected-row check, transaction boundary, and conflict representation all support it.
The mechanism is most precise when the revision scope matches the logical state on which the operation depends. Too broad a scope rejects compatible concurrent edits. Too narrow a scope misses changes that should invalidate the proposal. At the right boundary, a version column turns a silent race into an explicit result without holding a lock across the caller’s entire read-modify-write interval.