2026-09-10 · 8 min read
- sql
- concurrency
- databases
Optimistic concurrency allows readers to work without holding a lock, then requires a writer to prove that the row still has the version it read. It is effective when conflicts are uncommon and the application can surface or reconcile stale intent.
A version column protects one row from lost updates. It does not enforce cross-row invariants, serialize external side effects, or make a read from a lagging replica current.
Design the version contract
Use a non-null integer counter or another database-managed token with unambiguous equality semantics. Return it on reads, require the expected value on writes, and increment it for every mutation that can invalidate a previously read representation.
- Keep the version opaque to API clients.
- Define whether metadata-only changes increment it.
- Choose a counter width and overflow policy.
- Ensure triggers and bulk operations follow the same rule.
Perform the comparison atomically
Issue one conditional statement such as UPDATE documents SET body = ?, version = version + 1 WHERE id = ? AND version = ? and obtain the affected-row count or returned row from the same database operation. A preceding SELECT does not reserve the version.
If zero rows match, distinguish a deleted resource from a stale version only when the API needs that distinction and authorization permits it. Otherwise return a consistent conflict or precondition response without revealing hidden row existence.
Handle retries and side effects
A transport retry may repeat an update whose commit result was lost. Combine optimistic concurrency with an idempotency key when duplicate request execution matters, and record operation outcomes transactionally where practical.
- Do not automatically replay stale user intent against new state.
- Recheck authorization after fetching the current row.
- Perform irreversible external effects only after a known database outcome.
- Use stronger locking or serializable transactions for multi-row invariants.
Prove complete write-path coverage
Use Flashman's SQL formatter to review predicates, diff for sanitized row versions, JSON formatter for synthetic request and conflict bodies, timestamp converter for race timelines, and UUID generator for disposable operation identities.
Test two and many writers, stale deletes, no-op updates, trigger changes, ORM partial updates, bulk imports, background jobs, administrative SQL, retries after connection loss, failover, replica reads, counter boundaries, and metrics for conflict rate by operation.