Concurrency
Optimistic locking: interview questions and how to answer them
Optimistic locking takes no lock at all — it records the version a reader saw and refuses the write if that version has moved, turning a lost update into a detectable conflict.
Written and reviewed by Sahil Srivastav
What it actually is
Optimistic locking is a detection strategy, not a prevention strategy. A row carries a version — an integer, a timestamp, or in Postgres the system `xmin` column. A reader reads the row and the version together. When it writes back, the update is conditioned on the version still being what it read: `UPDATE ... SET v = v + 1 WHERE id = $1 AND v = $2`. If another writer got there first, the version moved, zero rows match, and the write is rejected.
The crucial property is that no lock is held across the think time. Between read and write the row is free for anyone. That means a long user session — a form open in a browser for four minutes — costs the database nothing, which is exactly the case where holding a lock would be indefensible.
It is called optimistic because it assumes conflicts are rare and pays only when they happen. The cost of a conflict is a discarded attempt plus whatever the caller does about it: retry, merge, or surface the collision to a human. That cost is the whole trade-off, and it is why optimistic locking is wrong for a hot row that a hundred threads are all updating — there, every attempt but one fails and you have built a livelock with extra steps.
Why it matters in production
Without it, the read-modify-write cycle silently loses data. Two support agents open the same ticket, both edit the priority field, both save. The second save overwrites the first with a value computed from stale data, and nothing anywhere logs that an update was lost. This is the lost update anomaly, and read-committed isolation — the default in Postgres, SQL Server, and Oracle — does not prevent it. Application-level versioning is what prevents it.
It also matters because the alternative in a web application is usually worse. Pessimistic locking means `SELECT ... FOR UPDATE` held across an HTTP request boundary, which means a transaction open for the duration of a user thinking. That pins a connection, blocks other writers, and in Postgres holds back the oldest-transaction horizon so autovacuum cannot reclaim dead tuples. One thoughtless `FOR UPDATE` in a form handler has taken down production more than once.
Interviewers reach for it because it forces a candidate to say what happens on conflict. Anyone can describe a version column; only someone who has shipped it can tell you whether their retry is safe to re-run, and what the user sees when it is not.
How it works
The version must be in the predicate, not in an if-statement
Reading the version, comparing it in application code, then updating is check-then-act with a race window wide enough for two threads to walk through. The comparison has to be part of the same statement as the write, so the database evaluates it under the row lock it takes for the update. The signal you rely on is the affected-row count: one means you won, zero means you lost.
Zero rows updated is ambiguous — disambiguate it
Zero rows can mean the version moved or the row was deleted. Both are conflicts but they need different handling, so a robust implementation follows a zero-row update with a `SELECT` on the id alone. Treating every zero-row result as "retry" makes a deleted row retry forever.
What the version protects is the fields you read
A single row-level version treats the row as one unit, so two agents editing unrelated columns collide unnecessarily. Field-level or group-level versions reduce false conflicts at the cost of complexity. The right answer depends on whether concurrent edits to different columns are semantically independent — for an order, changing the shipping address and the payment status usually are not.
Retry only if the work is reproducible
A retry re-reads the current state and recomputes. That is correct when the operation is a pure function of the state — decrement stock, recompute a total. It is wrong when the operation encodes a human decision made against the state the user saw: silently reapplying "set priority to low" on top of someone else's edit is the lost update you were trying to prevent, arriving by a different route.
Timestamps are a worse version than a counter
Clock resolution and clock movement both break timestamp versions: two updates inside the same millisecond produce the same value, and NTP adjustment can move a timestamp backwards. A monotonic counter incremented by the database, or a random UUID rewritten on each update, has neither problem.
Implementing it
Put the version increment in the same statement as the business update and never anywhere else — a code path that writes the row without bumping the version quietly disables the mechanism for everyone.
Bound the retry loop and make the bound small: three attempts with a few milliseconds of jitter. If three attempts fail, the row is contended enough that the answer is a different design, not a fourth attempt.
Return the conflict to the caller as `409 Conflict` with the current state included, so a UI can show a genuine merge prompt instead of a generic error. A conflict is information, and throwing it away is what makes users distrust the application.
In JPA/Hibernate, `@Version` gives you this and throws `OptimisticLockException` on conflict — but note it only protects entities that Hibernate flushes; a bulk `UPDATE` through JPQL or native SQL bypasses versioning entirely unless you write the predicate yourself.
-- Read: capture the version alongside the data
SELECT id, qty, version FROM inventory WHERE id = $1;
-- Write: the version check is the predicate, not an if-statement
UPDATE inventory
SET qty = $2, version = version + 1
WHERE id = $1 AND version = $3;
-- 1 row => we won
-- 0 rows => conflict OR the row is gone; disambiguate:
SELECT 1 FROM inventory WHERE id = $1;
-- Contended counters do not want optimistic locking at all.
-- A single atomic statement has no conflict to detect:
UPDATE inventory SET qty = qty - $2
WHERE id = $1 AND qty >= $2;Interview questions and how to answer them
Two users open the same record and both save. Walk me through what happens with and without optimistic locking.
Without it, both reads succeed, both updates succeed, and the second write overwrites the first using values computed from data that is now stale — the lost update anomaly, and nothing records that it happened. With a version column, the second update carries the version the user read; that version has moved, so the predicate matches zero rows and the write is refused. Now the application has a choice: recompute and retry if the operation is reproducible, or return 409 with the current state so the user can merge.
When would you choose pessimistic locking instead?
When conflicts are common rather than rare, when a failed attempt is expensive to redo, or when the operation must read several rows and see them consistently while deciding. Transferring money between two accounts is the classic case: the work is short, the rows are few, and you would rather block for a millisecond than discover a conflict after doing the arithmetic. The rule I use is that pessimistic locking is acceptable when the lock is held for a bounded, machine-scale duration — never across a user interaction.
Your version check returns zero rows updated. Is that a conflict?
It is one of two things: the version moved, or the row no longer exists. They need different handling — the first is retryable, the second is not — so I follow up with a select on the primary key alone. Treating them identically is how you get a retry loop that spins forever on a deleted row.
Would you use optimistic locking to protect a stock counter under a flash sale?
No. Under heavy contention the version check fails for almost every thread, so you burn capacity on doomed attempts and add latency with each retry. A counter does not need conflict detection because the decrement can be expressed atomically: `UPDATE inventory SET qty = qty - 1 WHERE id = $1 AND qty >= 1`. The database serialises the row internally and the guard prevents overselling. Optimistic locking is for read-modify-write with think time, not for hot increments.
Does `SERIALIZABLE` isolation make the version column redundant?
For a single transaction, largely yes — Postgres SSI would abort one of the two conflicting transactions. But it does not help the case optimistic locking is usually deployed for, where the read and the write are in *different* transactions separated by a user session. No isolation level spans that gap, because there is no transaction open across it. The version column is what carries the consistency guarantee across the request boundary.
How do you avoid a code path that forgets to bump the version?
Centralise the write. One repository method owns the update statement, and no other code writes that table — enforced by review, and ideally by a trigger that raises if `version` did not change, or by making the version increment happen in a `BEFORE UPDATE` trigger so it cannot be skipped. In ORMs the equivalent trap is bulk update statements, which bypass entity versioning silently.
Answers that lose the round
- Describing optimistic locking as "taking a lock later" — it takes no application lock at all; the only lock is the row lock the UPDATE itself holds for microseconds
- Comparing the version in application code and then issuing an unconditional UPDATE, which reintroduces the exact race the version exists to close
- Ignoring the affected-row count, so a lost conflict is reported to the user as success
- Retrying an operation that encodes a user decision, which overwrites the other writer and recreates the lost update
- Choosing optimistic locking for a hot single row — under real contention nearly every attempt fails and throughput collapses
- Claiming repeatable-read isolation removes the need for it: in Postgres repeatable read aborts the second writer with a serialisation failure, which you must still handle, and in MySQL InnoDB a read-modify-write across a non-locking SELECT can still lose the update
- Using a `updated_at` timestamp as the version and not mentioning clock resolution
Practise optimistic locking in a real repository
Gronex ships this as a runnable repository: an inventory service that oversells under concurrent purchases. The test suite hammers the same SKU from many threads and asserts stock never goes negative, so a version read compared in application code fails and only a genuinely atomic guard passes.
FAQ
Is optimistic locking a database feature or an application pattern?
An application pattern that relies on one database guarantee: that a single UPDATE statement evaluates its predicate and applies its change atomically under a row lock. The version column, the retry policy, and the conflict response are all yours to build. Some ORMs — JPA with `@Version`, Django with `select_for_update`-free `update()` patterns — package it, but the semantics stay application-level.
Can I use the row hash instead of a version column?
Yes, and it avoids a schema change: compare a hash of the fields you read. The downsides are cost on wide rows and false negatives when a field changes to the same value. It is a reasonable fallback when you cannot alter the table, not a first choice.
How does this differ from an idempotency key?
They answer different questions. A version answers "has the state moved since I read it?" and rejects stale writes. An idempotency key answers "have I already applied this exact request?" and makes a duplicate harmless. A retry that carries a fresh version but the same idempotency key is the normal combination.