Backend Interview: How Do You Prevent Lost Updates with Optimistic Concurrency Control?

Problem and applicable scenarios

The resource invoice_items/{id} contains a description and an amount. Alice and Bob both read version 7. Alice saves first and creates version 8. Bob then submits an edit that is still based on version 7. An unconditional UPDATE lets Bob overwrite Alice while both callers receive success. That is the lost update this question must prevent.

Assume an HTTP JSON API, a relational database that supports conditional updates, and resource IDs that are never reused. Each response has one canonical JSON representation. Every business-field change advances the resource version and produces a new strong ETag. Conflicts are expected to be uncommon, so the design uses optimistic concurrency instead of holding a database lock while a person edits. PUT, PATCH, and DELETE are in scope; real-time collaborative editing algorithms are not.

The core competency is backend design: connect the version a client read, an HTTP precondition, and an atomic database write into one testable contract. The examples use PostgreSQL-style SQL; another database needs an equivalent conditional update and affected-row check. Comparing a version in a controller and then issuing an unconditional update still leaves a race between the check and the write.

What the interviewer is evaluating

The first signal is whether the candidate can show the lost-update interleaving and explain why last writer wins is not necessarily correct. A strong answer returns a version witness on every editable read and requires a writer to prove that it is based on the current version.

The second signal is precise HTTP semantics. A GET returns a strong ETag and a modifying request sends it in If-Match. If-Match uses strong comparison, so a stale or weak tag cannot pass. A missing required precondition can produce 428 Precondition Required; a supplied tag that does not match produces 412 Precondition Failed. Reserve 409 Conflict for a business conflict not expressed by the HTTP precondition.

The third signal is making the database check and write atomic. Match both id and version, update the business fields, and increment the version in one statement. Zero affected rows is the point at which the resource is either gone or stale. Reading the version and then performing an unconditional write is a TOCTOU race.

Finally, look for conflict recovery and boundaries. Optimistic concurrency does not replace an idempotency key, and one row's version does not protect a cross-row predicate. If a hot resource continually returns 412, the design should change its contention strategy instead of making clients retry immediately forever.

Questions to clarify before answering

• Is the conflict domain the whole resource or one field? A whole-resource version is easiest to prove, but

edits to different fields still conflict. Frequent false conflicts may justify smaller aggregates, explicit operation APIs, or field-level merge rules.

• Which mutation methods require a version? This question requires If-Match on PUT, PATCH, and

DELETE. Imports, background jobs, and administration scripts must follow the same protocol instead of retaining an unprotected write path.

• Who resolves a conflict? For human edits, show original, proposed, and current values and let the user

choose. A machine may recompute and retry only when its merge function preserves the business invariant.

• What are the conflict rate and latency budget? Rare collisions fit an optimistic design. Hot inventory or

auction records may favor one atomic business update, a short pessimistic transaction, or a single-writer queue.

• Can a client resend the same request after a timeout? Version checks handle different intents racing.

Idempotency keys handle repeated transport of one intent. The design may need both.

• Does the version cover a multi-row rule? A row version only protects that resource. A rule such as “one

doctor must remain on call” needs Serializable, a common lock row, or a database constraint.

30-second answer framework

“I would return the current resource with a strong ETag, such as \"invoice-item-42-v7\" for version 7. The client sends that value in If-Match when saving. The server returns 428 if the required header is absent and 412 if the tag is stale. The real guard is an atomic database statement: UPDATE ... WHERE id = ? AND version = 7 changes the business fields and increments the version together. Zero rows means stale or deleted; never check first and then update unconditionally. A success response carries version 8's ETag. On 412, the client refetches and lets the user merge. An idempotency key separately covers retransmission after a timeout. I would test two clients that both read version 7 and prove that exactly one write succeeds.”

Step-by-step deep dive

Start with the incorrect history:

Alice: GET item 42 -> amount=10000, version=7
Bob:   GET item 42 -> amount=10000, version=7
Alice: PUT amount=10500 -> unconditional UPDATE -> success
Bob:   PUT amount=9800  -> unconditional UPDATE -> success
Final: amount=9800; Alice's accepted update is lost

The recommended read contract returns a strong ETag. It is an opaque value that the client must echo exactly. It must not contain sensitive information, and it never replaces authentication or authorization:

HTTP/1.1 200 OK
Content-Type: application/json
ETag: "invoice-item-42-v7"

{"id":42,"description":"Consulting","amountCents":10000,"version":7}

Alice places that tag in If-Match:

PUT /invoice-items/42 HTTP/1.1
Content-Type: application/json
If-Match: "invoice-item-42-v7"

{"description":"Consulting","amountCents":10500}

The server performs its normal authentication, authorization, and request validation before evaluating the precondition. If the API requires every mutation to be conditional, an absent If-Match returns 428 and tells the client to refetch and resubmit with a tag. If the supplied value is not the current strong ETag, the server does not modify the resource and returns 412. Strong comparison requires both entity tags to be non-weak and their opaque tags to match character by character. W/"invoice-item-42-v7" therefore cannot pass.

After the HTTP check, the database must atomically enforce the same expected version:

UPDATE invoice_items
SET description = $1,
    amount_cents = $2,
    version = version + 1
WHERE id = $3
  AND version = $4
RETURNING id, description, amount_cents, version;

$4 is expected version 7, decoded from the validated ETag. One returned row means success, so the response contains version 8 and its new ETag. On zero rows, read the current record within the authorization boundary: return 404 if it is absent, or 412 if it still exists. The no-ID-reuse assumption prevents delete and recreate from masquerading as the original resource. Regardless of classification, the zero-row path never writes.

Do not run SELECT version, compare it, and follow with an UPDATE that lacks the version predicate. Another transaction can commit between those statements, allowing the request that just passed its check to overwrite new state. Even if the controller compared the ETag, WHERE version = $4 is the final correctness boundary. An ORM concurrency token should generate the equivalent conditional update and turn zero affected rows into a concurrency conflict.

Align each status with its cause:

• 428 Precondition Required: the client omitted a condition required by this API.
• 412 Precondition Failed: the client sent If-Match, but the current representation does not satisfy it.
• 404 Not Found: subject to the authorization policy, the resource no longer exists.
• 409 Conflict: the version precondition passed, but another business-state conflict applies, such as a settled

invoice that is immutable.

After 412, an interactive client performs a new GET and keeps three values: what the user originally read, what the user proposed, and what the server now stores. A proposed change can be applied automatically only to a field whose current value still equals the original; other fields need an explicit conflict decision. A single resource version conservatively rejects even disjoint edits. If that becomes a measured bottleneck, split the resource or expose an intent-based operation such as POST /invoice-items/42/adjust-amount rather than silently reverting to last writer wins.

An idempotency key solves a different failure. Suppose Alice's version 7 update committed as version 8 but the success response was lost. A literal retry now has a stale If-Match. With a stable idempotency key, the server can return the stored first result. The version witness detects two different edits based on version 7; the idempotency record recognizes that one edit was transmitted twice. They are not substitutes. When the server cannot reliably prove prior execution, returning 412 is safer than guessing success from similar current data.

Optimistic concurrency assumes low contention. If a resource has a persistently high conflict rate, repeated reads and manual merges waste capacity and degrade the user experience. Inventory decrement can directly use UPDATE ... SET stock = stock - $1 WHERE stock >= $1 to enforce a one-row invariant. A short read-decide-write operation may use a row lock. An aggregate that must process commands in order may be partitioned to a single writer. Choose from the measured conflict rate, whether operations commute, the waiting budget, and the scope of the invariant.

Verification must force an interleaving rather than call the API twice in sequence. Have both clients read version 7, release different amounts through a barrier, and assert exactly one success, one 412, final version 8, and content from the winner. Also test missing If-Match returning 428, a weak tag failing, a success response with a new ETag, the old tag remaining stale, update racing with delete, and replay with the same idempotency key after a lost response. In production, monitor missing-precondition rate, 412 rate, automatic retries, and final abandonment by endpoint. A sudden increase usually identifies a hotspot or a client that is not refreshing.

High-quality sample answer

“I would set the conflict domain to one invoice line item. Both users have version 7, so the later save must not overwrite the first unconditionally. Every editable read returns a strong ETag, and every PUT, PATCH, or DELETE must echo it in If-Match. A missing header gets 428; a stale tag gets 412 without a write.

Comparing at the HTTP layer is not enough. I would store a per-resource version and make persistence execute UPDATE invoice_items SET ..., version = version + 1 WHERE id = ? AND version = ? RETURNING ... atomically. After Alice succeeds with version 7, the row is version 8. Bob's same predicate affects zero rows, so it cannot overwrite Alice. A success returns the new ETag. After zero rows, an authorization-aware read distinguishes 404 from 412, but no branch falls back to an unconditional update.

On 412, the client refetches and compares original, proposed, and current values so the user resolves a real conflict. If disjoint fields collide frequently, I would split the resource or design intent-based atomic operations. A timeout retry separately uses an idempotency key: it recognizes duplicate transport of one intent, while the version recognizes different intents based on the same old state.

I would use two connections and a barrier so both read version 7 before they submit. Exactly one must succeed, the other must receive 412, and the final version must be 8. I would also test absent If-Match, weak tags, delete races, and lost-response replay. If the 412 rate remains high in production, I would evaluate a conditional business update, a short lock, or a single writer rather than increasing retries indefinitely.”

Common mistakes

• Checking the version in application code before updating → another commit can land between the check and

write → put the version in the same UPDATE predicate and inspect affected rows.

• Continuing when If-Match is absent → protected clients coexist with legacy clients that can still clobber

data → make conditional mutation part of the API contract and return 428 when it is absent.

• Accepting a weak ETag for If-Match → the standard requires strong comparison, so a weak tag cannot match

→ generate a semantically valid strong ETag for editable representations.

• Returning 409 for every conflict → clients cannot distinguish an HTTP precondition failure from a business

state conflict → **return 412 for a failed version precondition and reserve 409 for the independent business conflict.**

• Automatically resending the same request after 412 → the old tag remains stale, while silently replacing it

can overwrite another edit → refetch and recompute or ask the user to merge.

• Assuming idempotency keys prevent concurrent overwrites → two different edits use different keys but can

still share one stale base → **use idempotency and version conditions for duplicate transport and concurrent intent respectively.**

• Claiming a version on every row protects every invariant → cross-row write skew never conflicts on one row's

version → choose Serializable, a common contention point, or a constraint for the invariant's scope.

• Retrying a hotspot immediately without a limit → failed requests collide again and amplify load → **measure

conflicts and consider an atomic operation, a short lock, or a single writer.**

Follow-up questions and responses

Follow-up 1: Should two PATCH requests to different fields conflict?

A whole-resource version advances on any field change, so disjoint patches conflict. That is the safe and easily explained default. If evidence shows many false conflicts, split independently changing lifecycles into subresources or send field baselines and define a three-way merge. Field-level tokens reduce false conflicts but multiply token state and complicate compound invariants and auditing; do not add them merely to suppress 412 responses.

Follow-up 2: What happens when a client retries after a timeout and the old ETag is stale?

Send a stable idempotency key and persist the key, request digest, expected version, and first result together. A replay with the same key and request returns the first result; the same key with a different request is rejected. Without a reliable idempotency record, current fields that merely look similar do not prove that the first request succeeded. Return 412 and let the client inspect current state.

Follow-up 3: Can updated_at be the version token?

It is safe only if the database generates it, every relevant mutation changes it, its precision distinguishes successive writes, and every node gives it the same semantics. Truncated timestamp precision or a bypassing write path can give two versions the same value. A per-resource integer or a database-native row version generally gives a less ambiguous equality check and can still be encoded as an opaque ETag.

Follow-up 4: How should the design change under high contention?

First group 412 responses by resource and operation to separate one hot key, an overly broad conflict domain, and long-offline clients. Convert commutative increment or decrement intents into atomic database expressions. Use a row lock for short non-commutative decisions, or route strictly ordered commands by resource key to one writer. Each alternative trades waiting, throughput, or architecture complexity for fewer rejected writes, so measured conflict and latency data should trigger the change.

Follow-up 5: Why does one row's version not prevent write skew?

In write skew, two transactions read the same cross-row predicate and update different records, so both row-version conditions can pass. Each token proves only that the row being updated has not changed; it says nothing about the shared predicate. Project the invariant onto a common counter row, lock a common guard, add an appropriate database constraint, or use Serializable to detect the non-serializable dependency.