A request loads an entity, changes a field in memory, and the ORM issues the UPDATE at commit. The database runs at READ COMMITTED isolation. Does that isolation level stop a concurrent request from silently overwriting the change, and if not, what does?

## Why the ORM shape is the problem A lost update needs two ingredients: a read, then a write derived from it, in separate statements. An ORM produces exactly that by design. You load an entity at the start of a request, business code changes fields, and the UPDATE is generated at flush from the difference between the current values and the snapshot taken at load. Between those two statements sits the entire request — validation, other queries, sometimes remote calls. Under READ COMMITTED, the SELECT takes no lasting lock. Another session can read the same row, modify it, and commit before your UPDATE runs. Your UPDATE then writes the full column set computed from stale inputs, and their change disappears. Nothing raises an error, because from the engine's point of view both transactions did legal work. ## What isolation does and does not cover The part owned by the engine is what a read sees. Higher isolation can extend that: a snapshot fixed for the whole transaction, or conflict detection that aborts one of two transactions that touched the same row. Whether raising the level actually prevents this specific anomaly is engine-specific, and that is the trap in the interview answer. On some engines, an elevated level detects the write conflict and aborts one transaction with a serialization error; on others, the higher level changes only what plain reads observe while the UPDATE still reads and overwrites the latest committed row. Either way you have to say what the application does next — and if the answer is a serialization error, the application must handle it. So the reliable statement is: isolation is not a substitute for an explicit conflict-detection mechanism when the read and the write are separate statements executed at application speed. ## The three application-level options 1. Version check at write time. A version column makes the generated statement UPDATE ... SET ..., version = old + 1 WHERE id = ? AND version = old. If a concurrent writer already bumped the version, the update affects zero rows, the ORM notices the mismatch between expected and actual row count, and raises a conflict. No locks are held between read and write, so throughput stays high; the price is that the loser must redo the work in a fresh persistence context. This is the default choice for interactive workloads. 2. Locking read. Ask for the row with a lock at load time so the engine emits SELECT ... FOR UPDATE (or FOR SHARE for a read lock). The row is held until commit, so the second writer waits and then reads your committed value. Correct and simple, but it serialises the hot row, holds locks for the whole request, and introduces lock-wait timeouts and deadlock risk when several rows are taken in inconsistent order. Suitable for short, contended, must-not-fail sections. 3. Remove the read. If the change is relative rather than absolute — increments, decrements, appends, state transitions guarded by a predicate — express it as one statement. UPDATE account SET balance = balance + ? WHERE id = ? has no window at all, and adding AND balance + ? >= 0 makes the invariant part of the statement. In an ORM this is a bulk update or a native statement, and it bypasses the persistence context, so anything already loaded must be refreshed or the context cleared afterwards. ## Choosing between them Ask how often conflicts really occur and what a conflict costs. Rare conflicts plus cheap retries favour the version check. Frequent conflicts on a small set of hot rows favour a locking read, because optimistic retries under heavy contention waste work and can livelock. Pure counters and relative updates favour the single statement, which is both the fastest and the least error-prone. They combine: a version column protects the general case while a handful of hot operations use one atomic statement. ## Practicalities the interviewer listens for Be explicit that the window is the whole request, not just the flush. Point out that partial updates — dynamic update generation that writes only changed columns — narrow but do not close the hole, because two requests changing the same column still collide. Mention that any of the failure modes here (conflict exception, serialization failure, lock timeout) leaves the persistence context unusable, so recovery means a new one and a re-read, and that retries must be bounded. And note the observability angle: conflicts should be counted, because a rising rate is a design signal that the contended row wants a different model.