Optimistic version lost update: prove which writer ignored the revision

The symptoms

• •Final stored value matches an earlier read, not the most recent read, even though the application logged a successful UPDATE.
• •Two concurrent writers each return success, but the row ends in a state that corresponds to neither writer's full edit; one writer's diff is silently dropped.
• •UPDATE statements with no version predicate complete in microseconds under contention, while the same statement with WHERE version = $1 occasionally returns 0 rows even when the row exists.
• •Application logs show two writers that both report "updated 1 row" within the same millisecond window, after which the row reflects only one of the two payloads.
• •Conflict metrics or deadlocks are not reported, yet user-visible state regresses after concurrent edits.

Likely causes

• •An UPDATE statement is issued without a version predicate, so a writer that read version N overwrites a row that has already advanced to version N+1 by another writer.
• •A repository or ORM helper silently strips the version column from the WHERE clause, so the conditional update degrades into an unconditional UPDATE that matches every time.
• •The version column is read inside a transaction, but the UPDATE is issued after a long-running read that re-uses a stale snapshot, so the conditional check sees a row that no longer reflects the latest commit.
• •Two writers each load version N, both compute their own version N+1, and both succeed because the application computed the next version from the read value rather than relying on a database-managed version source.
• •Batch or bulk update paths bypass the version check entirely, while single-row paths enforce it, so lost updates only appear under specific code paths.
• •A retry loop re-reads the row and re-applies the change, but reads inside a non-repeatable-read isolation level see a different version than the one used to compute the write.

First ten minutes

1. 01Freeze writes to the affected row if the application supports a feature flag or maintenance switch, so the lost update does not progress while you collect evidence.
2. 02Identify the exact table and the exact version column from the schema; record table name, column name, column type, and any default or trigger that mutates it.
3. 03Pull the most recent N minutes of row history from the application log: who read the row, what version they saw, and the SQL they ultimately executed. Mark every UPDATE that lacks a version predicate.
4. 04Inspect PostgreSQL's pg_stat_database for tup_updated, tup_hot_updated, and xact_commit on the database that owns the table to confirm the volume and pattern of updates at the moment of the incident.
5. 05Inspect pg_locks for lock waits and granted locks on the row's relation during the incident window; record any blocking PIDs and the lock modes held.
6. 06Read the application's structured audit log to find the two writers whose updates landed in the same window; record user, request id, read version, written version, and timestamp for each.
7. 07Decide which writer ignored the revision by matching the persisted value and the read version recorded by each writer: the writer whose read version does not match the row's prior version is the suspect.

Evidence to collect

• •The persisted row's version before and after the incident, taken from a row history table, change-data-capture stream, or trigger-based audit log.
• •For each suspect writer: the read version, the read timestamp, the SQL text of the UPDATE, and the affected row count reported by the driver.
• •pg_stat_database counters for tup_updated and xact_commit on the target database, snapshotted at incident start and incident end, to bound the update volume.
• •pg_locks snapshots at the moment of the incident, including granted and awaited locks, relation OID, and the PID of the holding backend.
• •The application-level retry and re-read trace, which reveals whether a writer re-read the row after a partial failure.
• •The isolation level in effect for each transaction, taken from server configuration or session state, to rule out snapshot-based re-reads as an alternative explanation.

Where to look

• •The boundary between the application's read path and write path: the call site that issues SELECT followed by UPDATE, and the helper that constructs the UPDATE statement.
• •The repository or ORM mapping for the version column, where column annotations, interceptors, or update templates can silently drop the predicate.
• •The PostgreSQL catalog for the table: pg_attribute to confirm the version column and its type, and pg_class to confirm the relation OID used in pg_locks filtering.
• •The PostgreSQL statistics views: pg_stat_database for cumulative update activity, and pg_stat_user_tables for per-table update activity on the affected relation.
• •The application's request-scoped log fields, which carry the request id, user id, and the SQL statement bound parameters, to correlate readers and writers.
• •The database's session and transaction state, accessible via pg_stat_activity and the current_setting('transaction_isolation') output for the backends involved.

Diagnostic steps

1. 01Reproduce in a controlled environment: open two sessions, have each read the same row at version N, then issue an UPDATE without a version predicate; observe that both report a row count of 1 and the final row reflects only the second writer.
2. 02Compare row count semantics: a versioned UPDATE that returns 0 rows is the correct signal of a lost update; an unconditional UPDATE that returns 1 row is the failure signature. Capture the driver-level row count from each writer.
3. 03Diff the SQL text of each writer's UPDATE. If one UPDATE contains WHERE id = $1 and the other contains WHERE id = $1 AND version = $2, the unconditional writer is the one that ignored the revision.
4. 04Query pg_stat_database for tup_updated and xact_commit on the target database, then cross-check with pg_stat_user_tables for the table to confirm the update count matches the application's reported row count.
5. 05Take a pg_locks snapshot during a deliberate reproduction to see whether the second writer waited on the first; the absence of a lock wait, combined with both updates succeeding, is consistent with a missed version check rather than a deadlock.
6. 06Inspect pg_stat_activity for the two backends, recording their application_name, state, and current_setting('transaction_isolation') to rule out snapshot drift as the cause.
7. 07Audit the ORM or repository for code paths that bypass the version predicate, including bulk update methods, raw SQL helpers, and any "force update" or "skip concurrency check" option.
8. 08Match the persisted row's pre-update version to each writer's read version. The writer whose read version does not equal the pre-update version is the one that ignored the revision; the writer whose read version does equal it is the one that observed the correct state but was overwritten.

Common mistakes

• •Concluding that the database "lost the update" when the application issued an UPDATE without a version predicate; the database faithfully executed the statement it was given.
• •Pointing at lock contention or deadlock reports; an unconditional UPDATE that matches by primary key will not block on the same row, so absence of lock waits is expected.
• •Assuming the last writer to commit is the one that ignored the revision; commit order is not evidence of which writer held a stale read.
• •Reading only the application log and missing the SQL bound parameters; without the read version, you cannot tell which writer was stale.
• •Trusting an ORM's "automatic optimistic locking" without verifying that the version column is included in the WHERE clause of the generated UPDATE.
• •Conflating row count returned by the driver with semantic success; a row count of 1 from an unconditional UPDATE is exactly the failure signature.

Safe fixes

• •Conditional on evidence that the UPDATE lacks a version predicate: add WHERE version = $read_version to the UPDATE statement and treat a row count of 0 as a concurrency conflict that the caller must resolve by re-reading and re-applying.
• •Conditional on evidence that an ORM is dropping the version predicate: configure the mapping so the version column is part of the WHERE clause for every update path, including bulk updates, and add a test that asserts the generated SQL contains the version predicate.
• •Conditional on evidence that two writers computed their own next version: move version incrementation to a database-managed source such as a trigger or a row-level expression, so the next version is a function of the current row, not of the read snapshot.
• •Conditional on evidence that the retry path re-reads under a weaker isolation level: set the transaction isolation level explicitly for the read-modify-write transaction, and re-read inside the same transaction so the re-read is consistent with the write.
• •Conditional on evidence that audit data is missing: enable a row-level audit mechanism, such as a change-data-capture stream or a trigger-based history table, so every read and write is observable after the fact.
• •Conditional on evidence that bulk paths bypass the version check: route all writes through a single repository method that enforces the version predicate, and reject calls that supply a null or absent version.

Prove the fix

1. 01Run a deterministic reproduction: two sessions read the same row at version N, then each issues a versioned UPDATE. The second writer must receive a row count of 0 and surface a concurrency conflict to the caller; the first writer's change must persist.
2. 02Inspect the generated SQL for every update path, including bulk and force-update paths, and assert the presence of a version predicate in the WHERE clause.
3. 03Add a regression test that interleaves two writers, asserts the post-condition that the row reflects exactly one of the two writes plus a monotonic version increment, and fails if both writes report success.
4. 04Confirm via pg_stat_database and pg_stat_user_tables that the table's update count matches the application's reported successful update count, with no surplus updates attributable to lost-update overwrites.
5. 05Re-run the incident scenario in staging with the same request id correlation and verify that the audit log records a concurrency conflict for the second writer, not two silent successes.

Prevention and next steps

• •Enforce a single repository method for writes that requires a non-null read version and includes it in the WHERE clause, and reject any code path that bypasses the method.
• •Add a schema-level check that the version column is non-null and monotonic, and add a test that asserts every UPDATE on the table includes the version predicate.
• •Keep an audit trail of reads and writes, with request id, user, read version, and written version, so future lost updates can be attributed to a specific writer without relying on log archaeology.
• •Review ORM and bulk update paths during code review, with a checklist item that confirms the version predicate is present in the generated SQL.
• •Monitor the ratio of successful UPDATEs to concurrency-conflict responses over time, and alert on a sudden drop in conflicts that coincides with sustained write traffic, which can indicate that the version check has been disabled.

Safe commands and checks

SELECT datname, tup_updated, xact_commit FROM pg_stat_database WHERE datname = current_database();
SELECT relname, n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = '<affected_table>';
SELECT pid, granted, mode, relation::regclass FROM pg_locks WHERE relation = '<affected_table>'::regclass;
SELECT pid, application_name, state, query FROM pg_stat_activity WHERE datname = current_database() AND state <> 'idle';
SELECT attname, atttypid::regtype FROM pg_attribute WHERE attrelid = '<affected_table>'::regclass AND attname = '<version_column>';
SHOW transaction_isolation;
SELECT current_setting('transaction_isolation');