Lost updates: expose the read-modify-write race

The symptoms

• •A counter, balance, inventory, or status field appears correct in isolation but drifts or "goes backwards" under concurrent traffic from two or more writers.
• •An UPDATE statement seems to be ignored: a row's value matches a value observed minutes earlier, even though at least one intervening UPDATE was committed by another transaction.
• •Application-layer audit fields such as `updated_at` or `updated_by` do not reflect the most recent logical operation, or a "version" column increments but the stored value still equals a prior read.
• •Read-committed workloads exhibit non-deterministic losses that disappear when the workload is serialized onto a single connection or when `SELECT ... FOR UPDATE` is added.
• •Reports that sum a column (e.g., SUM(balance)) disagree with the sum of the increments the application logged, with the difference equal to one or more concurrent transactions.

Likely causes

• •Application code performs an unguarded SELECT followed by a separate UPDATE on the same row, with no row-level lock or version check, so two transactions can both read the pre-increment value and both write the post-increment value.
• •Reliance on PostgreSQL read-committed isolation, which only prevents stale reads within a single statement but does not prevent a later statement from writing based on a value read before a concurrent commit.
• •Using ORM "save" semantics (load entity, mutate in memory, persist) without optimistic concurrency tokens such as a `version` or `updated_at` guard in the WHERE clause.
• •Retry loops around serialization failures that re-read the same row but then perform arithmetic on the freshly read value, masking but not preventing the race during the original window.
• •Splitting one logical mutation across multiple statements (read, branch, write) where only the write is wrapped in a transaction, expanding the window in which another writer can commit.
• •Bulk update jobs that compute a delta from a snapshot table and write back, while interactive transactions mutate the same rows concurrently, causing the bulk write to clobber the interactive change.

First ten minutes

1. 01Stop and define the exact row, the exact field, and the two competing transactions: identify the table, primary key, the column being mutated, and the application endpoints or jobs that write to it.
2. 02Confirm the isolation level actually in effect on the connection (for PostgreSQL, run `SHOW transaction_isolation;` in the same session type the application uses, not in psql defaults).
3. 03Capture the transaction boundaries: locate the BEGIN/COMMIT pair or the ORM `with transaction:` block that contains the SELECT and the UPDATE; the lost update lives inside this window.
4. 04Inspect the UPDATE statement text in slow-query or statement logs and confirm whether it carries a version predicate such as `WHERE id = $1 AND version = $2` or a row lock such as `FOR UPDATE`.
5. 05Reproduce the race deterministically by running two concurrent sessions against the same row with `psql`, using `\set PROMPT1` and `\set PROMPT2` to keep the sessions distinguishable, and confirm a value is lost.
6. 06Check whether any ORM event handler, trigger, or before-write hook re-reads the row inside the same transaction; such a re-read can mask the race in logs while still allowing a stale write.

Evidence to collect

• •The text of the SELECT that fetched the row prior to the UPDATE, including whether it used `FOR UPDATE`, `FOR NO KEY UPDATE`, `FOR SHARE`, or no lock clause at all.
• •The text of the UPDATE, including any version or timestamp predicate in the WHERE clause, and the value bound to it (e.g., the `version` read earlier).
• •The isolation level of the session that issued the UPDATE, taken from `SHOW transaction_isolation` in the same connection class.
• •Two timestamps: the time of the SELECT and the time of the UPDATE, demonstrating the gap during which a concurrent writer could commit.
• •The number of rows affected by the UPDATE (rowcount); a rowcount of 1 with a stale value bound to the parameters is the smoking gun for a lost update.
• •For PostgreSQL, the presence or absence of serialization failures (SQLSTATE `40001`) in the application log, and whether the application retries or surfaces them.

Where to look

• •At the application-to-database boundary: the data access layer or ORM repository methods that perform "load entity, mutate, persist" without a concurrency token.
• •Inside the transaction scope: the point between the SELECT and the UPDATE where no row lock is held, which is the precise window in which a concurrent commit can land.
• •At the schema boundary: tables that lack a `version`, `updated_at`, or `xmin`-based predicate column, making optimistic concurrency impossible without a migration.
• •At the connection boundary: connection pools that acquire separate physical connections for the SELECT and the UPDATE, so the row lock cannot span both statements.
• •At the trigger boundary: BEFORE UPDATE triggers that re-read or re-validate the row, which can either widen the race or, if they assert on a version, narrow it.
• •At the serialization-failure boundary: PostgreSQL error stream filtered for SQLSTATE `40001`, which indicates a serialization anomaly was detected at the repeatable-read or serializable level but the application chose the wrong isolation level to benefit from it.

Diagnostic steps

1. 01Prove the race exists by running two parallel sessions that each `BEGIN; SELECT col FROM t WHERE id = <pk>; UPDATE t SET col = col + 1 WHERE id = <pk>; COMMIT;` and observe that the final value of `col` equals the starting value plus 1 instead of plus 2.
2. 02Add `FOR UPDATE` to the SELECT in session A and rerun the parallel test; if the final value now equals starting value plus 2, the lost update is confirmed to be unguarded-read driven, and `FOR UPDATE` is a viable guard.
3. 03Switch the session to `SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;` (or SERIALIZABLE) and rerun the parallel test; if PostgreSQL now raises SQLSTATE `40001` for one of the transactions, the anomaly is detectable by the engine but not by the application code's chosen isolation.
4. 04Inspect the application's UPDATE statement for a version predicate: if `WHERE id = $1 AND version = $2` returns rowcount 0 on the loser, the optimistic guard works and the application must translate that 0 into a retry.
5. 05Compare the rowcount semantics between the ORM and raw SQL: confirm whether the ORM treats a rowcount of 0 as success; many do, which is how an optimistic guard silently loses updates.
6. 06Audit the retry path: if a `40001` or rowcount-0 triggers a retry that re-reads and re-applies the delta on the new value, the race is closed; if the retry replays the original delta against the original stale value, the race is preserved.

Common mistakes

• •Concluding that PostgreSQL "uses MVCC so it is safe" without checking the isolation level: MVCC prevents dirty reads but does not, by itself, prevent a lost update under read-committed.
• •Adding `FOR UPDATE` only to the SELECT inside a function whose caller has already mutated state in another transaction, which leaves the race window open in the caller, not the function.
• •Trusting an ORM's "save returns the updated row" success as proof of no loss; the ORM may report success while having written a value derived from a stale read.
• •Assuming that a unique constraint on a related table prevents the lost update; uniqueness protects against duplicates, not against clobbering a numeric or status field.
• •Wrapping only the UPDATE in a transaction while leaving the SELECT outside it, so the row lock cannot bridge the read and the write.
• •Retrying on serialization failure but recomputing the delta from a cached application variable rather than re-reading the row, which re-introduces the race on the retry attempt.

Safe fixes

• •If evidence shows the read and write happen in the same transaction and the engine is PostgreSQL, change the SELECT to `SELECT ... FOR UPDATE` so the second writer blocks until the first commits, then adopt the now-current value before computing the new one.
• •If the application is structurally unable to hold a row lock across the read (long think-time, external calls), add a `version` (or `updated_at`) column and rewrite the UPDATE as `UPDATE t SET col = <new>, version = version + 1 WHERE id = $1 AND version = $2`; treat a rowcount of 0 as a retryable conflict.
• •If the workload is mostly short transactions and the engine supports it, escalate the connection to `SET TRANSACTION ISOLATION LEVEL SERIALIZABLE` and handle SQLSTATE `40001` with bounded retries whose retry body re-reads the row before re-applying the delta.
• •If the mutation is a pure increment (`SET col = col + 1`), prefer a single-statement UPDATE so the engine evaluates the expression against the latest committed row, eliminating the read-modify-write window entirely; this is safe only when no business rule depends on the prior value.
• •If the row is partitioned or sharded, apply the guard per-partition and confirm the cross-partition transaction does not reintroduce the race at a coarser boundary.
• •Each fix is conditional on the diagnostic evidence: do not add `FOR UPDATE` to a workload already at SERIALIZABLE without first checking for `40001` handling, and do not switch to SERIALIZABLE if the application cannot retry, because the engine will then surface conflicts as errors the code cannot absorb.

Prove the fix

1. 01Run the two-session race reproducer again under the proposed fix; the final value of the row must equal the starting value plus the number of committed transactions (N writers produce +N), with no value "going backwards" relative to any committed intermediate state.
2. 02For an optimistic-version fix, force a conflict by manually setting the in-memory version to a stale value in one session and committing; the loser's UPDATE must return rowcount 0 and the application must either retry or surface the conflict, never a successful stale write.
3. 03For a serializable-isolation fix, run the reproducer under `SET TRANSACTION ISOLATION LEVEL SERIALIZABLE` and confirm exactly one transaction commits and the other receives SQLSTATE `40001`; a successful commit by both indicates the isolation level is not actually in effect.
4. 04Verify in the statement log that the SELECT and the UPDATE now share a transaction id (`backend_xid` in `pg_stat_activity` during the window), proving the lock or predicate spans the read and the write.
5. 05Run a sustained concurrency soak (for example, N concurrent writers for M seconds) and assert that the post-run sum of increments equals the post-run value of the column; any discrepancy is a residual lost update.
6. 06Add a regression check: a CI test that runs the two-session race against a throwaway schema and asserts rowcount and final value; fail the build if the test loses an increment, so future refactors cannot silently re-introduce the race.

Prevention and next steps

• •Adopt a default code-review rule that any UPDATE on a row previously SELECTed in the same request must either use `FOR UPDATE`, carry a version predicate, or be expressible as a single-statement delta.
• •Maintain a schema convention: tables that are written by more than one caller expose a `version` or `updated_at` column, and the data access layer refuses UPDATEs whose WHERE clause omits the predicate.
• •Set the application's default isolation level explicitly per connection class and document it, so "read-committed because that is the default" cannot silently widen the race window.
• •Wire SQLSTATE `40001` and optimistic-conflict rowcount-0 into a single retry policy whose retry body always re-reads the row before re-applying the delta, and alert if the retry rate exceeds a baseline.
• •For pure increments and decrements, prefer single-statement `SET col = col + <delta>` UPDATEs and forbid application-side read-then-add patterns in the style guide.

Safe commands and checks

psql -h <host> -p <port> -U <user> -d <db> -c "SHOW transaction_isolation;"
psql -h <host> -p <port> -U <user> -d <db> -c "SELECT pid, state, xact_start, query FROM pg_stat_activity WHERE state <> 'idle';"
psql -h <host> -p <port> -U <user> -d <db> -c "SELECT relname, n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = '<table>';"
psql -h <host> -p <port> -U <user> -d <db> -c "SELECT xmin, * FROM <table> WHERE id = <pk>;"
psql -h <host> -p <port> -U <user> -d <db> -c "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN; SELECT col FROM <table> WHERE id = <pk> FOR UPDATE; UPDATE <table> SET col = col + 1 WHERE id = <pk>; COMMIT;"
psql -h <host> -p <port> -U <user> -d <db> -c "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN; SELECT col FROM <table> WHERE id = <pk>; UPDATE <table> SET col = col + 1 WHERE id = <pk>; COMMIT;"
grep -n "40001" <application_log_path> | head -n 50
grep -n "FOR UPDATE\|version\s*=\s*\|updated_at\s*=\s*" <slow_query_log_path> | head -n 50