Optimistic Concurrency

Optimistic concurrency detects conflicting writes with a version or timestamp instead of locking rows up front, allowing parallel work and retrying the loser.

data-integrity

Definition

Optimistic concurrency is a strategy that lets multiple transactions read and attempt updates without acquiring long-lived locks. Each row carries a version number or timestamp. An update succeeds only if the version still matches the value read; otherwise the write is rejected and the caller retries with fresh data. It assumes conflicts are relatively rare and trades lock waiting for conflict detection and retry.

Context and scope

Optimistic concurrency is common in web applications where reads vastly outnumber conflicting writes. It prevents lost updates when two users edit the same record. It is a poor fit when conflicts are frequent or retries are expensive, where pessimistic locking or serializable isolation may be simpler.

Common misconceptions

Optimistic concurrency never blocks — it avoids read locks, but the database still serializes conflicting writes at commit time
A timestamp is always reliable — clock skew and low-resolution timestamps can miss conflicts; a monotonic version is safer
Retrying automatically is always safe — retries must re-read the row and reapply the user's intent, otherwise they can overwrite other changes

Anti-patterns

Updating by ID without a version predicate and assuming the last writer is correct
Retrying a failed version check with stale form data instead of reloading
Using optimistic concurrency for high-contention counters without batching or atomic increments

Related concepts

Related examples

Authoritative source

https://www.postgresql.org/docs/current/transaction-iso.html

The lost update problem

Two users load the same row, both change a different field, and both save. A blind UPDATE ... WHERE id = ? writes the last value and silently discards the first change. Optimistic concurrency makes the second write fail visibly.

UPDATE documents
SET title = $1, version = version + 1
WHERE id = $2 AND version = $3
RETURNING id, title, version;

If zero rows are returned, the caller’s version is stale. The correct response is to reload the row, show the conflict, and let the user reapply the change, not to retry with the old payload.

When to use it

Use optimistic concurrency for records edited by humans, configuration objects, and low-contention entities. Compared to pessimistic locking, it avoids holding locks across user think time. Compared to serializable isolation, it is a targeted application-level check that is easier to reason about per endpoint.

Pitfalls

A version column must be updated atomically with the data. If the API returns 409 Conflict, include the current version so the client can reload. For counters, use an atomic increment rather than read-modify-write. For multi-row invariants, a version on one row is not enough; consider a transaction with serializable isolation.