The interview question. A shared team dashboard shows how many incident reports were reviewed today. The count starts at eight. Two reviewers finish at almost the same moment. Both requests return success, but the dashboard says nine, not ten.
The service uses PostgreSQL at its default READ COMMITTED isolation. The application reads a count, adds one in memory, and writes it back. Explain the execution, compare three ways to fix it, and say when each is appropriate. The counter is one row in one database. We are not discussing distributed event deduplication here.
Think first
Two requests load a balance of 8, both increment a Java entity and call repository.save(), and both commit. The value is 9 rather than 10. The system uses PostgreSQL READ COMMITTED. Show the interleaving, select the best fix for an additive counter, then change the scenario to two conflicting document edits. Explain the difference and how you would test concurrency.
Take a few minutes to form an approach, then compare it with the worked explanation.
Reveal the worked explanation
The transaction does not protect the calculation you made earlier
It is tempting to say that a transaction should have made the increment safe. Each request did use a transaction. That is precisely why this is an interesting bug. The boundary of the transaction and the calculation performed inside it are different questions.
Here is a possible interleaving:
Initial count: 8
Reviewer A reads 8 Reviewer B reads 8
A calculates 8 + 1 B calculates 8 + 1
A writes 9 and commits B writes 9 and commits
Final count: 9
Two concurrent transactions read the same counter value and both write nine, losing one increment, while an atomic SQL increment produces ten
Neither transaction committed half an update. They each committed a complete, valid write. The error comes from their shared assumption that the value read earlier was still the correct value to replace. The second write used a literal 9 calculated from stale application state.
PostgreSQL's READ COMMITTED behavior matters here. When two UPDATE statements target the same row, the later updater waits for the earlier one if necessary. Once the first finishes, the second can update the new row version. But if its assignment is SET review_count = 9, waiting has not fixed the stale calculation. PostgreSQL cannot infer that you intended two increments rather than two assignments. Its documented statement behavior explains the distinction. PostgreSQL transaction isolation
Tell the database the operation, not just the result
If the operation really means increment this number by one, express that as one statement:
UPDATE daily_review_totals
SET review_count = review_count + 1
WHERE team_id = :team AND day = :day
Concurrent updates to the same row are serialized by the row-update machinery. Under the stated PostgreSQL isolation level, the later statement can apply the increment to the version produced by the first. The result is ten, provided both statements commit successfully and both increments are legitimate. The same row can still become a contention bottleneck at high rates, but that is a capacity problem, not a missing increment.
Be precise about the scope of this fix. One SQL expression protects this update. It does not make two independent application statements into one atomic decision. If the counter must never exceed a limit, add the condition in the update and inspect the affected-row count. Checking the limit in a separate earlier query just recreates the race.
What if the change cannot be expressed as an increment?
Suppose a reviewer is editing a full report, not just adding one to a counter. Replacing the whole row with a copy loaded several minutes ago can overwrite another person's description. A simple atomic arithmetic expression no longer captures the intent.
Use a version check instead. When the editor loads the report, it receives version 12. Saving means, "apply my edit only if the record is still version 12."
UPDATE reports
SET body = :new_body, version = version + 1
WHERE id = :report_id AND version = 12
A returned count of zero means someone changed or removed the record before this edit. The application should report a conflict, reload, or let the user merge changes. It should not silently repeat the same overwrite against version 13, because that would discard information without asking. This is optimistic concurrency control, sometimes called compare and swap at the application-data level.
A third choice is locking the record while computing the decision, for example by selecting it FOR UPDATE in a short transaction. That can be right when several related fields must be checked and changed together. Its price is waiting, lock contention, and the possibility of deadlocks when the transaction locks several records. You also should not hold that database transaction open while a human edits a form.
Those three choices solve different problems. An atomic update expresses a simple transformation. A version condition detects a conflict the application may need to resolve. A row lock coordinates a short read-and-decide operation on one authority. None should be selected solely because a framework exposes an annotation named Transactional.
What evidence would you look for in production?
First inspect the actual generated SQL. An ORM may issue a SELECT, create an object with review_count=9, then send an UPDATE assigning a literal value. The code that looks like report.increment() might not compile into SET review_count = review_count + 1.
Next create a deterministic concurrency test. Let both requests read eight before either writes. Then release their updates and check the committed count. Avoid relying on a load test hoping for the race to happen eventually. Count lost updates in a purpose-built test and use database logs or SQL tracing to confirm which statements actually ran.
Finally ask whether each request represents a unique, legitimate review. If duplicate client requests are possible, that is a different concern from this lost-update race. The database can faithfully increment once for every request and still count the same logical review twice. Do not confuse concurrency control with business-operation identity.
The interview answer should name the stale read, show the two commits, choose an enforcement mechanism, and defend the resulting conflict behavior. But another question follows naturally. The database can let one transaction read an old row while a different transaction writes a new row. Where does it keep the older value, and when is it safe to delete it?
Go deeper
Isolation is not one universal lost-update switch
PostgreSQL REPEATABLE READ differs from READ COMMITTED. If one transaction tries to update a row that another transaction changed since its fixed snapshot, PostgreSQL can abort the would-be updater with a serialization error rather than allowing a stale overwrite. An answer that claims every MVCC implementation permits this exact two-update execution at every isolation level is misleading.
An optimistic version condition should be paired with a clear client response such as 409 Conflict or an equivalent domain result. It does not make user edits commutative. Automatically retrying a full replacement without resolving the newly observed state defeats the protection.
The locking alternative also needs a concrete lock scope. A transaction that grabs a row lock before calling a slow service creates unnecessary waiting. Keep the critical section short, with network work outside it unless the external operation participates in a genuinely coordinated protocol.
The counters themselves can become hot. Once the team reaches tens of thousands of updates per second, examine contention and whether exact immediate totals are required. Do not introduce sharded counters merely to fix a two-client lost-update bug.
Check your reasoning
Show both reads of eight followed by two literal assignments to nine, not a lost commit.
Explain why the database can serialize row updates without repairing stale application arithmetic.
Use an atomic SQL arithmetic update for increments and a version conditional write for conflicting document edits.
Explain when a short SELECT FOR UPDATE critical section is appropriate and the price of lock contention.
Propose a synchronization-barrier concurrency test and inspect the actual SQL emitted by the ORM.
Common mistake: Assuming the @Transactional annotation or a lock acquired during UPDATE automatically converts SET value=9 into two increments.
Read the answer explanation
9, because both statements assigned the same stale literal. — Serialization of row-level writes does not turn stale SET value=9 assignments into SET value=value+1 operations.
Apply it elsewhere
Two administrators edit the same deployment policy from separate browser tabs. How would your solution change from a numeric counter?
Compare your approach
Use a version or ETag precondition and return a conflict that allows meaningful merging. Do not automatically replay a stale full-object replacement.
One more constraint
When would an atomic counter itself become the throughput bottleneck?
Quick revision
Two clients read count 8 and each send an assignment to 9. Both transactions can commit under PostgreSQL READ COMMITTED, and the later assignment loses one increment. Use UPDATE count = count + 1 for a simple atomic increment, a version-checked update when edits can conflict, or a short row lock when the decision spans several fields. The exact SQL matters. A transaction annotation around stale application arithmetic is not sufficient. A concurrency test should force both reads before the writes and then verify the committed result.
