Lost Updates in PostgreSQL: 253 Concurrency Tests

What happens when a database reports two successful transactions but preserves only one of their intended changes? In an isolated PostgreSQL 18.6 laboratory, I reproduced that outcome with two simultaneous writers. Both transactions committed. Both generated completion receipts. The stored counter increased by one instead of two. Nothing in the successful commit responses warned the client that application-level work had been overwritten. The experiment was designed to expose lost updates, distinguish detection from actual business completion, and measure the cost of preventing incorrect results under contention.

My investigation of lost updates was not a simulated benchmark built from assumed timings. I used an executable, frozen concurrency harness against a real PostgreSQL server, ran 253 predefined cases, repeated the entire matrix in a second execution, and checked the raw database events against persisted state. The central finding is more consequential than a single latency ranking: a system may acknowledge every write while failing to preserve every logical operation. Conversely, a stronger isolation level can protect the data precisely by refusing work that an application then needs to retry.

If You Only Read One Thing

Treat data correctness, business-operation completion, and throughput as three separate measures. A high commit rate does not disprove lost updates. A conflict error does not mean work was recovered. A fast transaction attempt does not mean a logical operation, including all retries, finished quickly. Each of those statements became visible in the same controlled laboratory rather than being assumed from database documentation.

In the deliberately unsafe READ COMMITTED lost updates workload, all 600 offered operations committed at every worker setting. With one worker, no increments disappeared. With four workers, 373 intended increments were lost; with eight, 489 were lost. Switching to a relative atomic UPDATE preserved all 600 increments at both four and eight workers. Optimistic checks and stronger isolation also protected state, but some configurations left most requested operations unfinished when retries were disabled. Those are different engineering outcomes, not merely different ways of describing “success.”

Research Question, Scope, and What Was Actually Tested

My research question was specific: under concurrent access to the same logical data, when do lost updates occur, and what changes when I use atomic SQL, explicit locking, optimistic version checks, REPEATABLE READ, SERIALIZABLE, or full-transaction retry? I treated transaction isolation as an implementation mechanism rather than a label that automatically guarantees a correct business workflow. The experiment also included a separate write-skew scenario, because keeping a single row correct is not equivalent to enforcing an invariant across several rows.

The test environment was an Apple M4 Pro Mac with 14 logical CPUs, 48 GiB of RAM, macOS 27.2 on ARM64, Python 3.9.6, PostgreSQL 18.6, and matching libpq 18.6. PostgreSQL ran inside a fresh temporary, private Unix-socket-only cluster with TCP listening disabled. A frozen Python harness used genuine concurrent database connections and controlled scheduling gates to force critical interleavings. No client database or production service was involved.

The first native attempt failed during server startup because of a macOS locale issue. Supplying LC_ALL=en_US.UTF-8 to the invoking process resolved that environment problem without changing the test harness, workloads, acceptance criteria, or SQL logic. I retained that failure in the research record. The primary run and the separate repetition then completed successfully. This distinction matters: an initially failed setup is not a failed transaction-isolation hypothesis, and fixing a host prerequisite must not quietly rewrite the experiment.

A PASS in the lost updates research matrix means that the predeclared expectation for the scenario matched the observed database state. It does not mean all requests were accepted or completed. Some tests were specifically designed to confirm expected aborts, exhausted retries, and broken business invariants. In the primary run, 242,934 raw events were recorded; the replication produced 240,666. The verification tooling re-derived case outcomes, counts, timestamps, and SQLSTATE errors from those records.

How the performance cells were measured

The lost updates performance matrix tested 13 strategy variants, one, four, or eight concurrent workers, and five repetitions of 120 offered operations per configuration. That gives 600 offered logical operations for each strategy-and-worker setting. The deliberately contested object was one hot counter row with no think time. Throughput used the measured monotonic interval; attempt p50 and p95 used the recorded database attempt timings; logical-operation latency included subsequent attempts and backoff where applicable. Values in the tables are medians across the five repetitions, not synthetic averages.

The individual performance windows for the lost updates comparisons were extremely short, approximately 17–52 milliseconds. They are useful for observing sharply contrasting correctness behavior under one controlled workload, but the measured operations-per-second values should not be interpreted as sustained production capacity. Between the two full executions, some cell-level throughput medians varied by as much as roughly 20%. I preserve that variability in the conclusions rather than promoting the highest number as a universal benchmark.

Lost Updates in the First Failure: Two Commits, One Persisted Increment

Why an apparently reasonable read-modify-write sequence fails

Consider a counter starting at zero. Transaction A reads zero and computes one. Transaction B also reads zero and computes one. A writes one and commits. B then writes its already-computed absolute value, one, and commits. Neither transaction has to fail, because both updates can be valid SQL statements even though B used a stale application-side value. The final state is one. The business intent was two increments. This is the concrete shape of the lost updates problem: the database did not lose a committed row version; the application lost the meaning of one concurrent operation.

-- Simplified illustration of the intentionally unsafe shape
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT value FROM counters WHERE id = 1; -- both sessions read 0
-- Application calculates next_value = previously_read_value + 1
UPDATE counters SET value = :next_value WHERE id = 1;
COMMIT;

The controlled lost updates schedule made the race deterministic with synchronization gates, instead of relying on favorable timing. In every repetition of the unsafe READ COMMITTED case, the two writers committed and both operation receipts existed, while the persisted value remained one. An important audit detail: the test row’s version field became two, not one. The frozen SQL incremented the version relatively even while assigning the counter value from a stale absolute read. A sentence in the original hypothesis expected version one; the measured state is value one, version two. This documentation discrepancy was retained, because the actual lost-update conclusion concerns the value, not the version column.

Lost updates became more visible as concurrency increased

All 600 operations produced successful commit receipts in every row of this table. The four-worker case preserved only 227 increments, while the eight-worker case preserved only 111. The replication returned 372 lost increments at four workers and 489 at eight workers. These numbers describe one deliberately high-contention counter workload, not a general PostgreSQL failure rate. They do show why monitoring only SQL errors or successful commits cannot expose all lost updates: the problem may be entirely invisible to those metrics.

The unsafe eight-worker lost updates configuration reported approximately 4,997 committed operations per second but only 913 persisted increments per second. Calling the first figure successful business throughput would have been a serious analytical error. The second reflects the durable effect the workload intended to produce. If your application maintains inventory, balances, quotas, or state transitions, the metric must agree with the actual business invariant, not simply the count of acknowledgments returned by the driver.

Atomic UPDATE: The Smallest Effective Fix for This Counter

The first correction for lost updates removed the application-side stale calculation. Instead of reading the value, computing an absolute replacement, and writing it back later, the transaction expressed the increment as a relative SQL operation. In READ COMMITTED, PostgreSQL can wait for a concurrent writer and apply the new operation against the updated row when the target remains eligible. PostgreSQL documents this statement-level behavior in its transaction isolation reference. The laboratory observed it directly rather than assuming it would always improve every workload.

-- Simplified atomic update used for the counter principle
UPDATE counters
SET value = value + 1
WHERE id = 1;

In the controlled two-session case, both atomic updates committed and the counter reached two. In the performance matrix, the atomic READ COMMITTED strategy preserved all 600 requested increments at one, four, and eight workers without retrying or reporting lost updates. Its primary-run median throughput was 7,184 completed operations per second with four workers and 6,574 with eight. In this specific hot-row experiment, it was also the fastest correct strategy among the tested variants at those two concurrency settings.

That is a narrow result, not an instruction to replace all business transactions with a single atomic expression. A relative UPDATE is well suited to an operation that can be expressed correctly within one database statement. Workflows that must inspect external conditions, enforce multi-row rules, call outside systems, or make decisions based on a previous read demand a broader design. The counter demonstration is useful because it isolates lost updates from those additional concerns.

SELECT FOR UPDATE: Correctness Through Explicit Serialization

The second correction against lost updates placed the read and write inside a transaction that explicitly locks the target row using SELECT ... FOR UPDATE. A second writer then waits until the first transaction releases the conflicting lock, reads the updated value, and performs its change. In the controlled experiment, the system independently confirmed this blocking with pg_blocking_pids, and the PostgreSQL server log recorded 32–36 millisecond waits after a deliberately imposed hold. This was observed blocking, not an invented description of a lock that might have been taken.

BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT value FROM counters WHERE id = 1 FOR UPDATE;
-- Calculate the next value while holding the row lock
UPDATE counters SET value = :next_value WHERE id = 1;
COMMIT;

The explicit-lock strategy avoided lost updates and completed all 600 operations per measured setting. Its median successful throughput was 6,375 operations per second with four workers and 5,817 with eight, below the atomic statement in this local test. At eight workers, its p95 processing-attempt duration was 4.816 milliseconds, compared with 2.118 milliseconds for atomic READ COMMITTED. The choice had not eliminated contention. It moved the contention into waiting rather than application-visible conflict rejection.

The performance cells in the lost updates comparison did not produce server-logged waits exceeding the 20-millisecond log threshold. Therefore I do not claim a measured lock-wait-duration distribution for those throughput comparisons; sub-threshold waits were not measured separately. The lock-wait proof came from the controlled schedules. PostgreSQL’s explicit locking documentation also explains why rows can block other writers and why inconsistent lock acquisition order can create deadlocks.

Optimistic Concurrency Control: Detection Is Not Completion

An optimistic concurrency control strategy tracks a version and allows an UPDATE only when the row still has the version originally read. This converts an overwrite into an observable conflict. But there are two distinct questions: did the system prevent lost updates, and did it finish the original logical request? An UPDATE affecting zero rows can answer the first question while failing the second.

-- Simplified version-predicate pattern
UPDATE counters
SET value = :next_value, version = version + 1
WHERE id = 1 AND version = :expected_version;
-- If zero rows changed: rollback and report a conflict,
-- or retry the complete logical transaction using fresh data.

In the forced two-session lost updates schedule, the stale optimistic writer affected zero rows and was rejected. The stored value stayed correct, but one intended increment remained unfinished. When the controlled case retried the complete operation, it reread the newer version and committed the second increment. That is why the article treats conflicts as explicit outcomes rather than quietly counting conflict detection as successful business processing.

The lost updates performance data reveal the same distinction at a larger scale. Without retry, optimistic version checking completed only 242 of 600 offered operations with four workers; 358 remained unfulfilled. At eight workers, only 140 completed and 460 remained unfulfilled. Neither case lost committed increments. The price of correctness was that a large portion of the offered work was not completed under this deliberately saturated hot-row condition. A system that merely logs those conflicts and moves on has preserved database integrity but not delivered the requested application outcome.

What Transaction Isolation Actually Changed

When analyzing lost updates, transaction isolation levels define which concurrent histories can be observed or committed, but their names are not promises that all work will succeed. I compared READ COMMITTED, REPEATABLE READ, and SERIALIZABLE using the same focused schedules. In PostgreSQL 18, REPEATABLE READ uses a stable transaction snapshot; SERIALIZABLE additionally detects histories that cannot be understood as a serial order. The official isolation semantics establish that foundation; the laboratory tested the outcome of those rules in a concrete implementation.

READ COMMITTED: statement behavior matters

READ COMMITTED permitted lost updates in the unsafe application-side read-modify-write pattern. It also allowed the relative atomic UPDATE and the explicit locking pattern to serialize access to the hot row correctly. Those two results are not contradictory. Isolation level and SQL shape operate together. A careless client can overwrite a valid newer value, whereas a relative statement can apply its arithmetic to the updated row. This was the first reason I avoided labeling an entire isolation level “safe” or “unsafe” without specifying what the application actually does.

REPEATABLE READ: a same-row conflict became an error

For the lost updates scenario under REPEATABLE READ, a transaction that attempted to change a row already modified by another overlapping transaction received SQLSTATE 40001. The unsafe absolute-write variant did not silently overwrite the earlier increment. However, with retry disabled, the conflicting logical operation was left incomplete. Even the relative atomic UPDATE and SELECT FOR UPDATE variants could receive a serialization failure in the overlapping schedule, because the transaction snapshot could not simply accept a later committed row version. The same SQL expression therefore behaved differently under stronger isolation.

SERIALIZABLE: protecting an entire business invariant

SERIALIZABLE has a broader purpose than preventing lost updates in a single counter: it prevents successful commits from producing a combination that cannot be explained by a serial execution. In the controlled row-update schedule, conflicting work failed with 40001 unless the application retried. In a separate write-skew case, SERIALIZABLE provided a protection that REPEATABLE READ did not. This is not evidence that SERIALIZABLE is universally faster or cheaper; it is evidence that certain cross-row rules require stronger coordination than a stable snapshot alone.

The table describes the deliberately forced two-session cases, not the completion rate of the larger performance matrix. That distinction is essential. A deterministic demonstration can show exactly how lost updates arise, while a highly concurrent workload reveals how often the same choices create retries, unfinished work, or latency under a particular pressure pattern.

Write Skew: When Correct Rows Produce an Incorrect System

To move beyond one counter, I tested a two-row business invariant: at least one of two on-call staff members must remain active. Two transactions independently saw two active rows. Each decided it was safe to deactivate a different person. At READ COMMITTED, both committed, leaving nobody on call. At REPEATABLE READ, both also committed, because each wrote a different row while relying on a common snapshot of the overall condition. This is write skew, not the same-row lost updates anomaly.

The write-skew result illustrates why preventing lost updates by simply checking row versions is not sufficient for every application. A version predicate on each individual row can prevent two writers from overwriting the same row while still allowing two different rows to be updated into an invalid combined state. Here the business rule belongs to the relationship between rows. In the SERIALIZABLE retry scenario, the retried request correctly declined its intended change after seeing the updated state. That is a successful preservation of the rule, not a second successful deactivation.

The general lesson from lost updates and write skew is to specify the invariant before selecting an isolation mechanism. For a single counter, atomic arithmetic may be adequate. For a row requiring a conditional decision, optimistic version checks or locking may be suitable. For a rule spanning several rows and reads, a serializable design or carefully engineered equivalent constraints may be needed. The exact choice depends on transaction scope, contention, data model, and how the application handles declined work.

Retries: Recovering Work and Paying for It

PostgreSQL explicitly advises applications to retry the entire transaction after serialization failures, including the earlier decisions that determined which SQL commands and values were used. Retrying only the final UPDATE with an old calculated value can recreate the original problem. A correct full-transaction retry obtains fresh data, reevaluates the business rule, and then attempts the new transaction. This is the practical difference between detecting lost updates and recovering from the concurrent condition that might otherwise have caused them.

In the lost updates laboratory, several variants used an eight-attempt budget and bounded backoff. With retries enabled, the measured configurations completed between 592 and 600 of 600 offered logical operations. The mean attempt count rose to 1.23–1.92 attempts per offered operation, depending on strategy and worker count. Those extra attempts are not free; they consume transaction round trips, scheduler time, and new opportunities for contention. The eight-worker p95 latency of a full logical operation reached approximately 8.8–12.1 milliseconds for several retry-heavy variants, even when the p95 duration of an individual attempt was only around 1–2 milliseconds.

Crucially, retries intended to recover from lost updates and related conflicts did not guarantee eventual completion. Across the primary run’s 195 performance cases, 34 logical operations exhausted the allowed retry budget; the second full execution recorded 30. This is a reason to design explicit terminal states and operational alerts rather than assuming a retry loop always fixes concurrent conflicts. The official serialization failure guidance likewise warns that many attempts may be necessary under intense contention.

Representative eight-worker outcomes

In a lost updates comparison, be careful when reading the “no retry” p95 column. It describes the measured latency of operations completed under that policy, not the latency that would have been needed to complete the hundreds of rejected operations. Comparing only fast successful requests can make a low-completion strategy appear attractive. The denominator matters: a completed request and an unfinished request must not collapse into the same performance metric.

Lost Updates, Throughput, and Why a Fast Commit Is Not Enough

When I compared lost updates and throughput, the right question was not “Which version executed the most SQL statements?” It was “How much requested logical work was actually completed, and how much of its intended effect persisted?” The distinction was stark. The unsafe four-worker case reported 5,345 committed operations per second but only 2,094 durable increments per second; at eight workers, 4,997 commits per second corresponded to 913 durable increments per second. The database acknowledged operations that the application failed to preserve.

The atomic READ COMMITTED variant led these particular lost updates comparisons because its operation could be expressed in a single relative SQL statement, and the workload targeted one counter. This does not rank all isolation strategies in general. A workload dominated by multi-row business rules, transactions that read several related records, longer processing, database network latency, or a distributed queue would have different constraints. I would make the decision using an integrity requirement first, then completion guarantees, then latency and throughput under realistic traffic.

The second full run repeated the central lost updates correctness outcomes almost exactly, but some throughput cells varied by roughly one fifth. Because the time windows were so short, the article does not interpret a difference of a few hundred operations per second as a universal advantage. The robust finding is the separation between committed work and durable effect, and between conflict detection and successful recovery. Those conclusions survive the repeated experiment.

Failure Handling Beyond Serialization Errors

A usable strategy against lost updates and other concurrency failures also needs to behave correctly when the happy path is interrupted. The integration cases exercised explicit rollback, duplicate delivery, deadlock, lock timeout, statement timeout, retry exhaustion, and fixture reset. The rollback test showed that an uncommitted change was visible inside its own session but not to an observer, and that no operation receipt survived rollback. The duplicate-delivery test reused the same logical operation identifier and correctly avoided a second increment. Those results matter because retries can otherwise introduce a duplicate side effect while attempting to repair an earlier failure.

The deadlock scenario produced SQLSTATE 40P01. The lock timeout produced 55P03, and the statement timeout produced 57014. In each case, the failed transaction entered an aborted state requiring rollback before the connection could be reused, with 25P02 identifying an attempted command in that state. The test intentionally observed these failures. They are not indicators of a failed laboratory run; they are confirmation that expected error paths were triggered and recovered in a controlled way. PostgreSQL’s SQLSTATE reference defines the error conditions.

Operational consequences of the failure-path tests

• Make logical operation IDs stable. Retrying a request should not silently duplicate a previously committed effect.
• Rollback before reuse. An aborted transaction cannot simply continue issuing ordinary SQL statements.
• Classify errors by code and context. A serialization conflict, lock timeout, and persistent constraint violation are not interchangeable.
• Bound retries and expose exhaustion. Rejection is sometimes necessary; silent disappearance is not an acceptable reporting policy.
• Recheck business conditions after retry. In the on-call scenario, the correct retried action was to decline a change, not to force a second successful update.

These principles for recovery beyond lost updates emerged from actual integration cases, not a general checklist pasted onto a benchmark. Still, one isolated laboratory cannot prove end-to-end exactly-once behavior through external payment systems, message brokers, or network acknowledgments. No crash recovery, unknown commit result, replica failover, or external side-effect recovery was measured. Those would require their own experiments and distinct idempotency contracts.

What I Verified, and What This Study Cannot Prove

To keep the lost updates findings reproducible, I kept the original experiment definition unchanged for the two successful runs. The evidence recorded both execution manifests, individual logical operation and attempt transitions, database snapshots, server errors, timing boundaries, and the final state of the private PostgreSQL cluster. The supplied verifier recalculated 253 case outcomes in each run and matched them to the stored results. A separate analysis implementation checked 1,621 conditions per run, including receipt accounting, committed logical IDs, value consistency, percentile recomputation, and whether the server and client agreed on SQLSTATE totals.

The primary run’s client and server logs both recorded 5,228 serialization-failure errors with SQLSTATE 40001. That figure includes controlled, write-skew, and performance contexts; it must not be presented as 5,228 distinct business requests or as a 5,228-operation failure rate. SQL errors, transaction attempts, and logical operation IDs have different denominators. The independent check also reproduced the counts in the second full execution, preserving the first failed startup as an explicit environmental result rather than hiding it.

Evidence boundaries and unanswered questions

• One local machine. Apple M4 Pro with PostgreSQL 18.6 is not a cross-platform or cross-engine benchmark.
• A single hot row. The performance test concentrated contention by design; ordinary workloads often distribute updates across many records.
• Very short runs. Each performance cell measured roughly 17–52 milliseconds with 120 operations and no think time. Long-duration sustainability was not evaluated.
• Client scheduling overhead. Python threads and the ctypes-to-libpq boundary are part of the measured latency; server-only execution time was not isolated.
• Incomplete lock-wait telemetry. The throughput runs logged no individual wait above the 20 ms logging threshold, so their separate lock-wait durations remain unmeasured.
• No system-resource profile. CPU, energy, memory, and disk I/O were not collected.
• No production topology. Networking, replicas, pooling, multi-service coordination, crash recovery, and external side effects were not included.
• No statistical generalization. Five repetitions per cell and two full executions establish observed reproducibility for these cases, not an industry-wide effect size.

It would also be misleading to conclude that READ COMMITTED always loses data, that SERIALIZABLE always resolves business requests, or that optimistic concurrency control is inherently too slow. The study establishes more precise claims: application-side absolute writes can produce lost updates in a real READ COMMITTED schedule; atomic statements and explicit locks avoided them in the tested single-row workload; and retry behavior changed the cost and completion rate of conflict-based strategies. The actual data support those statements without needing universal performance claims.

Decision Brief: Choosing a Concurrency Strategy That Matches the Invariant

My practical rule is to start from the invariant and count the actual completed business operation. If a relative SQL update safely expresses the whole operation, I would favor its simplicity over adding an unnecessary client-side read. When a decision depends on fresh state, I would choose an explicit conflict-handling policy and a transaction boundary that encloses the whole decision. When several rows must remain mutually consistent, I would test the cross-row condition rather than infer safety from a single-row test. Each decision must account for lost updates, rejected requests, retries, and service-level objectives together.

How to Reproduce the Reasoning Without Copying the Lab

An independent engineer investigating lost updates can reproduce the core reasoning without adopting this exact harness or hardware. Start with a private database and an invariant such as “N distinct accepted increment requests must increase the value by N.” Force two sessions to read the same initial value, then allow them to write in a controlled order. Record each logical operation ID, each transaction attempt, the commit result, the final counter value, and any database error code. Next compare a stale absolute replacement with a relative atomic update, a row lock, and a version predicate.

The critical step is to verify the persisted state after all transactions finish. Do not stop at the final HTTP response, a client-side success message, or the database commit count. Check whether the business invariant actually holds. When an operation fails with 40001, rerun the complete transaction and its decision logic with a fresh view. Then measure accepted operations, completed operations, rejected operations, retries, p95 end-to-end latency, and any terminal exhaustion. That is a meaningful evaluation of lost updates and recovery, even when conducted on a much smaller test matrix.

Beyond lost updates, repeat the design for a two-row invariant where each transaction modifies a different row after reading a shared condition. This distinguishes same-row write protection from serializable business rules. Freeze the conditions before the run, preserve unfavorable results, and describe exactly which isolation level, SQL shape, driver, and workload were used. A reproducible explanation is more valuable than a headline throughput number that cannot survive a closer look at the stored data.

Conclusion: Correctness Before a Faster Number

I began this study asking how lost updates emerge under concurrent production-style logic. The executed PostgreSQL laboratory answered that question with a concrete, repeatable failure: two commits, two receipts, and only one preserved increment. It also showed that the engineering response is not simply “turn up isolation.” Atomic SQL, explicit row locks, optimistic version checks, REPEATABLE READ, SERIALIZABLE, and bounded full-transaction retries produced distinct mixes of correctness, rejected work, waiting, and latency.

The most useful conclusion about lost updates is operational rather than promotional. Count the requested logical work, count what was actually completed, inspect the persisted business state, and then examine the cost of the mechanism that maintained it. In this experiment, the apparently successful unsafe implementation lost up to 81.5% of its committed increments in the eight-worker hot-row case. Some protective approaches prevented every lost increment yet left hundreds of requests unfinished without retries. A reliable system must make both outcomes visible. A database can protect its own consistency while the application still fails to fulfill a request; the architecture needs to handle both.

Author’s Note and Further Reading

This article documents one controlled lost updates laboratory experiment executed on November 11, 2026, and its separate full replication. The first server startup failure, contradictory draft wording about a version field, and genuine retry exhaustion remain part of the study’s interpretation. The examples are intentionally synthetic and the throughput measurements are not a published PostgreSQL capacity benchmark. My goal is to make the distinction between commit acknowledgment, persisted state, and business completion understandable enough to test in a real engineering workflow.

For engine semantics and implementation-specific behavior, consult the PostgreSQL 18 documentation on transaction isolation, explicit row locking, serialization failures and transaction retries, and SQLSTATE error codes. Explore the Software Development articles for related engineering topics and the full Articles hub for cross-disciplinary studies.