Optimistic Locking
Optimistic locking detects conflicting concurrent updates at write time using a version, allowing concurrent readers without locks.
Definition
Optimistic locking assumes conflicts are rare, so it does not hold locks while a client reads and decides. Instead each row carries a version or timestamp; an update includes the version it read in the WHERE clause, and the write succeeds only if the stored version still matches. A mismatch means someone else changed the row, and the application retries or reports a conflict. It trades a small chance of retry for no lock contention.
Context and scope
Preferred for web and API workloads where many users read the same record and updates are infrequent relative to reads.
Common misconceptions
- Optimistic locking prevents lost updates — it detects them and forces a retry; the application must still handle the conflict
- It removes the need for transactions — the compare-and-set must itself be atomic, which the database transaction provides
- A mismatch always means a bug — it is the expected signal of a concurrent update and should be handled, not feared
Anti-patterns
- Reading without the version, then writing with a stale version that silently overwrites another user change
- Retrying the update without re-reading, so the retry writes the same stale values again
- Using timestamps as the version when clock resolution allows two updates in the same tick
Related concepts
Related examples
Authoritative source
https://en.wikipedia.org/wiki/Optimistic_concurrency_control
What it is
Optimistic concurrency control lets several transactions proceed without locking the data they read. On commit, the database checks whether any value read still has its original version; if so the transaction commits, otherwise it aborts and may retry. In application terms the pattern is a version column plus an update guarded by that version — an update affects a row only when the caller proves it saw the current version.
Why it matters
Pessimistic locking holds a lock from read to write, which blocks other users and risks deadlock, especially across a multi-step user interaction. Optimistic locking avoids that by not locking at all and detecting the conflict at write time. For read-heavy workloads with occasional writes, it improves throughput and lets clients work without a lock that would have to survive a slow round trip.
How to use it well
Always read the version and include it in the update predicate, and treat a zero-rows-affected result as a conflict rather than an error. On conflict, reload and reapply the change, or surface it to the user; never blindly retry with the old values. Keep version increments accurate and monotonic, prefer explicit version numbers over loose timestamps, and add a conflict-handling path to tests, since the failure mode only appears under real concurrency.