How to test lost-update protection with concurrent writers

The symptoms

• •Two concurrent writers both report success, but the final stored row matches only the second writer's payload with the first writer's field changes silently dropped.
• •One writer receives a stale-revision error such as a version mismatch, optimistic concurrency exception, or "0 rows updated" while the other writer's changes persist.
• •Audit or read-after-write comparison shows a last-writer-wins outcome that conflicts with the application's intended merge or reject semantics.
• •Intermittent duplicate or shadowed rows appearing when two transactions read the same row, derive a child row, and commit nearly simultaneously.
• •Application logs show a successful UPDATE returning affected-rows count of zero, which is often the database's quiet way of reporting a lost update.

Likely causes

• •Read-modify-write cycle performed outside a database-enforced isolation level that would otherwise detect the conflict (for example, code that reads, mutates in memory, and writes without a version guard).
• •Version or revision column incremented on write but not included in the UPDATE predicate, so the database silently overwrites concurrent changes.
• •ORM-level optimistic concurrency disabled or configured to track only timestamps with insufficient resolution, allowing two writes inside the same timestamp window.
• •SELECT-then-UPDATE pattern executed in two separate transactions instead of a single atomic statement or row lock.
• •Application retry logic that re-reads the row, recomputes the delta, and writes again without checking that the original read's precondition still holds.

First ten minutes

1. 01Decide whether the application is expected to reject the loser outright, surface a conflict to the caller, or merge the two writes. The decision drives which guard you must verify.
2. 02Identify the canonical record under test: the table name, the revision or version column, and whether the application uses optimistic concurrency tokens or pessimistic row locks.
3. 03Confirm the database's default transaction isolation level for the connection under test, since lost-update behavior depends on whether the platform protects against it by default.
4. 04Collect a baseline row by reading the current revision or version value plus all mutable fields, so the verification run has a known starting state.
5. 05Sketch the two concurrent writer actions: what each reads, what each computes, and what each commits, so the test can assert the expected winner and loser outcome.

Evidence to collect

• •Schema definition for the target table including the version, revision, or xmin-style column used for concurrency control.
• •Application or repository code path that performs the read-modify-write, specifically the UPDATE statement and its WHERE clause.
• •Database transaction isolation level configured on the connection, pool, or session used by the writers.
• •Affected-rows count and any database error code returned to the application on each writer's UPDATE attempt.
• •Final row state and revision value after both writers complete, compared against the expected winner-loser outcome.

Where to look

• •At the repository or data-access layer: locate the UPDATE statement and confirm whether the version column is part of the WHERE predicate and the SET clause.
• •At the ORM mapping: inspect the concurrency token configuration, the dirty checking behavior, and the merge policy on flush.
• •At the transaction boundary: review where the transaction begins, what isolation level is requested, and whether SELECT ... FOR UPDATE is used.
• •At the database statistics view: consult the monitoring statistics documentation for the platform under test to confirm transaction-level counters relevant to UPDATE activity and lock waits.
• •At the application's error handling: trace what happens when an UPDATE returns zero affected rows or a unique constraint violation.

Diagnostic steps

1. 01Reproduce the concurrent writers in a controlled harness: read the same row in two sessions, mutate different fields in memory, then commit both UPDATEs as close to simultaneously as possible.
2. 02Inspect the UPDATE statement's WHERE clause. If the version column is absent, the database cannot distinguish a stale write from a fresh one, and the database will silently accept the loser's payload.
3. 03Compare the application's isolation level to the platform's default. If the application relies on the default, confirm whether the platform's default protects against lost updates for the workload in question.
4. 04Check whether the application treats a zero affected-rows count as success. A successful UPDATE that matched no rows is often the silent lost-update signature.
5. 05Replay the harness with an explicit optimistic guard: include the previously-read version in the WHERE clause and assert that exactly one writer succeeds while the other receives a version mismatch or zero affected rows.
6. 06Replay the harness with an explicit pessimistic guard: SELECT ... FOR UPDATE the row in transaction A and confirm transaction B blocks or is rejected until A commits.
7. 07Differentiate a true lost update from a unique-constraint conflict: a lost update leaves both transactions committing successfully, while a constraint conflict surfaces as a duplicate key error.

Common mistakes

• •Treating a successful UPDATE with zero affected rows as a no-op rather than as evidence that the row was stale or deleted by another writer.
• •Using wall-clock timestamps as the concurrency token at sub-millisecond resolution, which can collapse two distinct writes into the same timestamp and re-introduce the lost update.
• •Relying on a higher isolation level without verifying that the application's read-modify-write path is contained inside a single transaction with the correct snapshot semantics.
• •Implementing retry logic that re-reads and re-writes without re-asserting the original precondition, which converts a detected lost update into an undetected overwrite.
• •Testing only the single-writer happy path and never executing two concurrent writers, so the absence of a concurrency token goes undetected until production.

Safe fixes

• •Add the version or revision column to the UPDATE WHERE clause and increment it in the SET clause, so a stale writer's UPDATE affects zero rows and is reported as a concurrency conflict.
• •If the platform supports row-level optimistic concurrency tokens at the ORM layer, enable the concurrency token mapping on the entity and remove any code that bypasses it with raw UPDATE statements.
• •If the workload genuinely requires pessimistic locking, wrap the read-modify-write in a transaction and acquire a row lock with SELECT ... FOR UPDATE (or platform equivalent) before computing the new value.
• •Configure the connection's transaction isolation level explicitly for the data-access path rather than relying on the platform default, and document the chosen level alongside the concurrency guard.
• •Translate a zero affected-rows count into an application-visible concurrency error so the caller can decide between merge, retry-with-re-read, or reject, rather than silently accepting the loser's payload.

Prove the fix

1. 01Run the concurrent-writer harness and assert that exactly one writer succeeds and the other receives a version mismatch, an affected-rows count of zero, or an explicit concurrency exception.
2. 02Read the final row and assert that its mutable fields match the winner's payload and that the version column has incremented by exactly one from the baseline.
3. 03Repeat the harness under simulated contention (for example, ten pairs of writers on the same row) and assert that the sum of successful winners equals the number of starting baselines, with no silent overwrites.
4. 04Inspect the database's transaction statistics for the test window and confirm UPDATE activity matches the expected number of winning writes, with no anomalous duplicate or shadowed rows.
5. 05Add a regression check that any future change to the repository layer must keep the version column in the UPDATE WHERE clause, for example by asserting on the rendered SQL during a test run.

Prevention and next steps

• •Treat the version column as a mandatory part of the UPDATE predicate and codify it in repository templates so new queries cannot omit it.
• •Make concurrency-token handling a documented part of the data-access contract, including what the application does on a zero affected-rows count.
• •Include a concurrent-writer scenario in the standard test suite for any entity that supports multi-user edits, not just the single-writer happy path.
• •Monitor UPDATE activity and affected-rows anomalies against the monitoring statistics surface so a regressed lost-update guard is visible operationally, not only in code review.

Safe commands and checks

Read the target table schema and identify the version, revision, or xmin-style column: <code>SELECT column_name, data_type FROM information_schema.columns WHERE table_name = '<table_name>' ORDER BY ordinal_position;</code>
Inspect the database's default transaction isolation level: <code>SHOW transaction_isolation;</code>
Capture the baseline row and revision before the concurrent-writer harness: <code>SELECT id, <mutable_fields>, version FROM <table_name> WHERE id = <row_id>;</code>
Render the application's UPDATE statement with parameter values to confirm whether the version column appears in the WHERE clause: <code>SELECT current_query() FROM pg_stat_activity WHERE pid = <pid>;</code>
Confirm UPDATE activity and lock waits for the test window from the database's statistics view, per the platform's monitoring stats documentation.