How it works
Store a version alongside the data, such as a document with id=42, content='Draft', and version=7. A client reads both the content and version, then prepares its edit without holding a database lock during the user's editing time.
Submit the change as one atomic conditional update: UPDATE documents SET content=:content, version=version+1 WHERE id=42 AND version=7. This is a database form of compare and swap: replace the value only if the expected version still matches.
Check the affected-row count. One updated row means the change succeeded within the transaction; zero means the expected version no longer matches or the record no longer exists. If two writers submit version 7, only one can advance it to version 8.
Every write path that changes the protected data must advance the version. Use a version that does not repeat for the same logical record; a counter is generally clearer than a wall-clock timestamp, which can collide or have insufficient precision.
Handle conflicts according to the operation. A document editor can show the current revision and offer a merge; an automated reservation can reread, recheck availability, and retry within a fixed budget. Do not simply attach the newest version to an old payload and overwrite the intervening change.
Enforce the comparison at the authoritative write store, not with a separate application-side check. A conditional update protects its record, but multi-record rules still need an appropriate transaction, constraint, or coordination scheme; independent checks on asynchronously replicated regions do not create global exclusivity.
A worked example
Two buyers try to hold the same seat
Seat A12 is available at version 18. Buyers Maya and Leo both read it. Each submits: UPDATE seats SET status='held', hold_id=:hold_id, expires_at=:deadline, version=version+1 WHERE seat_id='A12' AND status='available' AND version=18. Maya's transaction updates one row and commits version 19; Leo's updates zero rows and returns a conflict. If creating a separate hold record is required, that insert belongs in the same transaction. Maya receives a two-minute hold, not a confirmed booking. Payment confirmation and hold expiration must also use conditional state transitions so only one wins. A request idempotency key lets Maya recover her existing hold if the successful response is lost.
Trade-offs to say out loud
- No lock held across user think time
- Readers can prepare changes without a long-lived application transaction, although the database may still take short-lived locks while executing the update.
- Efficient when conflicts are uncommon
- Under heavy contention, failed attempts and repeated reads can waste capacity and worsen tail latency.
- Conflict handling becomes explicit
- The application must choose whether to reject, merge, or safely recompute a failed operation rather than silently accepting the last writer.
- Protection has a defined scope
- One record's version does not protect an invariant spanning several records unless the surrounding transaction or data model enforces it.
- All writers must cooperate
- An administrative script or background worker that changes data without advancing the version can bypass conflict detection.
- Retries do not cover external side effects
- A version check does not deduplicate payments or messages, so external effects need idempotency and a reliable handoff such as an outbox.
When to reach for it
- Clients read a document or configuration, edit it, and save later, so concurrent saves must not silently overwrite each other.
- Multiple workers may transition the same order, job, or reservation, and only one transition from a particular version should succeed.
- A reservation or assignment requires an atomic conditional change, but expected contention is low enough that retries are acceptable.
- An API needs to reject stale updates, using an expected version or an ETag with an If-Match precondition.
How to bring it up in the interview
- Start with the invariant: for example, two dispatchers must not assign the same available driver. Show the conditional update with both the expected version and the required current state.
- Walk through two writers reading the same version, then explain the affected-row count and exactly what the losing writer does.
- State the contention assumption and retry budget. Expect a follow-up about hot inventory, starvation, and when admission control or serialized processing would work better.
- Define the transaction boundary. Interviewers may ask whether updating inventory, creating a reservation, and recording an event either all commit or all roll back.
- Separate a known conflict from an unknown outcome after a timeout. Use an operation ID or idempotency key to recover the result rather than blindly issuing the business operation again.
Common mistakes
- Reading the version, checking it in application code, and then issuing an unconditional update, leaving a race between the check and write.
- Incrementing the version without including the expected version in the update predicate.
- Automatically retrying stale user content against the newest version, which can recreate the lost update the check was meant to prevent.
- Assuming optimistic locking means the database takes no locks or that MVCC automatically rejects every stale application save.
- Treating a per-row version as a replacement for multi-row transactions, global coordination, or idempotency.
Where it comes up
Interview questions where optimistic locking is part of a strong answer, and why.
- Design Wikipedia or NotionVersion-checked saves prevent concurrent editors from overwriting revisions and support an explicit conflict-resolution flow.
- Design a ticket booking system like TicketmasterConditional seat-state updates arbitrate competing holds and races between payment completion and expiration.
- Design a ride hailing app like UberAn atomic version-and-state check can prevent concurrent dispatchers from assigning the same driver.
- Design Dropbox or Google DriveExpected file versions detect offline edits based on stale metadata so the system preserves conflicts instead of overwriting changes.
- Design a payment systemConcurrent partial refunds and payouts need conditional balance or state updates within transactions to prevent invalid allocations.
- Design a hotel booking systemInventory version checks can participate in an atomic multi-night reservation transaction, with the entire attempt rolled back on conflict.
- Design Netflix's concurrent screen limitConditional account or slot updates prevent simultaneous playback starts from claiming the same remaining capacity.
Questions people ask
How do you implement optimistic locking with a version column?
Read the row and its version, then update with WHERE id=:id AND version=:expected_version while incrementing the version in the same statement. Check the affected-row count and commit the transaction before reporting success. A zero-row result requires conflict or missing-record handling, not an unconditional overwrite.
What is the difference between optimistic and pessimistic locking?
Optimistic locking detects a conflict when a writer attempts its conditional update. Pessimistic locking acquires a lock before the protected read-and-write sequence, making competing operations wait or fail. Optimistic locking suits infrequent conflicts and long editing gaps; short pessimistic transactions can be preferable when conflicts are frequent.
Is optimistic locking the same as MVCC or compare and swap?
MVCC manages database row versions to support visibility and transaction isolation; it does not necessarily reject a stale payload submitted by an application. Optimistic locking explicitly checks whether the data changed since the application's read. Compare and swap is the atomic primitive behind a version-checked update: write only if the expected value still matches.