ConcurrencyGENERALDATABASE-SPECIFICFRAMEWORK-SPECIFIC

Optimistic Concurrency

Read a version, write only if it has not changed, and treat zero rows updated as a conflict — never as success.

What actually happensHow to build it

The requirement, the obvious build, and why it breaks

Every lesson starts where the work starts: someone asked for something, and the first implementation that comes to mind has a problem.

The question

How do I let two users edit the same record without one silently overwriting the other?

The requirement

Two support agents open the same ticket. Both edit it. Neither should lose their work without being told.

The obvious build

Load the record, apply the changes, save the whole object back with UPDATE tickets SET ... WHERE id = ?. Last write wins, which is simple and usually fine.

Why it breaks

Agent A changes the priority; agent B, working from the version they loaded before A saved, changes the assignee and writes back every field. A's priority change is gone, with no error and no trace.

How it breaks in production
  • Agent A changes the priority; agent B, working from the version they loaded before A saved, changes the assignee and writes back every field. A's priority change is gone, with no error and no trace.
  • The overwrite is invisible. Both agents saw a success. The lost change is discovered later by someone wondering why the priority reverted.
  • It is worse with whole-object saves from an ORM, which write every column including the ones the user never touched, so a stale load overwrites fields the user never saw (What an ORM Actually Does).
  • Adding a "last modified" comparison in application code — load, compare timestamps, then write — is the same read-decide-write race one level up.
  • Wrapping it in a transaction changes nothing at read-committed: both transactions read the row, both update it, and the second commit wins (Isolation Levels).
Requirement→API Contract→Application Logic→Data Access→External Deps→Concurrency→Failure→Security→Observability→Deployment→Scale

What is actually happening

  • Optimistic concurrency assumes conflicts are rare. It does not prevent them; it detects them, at the moment of writing, and turns them into an explicit outcome.
  • The row carries a version integer. A read returns the row and its version. The write is conditional: UPDATE ... SET ..., version = version + 1 WHERE id = $1 AND version = $2.
  • If another writer committed in between, the row's version is no longer $2, the WHERE matches nothing, and the update affects zero rows. That count is the entire mechanism — it is how the conflict is detected, and ignoring it is how the mechanism silently fails.
  • The compare and the write are one statement, so there is no window between checking the version and acting on it. This is compare-and-swap expressed in SQL (Compare-and-Swap: The Primitive Everything Is Built On).
  • It requires no locks and no coordination. Readers are never blocked, writers are never blocked, and the loser of a race learns it lost rather than being made to wait (Optimistic vs Pessimistic).
  • HTTP has the same mechanism at the protocol level: ETag on the response and If-Match on the write, with 412 Precondition Failed as the conflict. Same idea, expressed in the contract instead of the schema (Conditional Requests: ETags, 304 and 412).

Zero rows updated is the whole mechanism

DATABASE-SPECIFICRETURNING is Postgres (and SQLite, and MariaDB); MySQL has no RETURNING on UPDATE, so the new version must be re-read or computed client-side from the value that was sent. The conditional-update semantics are identical; only the round-trip count differs.

The version column does nothing on its own. What makes optimistic concurrency work is that the conditional update reports how many rows it matched, and that the application treats zero as a conflict rather than as a no-op.

This is where implementations fail, and they fail quietly. The column is added, the WHERE version = $2 is written, the update runs — and the return value is discarded, as return values from update statements usually are. The endpoint returns 200. The user's change was silently dropped, exactly as before, except now there is a version column that makes everyone believe otherwise.

So the discipline is narrow and absolute: every conditional update must branch on its affected-row count. If the code does not read that number, the mechanism is not present, whatever the schema says.

The conditional update and its three outcomes
1-- Read: the version travels back to the client with the data.
2SELECT id, title, priority, assignee, version
3  FROM tickets WHERE id = $1;
4 
5-- Write: compare and swap, in one statement. No window.
6UPDATE tickets
7   SET priority = $3,
8       assignee = $4,
9       version  = version + 1,      -- incremented in SQL, never in app code
10       updated_at = now()
11 WHERE id = $1
12   AND version = $2                 -- the version the caller read
13RETURNING version;
14 
15-- Exactly three outcomes, and the caller must distinguish all three:
16--   1 row  -> applied; return the new version to the client
17--   0 rows, row exists      -> CONFLICT (409). Someone else wrote first.
18--   0 rows, row absent      -> NOT FOUND (404).
19--
20-- Telling the last two apart needs a follow-up read; do it only on the
21-- zero-row path, so the common case stays one round trip.

Two details carry the guarantee: version = version + 1 is evaluated by the database, so the increment cannot race with itself, and the WHERE contains the caller's version, so the comparison and the write are indivisible.

When to retry, and when a retry is the bug

A detected conflict has to become something. The two options are to retry the operation against fresh state, or to hand the conflict to whoever initiated it. Choosing wrongly reintroduces the lost update you just detected.

Retry automatically when the operation is a function of current state — increment a counter, add a tag, recompute a derived total. Re-reading and re-applying produces a correct result, and the user never needed to know.

Do not retry when the operation encodes a human decision made against a specific version. If an agent set the priority to "low" while looking at a ticket that has since been escalated, silently reapplying "low" over the escalation is precisely the overwrite optimistic concurrency exists to prevent. Show them what changed and let them decide.

The dividing question is simple: if I re-read the row now, would the user still want the same write? If yes, retry. If the answer depends on what changed, ask them.

Handling a detected conflict
Blind retry
for (let i = 0; i < 5; i++) {
  const t = await load(id)
  const res = await update(id, t.version, { priority: input.priority })
  if (res.rowCount === 1) return res
  // re-loops with fresh version, re-applies the user's stale decision
}
throw new Error('too many retries')
Retry only what is recomputable
// Recomputable from current state: safe to retry.
async function addTag(id: string, tag: string) {
  for (let i = 0; i < 5; i++) {
    const t = await load(id)
    if (t.tags.includes(tag)) return t              // already true
    const res = await update(id, t.version, { tags: [...t.tags, tag] })
    if (res.rowCount === 1) return res
    await sleep(jitter(i))                          // avoid lockstep retries
  }
  throw new Conflict409()
}

// A human decision against a version: never retried silently.
async function setPriority(id: string, version: number, priority: Priority) {
  const res = await update(id, version, { priority })
  if (res.rowCount === 0) {
    const current = await load(id)
    throw new Conflict409({ current, yourVersion: version })  // let them see it
  }
  return res
}

The left version detects the conflict and then destroys the information it detected, reapplying a decision made against data the user never saw. The right version separates operations that can be recomputed from decisions that cannot, and returns the current state so the caller can resolve a real conflict rather than being told to try again.

Optimistic across the wire: ETag and If-Match

PROTOCOL-SPECIFICHTTP distinguishes 409 Conflict — the request conflicts with the resource's current state — from 412 Precondition Failed, which specifically means an If-Match precondition failed. Use 412 when the client sent a precondition header, and 409 when the conflict was detected by other means; clients written against one and served the other will handle it as an unexpected error.

A version column protects the row inside one request. It does not protect against a user who loaded a page, went to lunch, and saved — unless the version makes the round trip to the client and back. HTTP has a standard way to express exactly that.

The server returns an ETag with the resource, derived from the version. The client sends it back as If-Match on the update. The server compares, and answers 412 Precondition Failed if it does not match. It is the same compare-and-swap, moved into the contract so that any client — including ones you did not write — participates (Conditional Requests: ETags, 304 and 412).

The API-design domain owns the contract details: which representations get ETags, whether they are strong or weak, and how If-Match: * behaves. What belongs here is the implementation consequence: the header value has to map to something the database can compare atomically, which in practice means the version column and not a hash of the response body, because a hash cannot be used in a WHERE clause.

LayerTokenConflict signalWhat it protects
SQL statementversion column0 rows affectedTwo writers inside overlapping requests
Service methodversion parameterConflict exceptionA stale in-process object graph
HTTPETag / If-Match412 Precondition FailedA client holding a stale representation
UILoaded version in form stateA diff shown to the userA human editing against stale data
Document storeDocument revision (_rev, _etag)Update rejectedThe same mechanism, natively provided
Cache entryValue + versionCompare-and-set failureTwo writers to one cache key (Cache Invalidation)

How to build it

Most important first.

  • Add a version column (or a timestamp with sufficient resolution, though an integer is safer) and increment it in the same statement that updates the row. Never in application code.
  • Check the affected-row count on every conditional update. Zero rows must raise a conflict. This is the single most important line in the implementation and the one most often missing.
  • Surface the conflict to the caller as 409 Conflict, or 412 if the precondition came in as If-Match, with enough information to resolve it — ideally the current state (Status Codes From the Server's Side).
  • Retry automatically only when the operation can be recomputed from fresh state without user input: incrementing a counter, appending to a set, recalculating a derived field. Bound the retries.
  • Do not retry automatically when a human made a decision against a version of the data. Show them what changed; silently reapplying their edit over someone else's is the bug in a different costume.
  • Update only the fields that changed. Whole-object writes turn every concurrent edit into a conflict even when the two users touched different fields (Three Models, Not One).
  • Expose the version to clients so they can send it back. Without a round trip through the client, optimistic concurrency only protects against races inside one request.

What can go wrong

Failure modes
  • Ignoring the row count, so a conflict is indistinguishable from success. The mechanism is present, the column is there, and it protects nothing.
  • An ORM that manages versions and silently swallows the conflict — or one that throws an exception type the application catches and retries blindly, reapplying stale data.
  • Retrying automatically where a human decision was involved, which restores the lost-update bug on top of a correct mechanism.
  • Using updated_at as the version with second resolution, so two updates in the same second are indistinguishable.
  • Unbounded retry loops under contention, turning a hot row into a CPU-burning livelock (Livelock).
  • Version incremented in application code (obj.version + 1) rather than in SQL (version = version + 1), reintroducing a read-modify-write on the version itself.
  • Optimistic concurrency on a row that is contended constantly, where nearly every write conflicts and the retry cost exceeds what a lock would have cost (Pessimistic Locking).
What can race
  • The race this mechanism exists to detect: two writers reading the same version and both attempting to write.
  • A client caching a version across a long session and sending it much later, which is a legitimate conflict rather than a bug.
  • Two fields of one row edited concurrently by different users — a conflict under whole-object updates and not a conflict under field-level ones.
  • A retry loop racing a continuous stream of other writers on a hot row, so it never wins (Starvation).
  • The version incremented outside the conditional statement, which races with itself.
Security
  • A conflict response tells the caller the record changed. On a shared object that is fine; on an object the caller should not be able to observe at all, the version is a side channel and the authorization check must come first (Object-Level Authorization).
  • Do not accept a client-supplied version as authorization to skip a permission check. It is a concurrency token, not a capability.
  • Without conflict detection, a slow attacker can deliberately reload a stale version to revert a security-relevant field — a permission downgrade, a disabled flag — by writing a whole object.
Misreads
  • "Optimistic locking" — it is not a lock. Nothing is held, nothing blocks, and the name causes people to expect mutual exclusion that does not exist.
  • "Zero rows updated means the row was not found." It means the WHERE did not match, which here means the version moved. Conflating the two turns a conflict into a 404 or, worse, into a success.
  • "It prevents lost updates." It detects them. Prevention is what you do with the detection.
  • "Always retry on conflict." Only when the operation can be recomputed. A human edit re-applied over someone else's change is the original bug.
  • "A timestamp is as good as a version." Only if its resolution exceeds your write rate and its source is monotonic — two conditions that fail more often than expected (Eventual Consistency in Practice).

Operating it

How you see it in production
  • Count conditional updates that affect zero rows, by table. That is your conflict rate and it should be a metric, not a log line.
  • Alert when the conflict rate on an endpoint rises sharply — it means contention has moved from occasional to structural, and optimistic may be the wrong choice there now.
  • Track retry attempts per operation. A rising mean means the row is hot enough that retries are compounding (Hot Keys: When Aggregate Metrics Hide a Saturated Node).
  • Log the versions involved on conflict — expected, actual — because that pair immediately distinguishes a genuine concurrent edit from a client sending a stale version it cached.
What changes at 10x and 100x
  • Conflict rate rises with the square of concurrent writers on a row, roughly, so a row that conflicts occasionally at 10x conflicts constantly at 100x.
  • Under high contention, optimistic degrades badly: work is done, discarded, and redone, so throughput falls while CPU rises. That inversion is the signal to switch mechanisms (Optimistic vs Pessimistic).
  • It scales beautifully where writes are spread across many rows, which is the common case. Per-user, per-document and per-order rows almost never contend.
  • Nothing about it requires coordination between instances, so it scales horizontally without change — which is its main advantage over any lock (A Mutex on Server A Does Nothing About Server B).
What this costs
  • Optimistic costs nothing when there is no conflict and costs the whole operation when there is. That is the right trade only if conflicts are genuinely rare.
  • It pushes conflict resolution to the caller, which is honest and means clients must handle a 409 they would rather not think about.
  • A version column is a schema change and a discipline: every write path must respect it, and the one that does not is the one that loses updates.
  • Field-level updates reduce false conflicts and give up the simplicity of saving a whole object.

Where this applies

Backend advice is context-sensitive. These labels say what each claim is specific to, and where a different stack or scale would differ.

  • GENERALCompare-and-swap on a version is available in any store that can express a conditional write.
  • DATABASE-SPECIFICPostgres and MySQL both report affected rows for a conditional UPDATE, but drivers differ in what they return: MySQL by default reports *changed* rows rather than *matched* rows, so an update that sets a column to its existing value can report 0 and look like a conflict. Some drivers enable CLIENT_FOUND_ROWS to report matched rows instead. Know which your driver does before branching on the number.
  • FRAMEWORK-SPECIFICMany ORMs implement this for you — Hibernate's @Version, Django's select_for_update alternative of conditional updates, ActiveRecord's lock_version. What differs is the exception raised and whether the session is left usable after it; a caught-and-ignored StaleObjectStateException is the same failure as an ignored row count.

Where the depth lives

This domain teaches the application-side mechanism and hands the rest off.

API Designoptimistic-concurrencyconditional-requests
Databasemvccisolation-levelsconcurrency-anomaliesupdate-delete-under-mvcc
Performancehot-keys
Concurrencyoptimistic-concurrency-controlcompare-and-swapoptimistic-vs-pessimisticreasoning-about-racesstarvation
Domains that do not exist yet
  • — Distributed Systems — version vectors and conditional writes as the general form of conflict detection without coordination.