Lost-update checklist

The symptoms

• •A row's column value reverts to an older value after a brief window during which another transaction or process reported a successful update, with no error returned to either client.
• •Two clients each observe a successful UPDATE returning one affected row, yet the final persisted state matches only the last commit, while the other writer's change is absent from any later SELECT.
• •Audit or version columns do not advance as expected: an updated_at timestamp, a version integer, or a row_hash repeats across writes that the application believes succeeded.
• •Application logs show no exception, no deadlock, and no serialization failure, yet business users report that edits "did not stick" or were "rolled back by something else."
• •Conflict rates in retry or idempotency metrics rise even though no network or timeout errors are visible, suggesting the database accepted both writes in isolation but the second overwrote the first.
• •On PostgreSQL, pg_stat_database conflicts counters do not move, but the row's xmin and xmax values in page inspection tools indicate a transaction that read the pre-image, wrote, and was superseded.

Likely causes

• •Read-modify-write cycles executed outside any explicit row lock, with no version or timestamp predicate in the WHERE clause, so the second writer's UPDATE matches the same row the first writer already modified.
• •Connection pools or retry middleware silently replaying a previously committed UPDATE against a row whose state has already advanced, because the application keys off an in-memory snapshot rather than a database-returned row version.
• •Read Committed isolation being assumed to provide conflict detection when only PostgreSQL's Repeatable Read or Serializable levels, or an explicit SELECT ... FOR UPDATE, would surface a concurrent modification as a serialization failure.
• •Application-level caching layers returning a stale entity that is then mapped back onto a fresh UPDATE, so the write itself succeeds but carries pre-conflict field values.
• •Bulk update paths that read a set of rows, mutate them client-side, and then issue per-row UPDATE statements without re-reading or re-checking a version column between read and write phases.
• •Read-write splitting topologies where the write is routed to a primary but the prior read came from a replica that lagged or had already evicted the row from its snapshot, allowing a stale pre-image to be written back.

First ten minutes

1. 01Freeze further writes to the suspect table only if your environment supports a maintenance mode; otherwise, capture a timestamp window so any further lost-update evidence can be correlated.
2. 02Pull the transaction isolation level from the session that produced the lost write: on PostgreSQL, query pg_settings for default_transaction_isolation and confirm the value against the application's documented expectation.
3. 03Identify every code path that touches the affected row and classify each as read-then-write, pure UPDATE with predicate, or bulk re-write; lost updates are only possible on paths that re-read state before writing.
4. 04Record the exact UPDATE statement, the bound parameter values, the connection or backend identifier if available, and the commit timestamp; do this before any application restart so the evidence is not lost.
5. 05Check whether the schema carries a version, updated_at, or etag column that the application is supposed to use as a concurrency token, and confirm whether the predicate in the failing UPDATE references it.
6. 06Look at pg_stat_activity for the affected database at the suspected window to confirm overlapping sessions and to note wait_event and state values, which indicate whether lock waits or idle-in-transaction states were involved.

Evidence to collect

• •Per-writer sequence: the SELECT that fetched the row, the in-memory mutation, and the UPDATE that wrote it back, with each step's timestamp and transaction identifier.
• •Row-level forensic data: the current xmin, xmax, and any system or application version columns on the row, compared against the values the application believed it was writing.
• •Session-level data from pg_stat_activity and pg_stat_statements for the suspected window, including the query text, execution counts, and any rows-returned or rows-modified deltas.
• •Application-side evidence: the ORM or repository log lines that show the entity as loaded versus the entity as persisted, including any automatic dirty-checking output and the SQL emitted.
• •Topology evidence: which connection string served the read and which served the write, and whether any replica was in the read path prior to the write being issued.
• •Reproduction evidence: a deterministic script or transaction trace that demonstrates two writers, an interleaving, and the final row state matching only the last commit.

Where to look

• •At the application-to-database boundary: the ORM's session or unit-of-work, the repository's update method, and any cache layer that materializes entities between read and write.
• •At the database transaction boundary: the SET TRANSACTION ISOLATION LEVEL statement or session default, the COMMIT boundaries, and any explicit advisory or row locks taken before the UPDATE.
• •At the row itself: system columns xmin and xmax exposed by PostgreSQL, plus any application-managed version or updated_at column that can serve as a conflict token.
• •At the statistics boundary: pg_stat_database for transaction commit and rollback counts, pg_stat_user_tables for live and dead tuple changes, and pg_locks for any held tuple-level locks during the suspect window.
• •At the routing boundary: connection pool configuration, read replica routing rules, and any logic that decides whether a SELECT goes to a primary or a standby.

Diagnostic steps

1. 01Compare the application-reported final state with the row's xmin or application version column; a match means the application believed it wrote the value that actually persisted, ruling out a logic bug and pointing at concurrency.
2. 02Reproduce the lost update under controlled isolation: open two sessions, set an explicit isolation level that matches production, perform the read-modify-write, and observe whether the database raises a serialization error or simply overwrites silently.
3. 03Audit every UPDATE statement that touches the suspect table and classify it as version-aware (predicate includes version or updated_at) or version-blind (predicate keys only on primary key or business key); lost updates concentrate on version-blind paths.
4. 04Inspect pg_locks snapshots during the suspect window for tuple-level locks; absence of tuple locks during overlapping transactions is consistent with a read-modify-write race rather than an explicit locking discipline.
5. 05Trace each transaction's lifecycle in pg_stat_activity; an idle-in-transaction state held across a long read-modify-write window is a strong indicator that another writer was able to slip in unobserved.
6. 06Cross-check pg_stat_statements for the table: a sudden increase in calls returning rows where the WHERE clause matched by primary key only, with no version predicate, suggests a path that bypasses optimistic concurrency.
7. 07If a read replica is in the read path, confirm replica lag at the timestamp of the read; a non-zero lag at the moment of the SELECT is sufficient to explain why the subsequent UPDATE carried stale data.
8. 08Differentiate from non-concurrency causes: confirm there is no trigger that overwrites columns on UPDATE, no BEFORE UPDATE that re-derives a value from older state, and no replication or restore operation that clobbered recent changes.

Common mistakes

• •Concluding that "the application wrote the correct value" without inspecting the row's xmin or version column, which hides the fact that another writer's commit landed between the application's read and write.
• •Treating Read Committed as if it provides write-write conflict detection; on PostgreSQL, Read Committed allows the second UPDATE to proceed silently, and only Repeatable Read or Serializable will raise a serialization failure on true conflicts.
• •Adding a retry loop without a concurrency token, which only masks the symptom by re-running the same read-modify-write and can still lose updates if the retry's SELECT happens to read the same stale snapshot.
• •Assuming connection pooling guarantees serial execution of transactions from the same application instance; pool checkout and checkin interleavings are exactly what allow the race.
• •Reading from a replica and writing to the primary without accounting for replica lag, then concluding the database "lost" data when the application itself wrote a stale pre-image.
• •Looking at pg_stat_database conflicts for serialization failures without realizing those counters advance only for certain isolation levels; their absence does not prove no conflict occurred.

Safe fixes

• •Introduce a version or updated_at predicate on the UPDATE so the statement matches only the row state the application actually read; if zero rows are affected, raise a concurrency conflict rather than silently retrying.
• •Wrap read-modify-write sequences in an explicit SELECT ... FOR UPDATE on the row, scoped to a single transaction, so the second writer blocks until the first commits and then sees the new state.
• •Raise the isolation level for the affected transaction to Serializable or Repeatable Read where the workload permits, so PostgreSQL returns a serialization failure (SQLSTATE 40001) on overlapping read-write conflicts instead of allowing a silent overwrite.
• •Route reads that feed a subsequent UPDATE to the primary rather than a replica, eliminating stale-pre-image writes caused by replication lag; keep analytical reads on replicas if needed.
• •Disable or constrain any cache layer that returns an entity without exposing the version token used at read time, so the write cannot carry pre-conflict field values back to the database.
• •Add a database-level check or trigger that requires a non-decreasing version column on UPDATE for the affected table, turning a silent overwrite into a constraint violation that surfaces in application logs.

Prove the fix

1. 01Run a controlled two-session race that mirrors the production interleaving: confirm that, with the fix in place, the second writer now receives either zero rows affected (when using a version predicate) or a serialization failure (when using Serializable), never a silent overwrite.
2. 02Capture the row's xmin or version column before and after the race: assert that the value advances monotonically with each successful writer and that the final committed xmin belongs to the writer that the application believes succeeded.
3. 03Replay production traffic against a staging database with pg_stat_statements enabled and verify that UPDATE statements on the suspect table now include the version or updated_at predicate in the WHERE clause and that affected-row counts match application expectations.
4. 04Monitor for an extended period and confirm that the application's concurrency-conflict metric rises only when real conflicts occur and that subsequent retries read the post-conflict state rather than a stale snapshot.
5. 05Confirm that the database's transaction commit and rollback counters advance in proportion to application write attempts, with no skew that would indicate commits are being silently superseded by later transactions on the same row.

Prevention and next steps

• •Make a version or updated_at predicate mandatory in code review for any UPDATE statement that targets the suspect table, and reject submissions that key only on primary key or business key.
• •Document the expected isolation level per service or repository, and assert it at session start so a configuration drift cannot silently downgrade conflict detection.
• •Separate read-only connections from read-write connections at the routing layer, and ensure that any session allowed to issue an UPDATE has been bound to the primary for both its prior SELECT and its write.
• •Keep idle-in-transaction timeouts aggressive on application connections so long read-modify-write windows cannot be exploited by concurrent writers.
• •Periodically audit pg_stat_statements and pg_locks for the suspect table to detect patterns of version-blind updates or prolonged tuple-lock waits before they manifest as lost updates in production.

Safe commands and checks

SELECT name, setting FROM pg_settings WHERE name IN ('default_transaction_isolation','default_transaction_read_only');
SELECT pid, datname, usename, application_name, state, wait_event_type, wait_event, xact_start, query_start, LEFT(query, 200) FROM pg_stat_activity WHERE datname = current_database() ORDER BY xact_start;
SELECT relation::regclass, mode, granted, pid FROM pg_locks WHERE relation = 'public.<table_name>'::regclass;
SELECT calls, total_exec_time, rows, query FROM pg_stat_statements WHERE query ILIKE '%UPDATE <table_name>%' ORDER BY calls DESC LIMIT 20;
SELECT xmin, xmax, * FROM public.<table_name> WHERE <pk_column> = '<pk_value>';
SELECT datname, xact_commit, xact_rollback, conflicts, deadlocks FROM pg_stat_database WHERE datname = current_database();
SELECT relname, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = '<table_name>';