Lost update problem

A lost update happens when two transactions read the same value, each computes a new value from it, and both write back. The second write replaces the first, so one change disappears without an error. It hides in ordinary code: a wallet balance, a stock counter, a like count, a ticket open in two browser tabs. The fix is to make the check and the write happen in one place, at one moment.

Picture it first: one stock board, two shop assistants

A kirana shop keeps its stock on a whiteboard: “Parle-G: 40”. Ravi sells 3 packets, glances at the board and walks off doing the sum in his head. Meanwhile Meera sells 5, reads 40, wipes it and writes 35. Ravi comes back, wipes 35 and writes 37.

The board says 37; the shelf holds 32. Neither assistant got the arithmetic wrong; Meera’s sale was erased by a write that never looked at it.

There are three honest ways to run the board:

• Write the change, not the total. Each assistant writes “−3” or “−5” and the board keeps the sum.
• Hold the marker. Whoever reads the board keeps the only marker until they have written.
• Check before you wipe. Ravi checks the board still says 40 before writing 37. It says 35, so he redoes the sum.

Map it back: the board is a row, glancing at it is a plain SELECT, the sum in Ravi’s head is application code, and wiping and writing is an UPDATE with a literal value. The three fixes are an atomic update, a row lock and a compare-and-set.

The race, precisely

Berenson and co-authors named the anomaly in their 1995 critique of the SQL isolation levels:

P4: r1[x] … w2[x] … w1[x] … c1

T1 reads x, T2 writes x, then T1 writes x based on its earlier read and commits. Their example history, r1[x=100] r2[x=100] w2[x=120] c2 w1[x=130] c1, ends at 130: T1’s +30 survives and T2’s +20 is gone, even though T2 committed first.

Three things make it nasty:

• Nothing dirty is read. Every value seen was committed, so defences against dirty reads don’t help.
• Nothing fails. Both transactions commit; the only symptom is a wrong number.
• It needs only one row. Unlike write skew, both writes hit the same item, so a database can detect it. Whether it does depends on the engine.

A narrower form, P4C (rc1[x] … w2[x] … w1[x] … c1), reads through a cursor. Cursor Stability, a level between READ COMMITTED and REPEATABLE READ, prevents it by locking the cursor’s current row until the cursor moves. Few apps update through cursors today; general P4 is the one that bites.

Read the figure as a window: B’s read is correct at t2 and goes stale at t4, when A commits. Anything committed between a session’s read and its write is overwritten, so a longer gap (a slow API call, a retry, a human at a form) means more losses.

Where it shows up

Flash-sale stock. One phone left: stock = 1. Buyers X and Y both read 1, both pass the app’s if stock >= 1 check, both write stock = 0 and both get a confirmation. With many buyers on one SKU in the same second, as in a flash sale, overlapping windows are close to certain.

Like and view counters. Ten likes arrive together; all ten requests read 41 and write 42. The counter shows 42 instead of 51, so nine likes are lost (10 − 1 = 9) and nobody notices.

Two people editing one form. Priya opens support ticket #8812 at 10:00, Arjun at 10:01. Priya raises the priority from P3 to P1 and saves at 10:02. Arjun fixes a typo and saves at 10:05; his form submits every field, including the P3 it loaded, so the priority quietly reverts. No transaction spans those five minutes, and none should while a person types, so no isolation level can help; the protection has to travel with the request.

Why a transaction doesn’t save you

Here is the bug as most people first write it:

# PostgreSQL or MySQL at its default isolation level
with conn.transaction():
    balance = conn.execute(
        "SELECT balance FROM wallets WHERE id = %s", [wallet_id]).fetchone()[0]
    new_balance = balance + amount                 # computed in the app
    conn.execute("UPDATE wallets SET balance = %s WHERE id = %s",
                 [new_balance, wallet_id])

The transaction makes both statements commit together; it doesn’t make anyone wait between them. A plain SELECT takes no lock under MVCC, so a second session reads the same ₹500. Now compare:

UPDATE wallets SET balance = balance + 200 WHERE id = 42;

PostgreSQL’s docs spell out the READ COMMITTED rule: if a concurrent transaction has already updated the target row, the second UPDATE waits for it to finish, re-evaluates its WHERE clause against the updated row, and applies its change to that version. So balance + 200 starts from ₹700 and gives ₹900. InnoDB gets there another way: UPDATE always reads the newest committed row, never the snapshot.

The app-computed version goes through the same machinery and still loses: it is applied to the new row too, but it writes the literal 700 it was given. The database can only protect arithmetic it can see.

What each database does with the race

The isolation levels hub has the full anomaly matrix; this table answers one question about the naïve code.

PostgreSQL’s REPEATABLE READ is snapshot isolation with the paper’s first-committer-wins rule, checked when the second writer reaches the row (after waiting, if the first writer is still running): if a transaction that committed after your snapshot changed it, you are rolled back and should retry the whole transaction.

MySQL’s InnoDB uses the REPEATABLE READ snapshot for plain SELECTs only; the manual says it “applies to SELECT statements … not necessarily to DML statements”. The UPDATE writes onto the newest row without asking whether it changed since your read, and Hermitage’s P4 test records the lost update. MariaDB, a MySQL fork, added that check as innodb_snapshot_isolation, on by default since 11.6.2. The MySQL 8.4 and 9.5 manuals describe no equivalent, so on MySQL treat REPEATABLE READ as no protection.

SQL Server’s REPEATABLE READ holds shared locks to commit, so you trade lost updates for deadlock retries. Oracle’s SERIALIZABLE raises ORA-08177, whose documented action is to retry.

The menu of fixes

Six fixes, simplest first. The first three cover most cases; Fig. 2 replays the wallet race under them.

Fix 1: let the database do the arithmetic

Express the change as a delta, add a guard for any invariant, and check how many rows matched.

-- Wallet credit: no read needed
UPDATE wallets SET balance = balance + 200 WHERE id = 42;

-- Flash-sale decrement with a floor (PostgreSQL)
UPDATE inventory
SET    stock = stock - 1
WHERE  sku = 'PHONE-42' AND stock >= 1
RETURNING stock;          -- no row back: sold out

In PostgreSQL a non-matching update is not an error; the command tag says UPDATE 0, and your code must turn that into “sold out”. RETURNING hands back the new value in the same round trip (since PostgreSQL 18 it can return OLD values too). MySQL has no RETURNING: check the affected-row count, then re-read the row in the same transaction, where your own write is visible and the row stays locked.

What you gain: one round trip, no retries, and correct results at READ COMMITTED on every engine above. What you pay: the rule must fit in SQL, the guard protects only what you wrote, and every writer of a hot row queues on its lock (see contention and double booking).

Fix 2: lock the row before you read it

When the decision needs application logic (KYC limits, fraud rules), take an exclusive row lock with the read.

BEGIN;
SET LOCAL lock_timeout = '2s';               -- PostgreSQL: don't wait forever
SELECT balance, kyc_tier FROM wallets WHERE id = 42 FOR UPDATE;
-- app: check the tier's limit, compute the new balance
UPDATE wallets SET balance = 900 WHERE id = 42;
COMMIT;

B’s FOR UPDATE blocks until A commits. At READ COMMITTED, PostgreSQL then locks and returns the updated row, and InnoDB’s locking reads always see the newest data, so B starts from ₹700. (At PostgreSQL’s REPEATABLE READ, B gets an error instead.) SQL Server spells the lock WITH (UPDLOCK).

Add NOWAIT to fail at once instead of queueing (PostgreSQL raises 55P03, MySQL ERROR 3572), or set lock_timeout, whose default of 0 means wait forever. Don’t use FOR SHARE here: MySQL’s manual warns that two shared-lock readers of a counter deadlock when both try to write it.

What you gain: any logic between read and write, a fresh value, and no retries in the common case. What you pay: writers queue behind the slowest transaction, inconsistent lock order deadlocks, and the lock can never span user think time. Deadlock ordering and SKIP LOCKED belong to optimistic vs pessimistic locking.

Fix 3: compare-and-set on a version column

Read freely, then write only if the row is still the version you read.

SELECT balance, version FROM wallets WHERE id = 42;        -- 500, 7

UPDATE wallets
SET    balance = 700, version = version + 1
WHERE  id = 42 AND version = 7;
-- 1 row: you won.  0 rows: someone wrote first, so re-read and retry.

for attempt in range(3):
    balance, version = read_wallet(wallet_id)
    if cas_update(wallet_id, balance + 200, expected_version=version) == 1:
        break
    time.sleep(0.005 * 2 ** attempt)      # back off before re-reading
else:
    raise ConflictError("wallet is busy, please retry")

The WHERE clause is checked against the newest row, so the check and the write are one atomic step even at READ COMMITTED. Prefer an integer to a timestamp: two writes in one clock tick look identical, and RFC 9110 notes HTTP dates have only one-second resolution.

What you gain: no locks between read and write, explicit conflicts, and a token that can travel in a form, an ETag or a DynamoDB attribute. What you pay: retries and wasted work that grow with contention, and every write path must bump the version, or one forgotten admin script defeats it.

Fix 4: let the engine detect it, and retry

On PostgreSQL you can keep the naïve code, run it at REPEATABLE READ or SERIALIZABLE, and retry the whole transaction on SQLSTATE 40001.

for attempt in range(5):
    try:
        db.execute("BEGIN ISOLATION LEVEL REPEATABLE READ")
        balance = db.scalar("SELECT balance FROM wallets WHERE id = %s", [wallet_id])
        db.execute("UPDATE wallets SET balance = %s WHERE id = %s",
                   [balance + 200, wallet_id])
        db.execute("COMMIT")
        break
    except SerializationFailure:          # SQLSTATE 40001
        db.execute("ROLLBACK")
        time.sleep(0.01 * 2 ** attempt)    # then rerun everything

What you gain: no schema change, and every row the transaction writes is covered. What you pay: retry plumbing, side effects kept outside the retried block, wasted work under contention, and portability, since the same code on MySQL’s default level loses updates silently. It can’t cover a form edit either. Serializable isolation covers what the stronger level adds.

Fix 5: across HTTP requests, ETag and If-Match

For the two-agents-one-ticket case, the server sends the version as an entity tag and the client returns it in If-Match when saving.

GET /tickets/8812
→ 200 OK
  ETag: "v7"

PUT /tickets/8812
If-Match: "v7"
Content-Type: application/json
→ 412 Precondition Failed        (someone saved v8 in the meantime)

RFC 9110 says If-Match exists to prevent “the ‘lost update’ problem”. The server must use the strong comparison (weak W/ tags never match), must not perform the method when the condition is false, and may answer 412 Precondition Failed. To stop clients skipping the header, answer 428 Precondition Required (RFC 6585). Behind the API it is Fix 3: … WHERE id = 8812 AND version = 7, with 0 rows mapped to 412. Server-rendered apps use a hidden field; Rails’ optimistic locking docs recommend putting lock_version in the form.

What you gain: correctness across minutes of think time with zero locks held, in standard HTTP. What you pay: every client must send the header, and you need a conflict screen that shows what changed and lets the user re-apply.

Fix 6: outside SQL, the same three shapes

Redis. INCRBY wallet:42 200 is atomic with no read. For check-then-write, WATCH gives optimistic locking: if a watched key changes before EXEC, the transaction is discarded and EXEC returns null, so you loop. The docs call a Lua script usually simpler and faster:

-- KEYS[1] = stock:PHONE-42, ARGV[1] = quantity
local stock = tonumber(redis.call('GET', KEYS[1]) or '0')
if stock < tonumber(ARGV[1]) then return -1 end
return redis.call('DECRBY', KEYS[1], ARGV[1])

Redis 8.4 added SET key value IFEQ old, a one-command compare-and-set for strings.

DynamoDB. UpdateItem with ADD views :one is an atomic counter, but AWS warns it is not idempotent, so a retried timeout can count twice; for money, use a conditional update. A ConditionExpression is checked against the latest version of the item, and a false one fails with ConditionalCheckFailedException:

aws dynamodb update-item --table-name Inventory \
  --key '{"sku": {"S": "PHONE-42"}}' \
  --update-expression "SET stock = stock - :one" \
  --condition-expression "stock >= :one" \
  --expression-attribute-values '{":one": {"N": "1"}}'

For document edits, condition on a version attribute instead.

MongoDB. $inc is atomic within one document, and a guard in the filter makes the update conditional:

db.inventory.updateOne(
  { _id: "PHONE-42", stock: { $gte: 1 } },
  { $inc: { stock: -1 } }
)  // matchedCount 0 → sold out
db.tickets.findOneAndUpdate(
  { _id: 8812, version: 7 },
  { $set: { priority: "P1" }, $inc: { version: 1 } },
  { returnDocument: "after" }
)  // null → someone else saved first

Choosing between them

ORM pitfalls

ORMs make read-modify-write the path of least resistance: load an object, change a field, save it.

Django. The docs’ own race example is reporter.stories_filed += 1; reporter.save(): two threads load the old value and the second save discards the first increment. F("stories_filed") + 1 makes Django emit SET stories_filed = stories_filed + 1, and .filter(pk=pk).update(...) with the same F() does it in one query. Two catches: the F() assignment is applied again on the next save() until you call refresh_from_db(), and a plain save() writes every column, so two requests editing different fields still clobber each other unless you pass update_fields. For pessimistic locking, use select_for_update() inside transaction.atomic().

Rails. An integer lock_version column switches on optimistic locking; a stale save raises ActiveRecord::StaleObjectError.

Hibernate and JPA. A @Version field is checked and incremented on each update, and a conflict throws an exception your code must handle.

An entity cached in Redis or a session, edited and written back later, is the same bug without an ORM.

Merge or reject?

Counters merge. Increments commute: +200 then +200 gives ₹900 in either order, so send deltas, not totals (atomic update, INCRBY, ADD, $inc). A delta isn’t idempotent, though: a retried timeout credits twice, so pair deltas with an idempotency key.

Documents reject. Two edits to one paragraph don’t commute. Reject the second with 412 and show a diff; auto-merge only edits to different fields. Real-time co-editing goes further, as in Google Docs-style editors.

Across replicas. When several replicas accept writes independently, a CRDT counter keeps per-replica tallies and sums them on merge, so no increment is lost, while last-write-wins loses them by design; see clocks, ordering and conflict resolution.

Going deeper: finding lost updates in production

Lost updates log no errors, so you find them by their fingerprints:

• Balances off by exactly one transaction amount, say ₹200, rather than a random figure.
• Oversold inventory: more confirmed orders than units, while the stock column reads 0, not −1.
• Counters below the event log: the like column says 42, the likes table has 51 rows.
• “I changed it and it changed back”, usually reported by the first editor.

Reconcile. A job that recomputes derived values from their source of truth (balances from ledger entries, counters from COUNT(*)) and alerts on any difference turns silent drift into a page.

Design it out. In an append-only ledger, each credit is an INSERT of a +₹200 entry and the balance is their sum, or a cached column kept with an atomic update in the same transaction. Inserts don’t overwrite each other, so there is no shared value to lose, one reason money moves through a double-entry ledger.

Test with concurrency. Fire N parallel increments at one row and assert the value rose by exactly N, or replay the two-session script by hand in two psql windows.

Two related traps: reading from a replica or cache widens the stale window by the lag, and a distributed lock whose lease expires mid-write lets a stale value through unless storage checks a fencing token, a version check by another name.

Cheat sheet

How to say it in the interview

“A lost update is a read-modify-write race: two requests read a row, compute in the app and write back, and the later write erases the earlier one, so two ₹200 top-ups on ₹500 end at ₹700. A transaction alone doesn’t stop it at read committed, nor does MySQL’s repeatable read, so I fix the write. For balances and stock the database does the arithmetic, UPDATE … SET stock = stock - 1 WHERE stock >= 1, and I check rows affected. If the decision needs app logic, I lock the row with FOR UPDATE in a short transaction. For edits across a user’s think time, I use a version column with ETag and If-Match, so a stale save gets a 412 and a diff.”

Does wrapping it in a transaction fix it? Not by itself. At READ COMMITTED, the default in PostgreSQL, Oracle and SQL Server, a plain SELECT takes no lock, so both transactions read ₹500 and both commit. At REPEATABLE READ it depends: PostgreSQL aborts the second writer with 40001, SQL Server deadlocks one, MySQL lets it through. A transaction gives all-or-nothing; the atomic update, lock or version check gives correctness.

FOR UPDATE or a version column? FOR UPDATE when the transaction is short and conflicts are frequent, so waiting beats redoing work. A version column when conflicts are rare, when read and write sit in different requests, or when you can’t hold a lock at all. On one very hot row neither scales and you reshape the data; see optimistic vs pessimistic locking.

How do you stop two people overwriting each other’s edits in a web form? Send the version with the page as an ETag or hidden field, and make the save conditional: If-Match on the PUT, WHERE id = ? AND version = ? on the server. A mismatch returns 412 and the UI shows the diff so the user can re-apply; 428 rejects clients that omit If-Match.

How do you do it in DynamoDB or Redis? Counters use atomic increments: ADD in DynamoDB, INCRBY in Redis. Guarded changes use a DynamoDB ConditionExpression such as stock >= :one or version = :v, which fails with ConditionalCheckFailedException, or a Redis Lua script; WATCH with MULTI/EXEC also works and returns null on conflict. Increments aren’t idempotent, so deduplicate retries.

Why does PostgreSQL catch it at REPEATABLE READ but MySQL doesn’t? PostgreSQL’s REPEATABLE READ is snapshot isolation with a first-committer-wins check: if a row it is about to change was changed by a transaction that committed after its snapshot, it aborts. InnoDB uses the snapshot only for plain SELECTs; its UPDATE writes onto the newest row without asking whether it changed. MariaDB added that check in 11.6.2; MySQL 8.4 and 9.5 document none.

Where to go next

• Transaction isolation levels: the full anomaly catalogue, level by level.
• Optimistic vs pessimistic locking: contention maths, deadlocks and SKIP LOCKED.
• Read committed vs repeatable read: why one UPDATE re-checks and the other aborts.
• Serializable isolation: write skew, which a one-row version check cannot catch.
• Contention and double booking: hot rows and flash-sale inventory at scale.
• Design a double-entry ledger: an append-only design with nothing to overwrite.
• Redis and Cassandra and DynamoDB: atomic counters and conditional writes in practice.

References and further reading

1. A Critique of ANSI SQL Isolation Levels (Berenson, Bernstein, Gray, Melton, O’Neil and O’Neil, SIGMOD 1995)microsoft.comDefines P4 (lost update) and P4C (cursor lost update), places Cursor Stability between read committed and repeatable read, and shows first-committer-wins preventing P4.
2. PostgreSQL documentation: Transaction Isolationpostgresql.orgHow a read committed UPDATE waits and re-evaluates against the new row version, and the repeatable read error “could not serialize access due to concurrent update”.
3. PostgreSQL documentation: UPDATEpostgresql.orgRETURNING (with OLD and NEW values since PostgreSQL 18) and the UPDATE count command tag, where zero rows is not an error.
4. MySQL 8.4 Reference Manual: Consistent Nonlocking Readsdev.mysql.comRepeatable read snapshots apply to plain SELECTs, not to UPDATE and DELETE, which act on the newest committed rows.
5. MySQL 8.4 Reference Manual: Locking Readsdev.mysql.comFOR UPDATE versus FOR SHARE for read-then-increment (FOR SHARE deadlocks), plus NOWAIT and SKIP LOCKED.
6. Hermitage: testing transaction isolation levels (Kleppmann)github.comHand-run P4 tests per database: PostgreSQL repeatable read aborts, MySQL repeatable read allows it, SQL Server repeatable read deadlocks, Oracle serializable aborts.
7. RFC 9110: HTTP Semantics (ETag, If-Match, 412)rfc-editor.orgIf-Match with strong ETag comparison exists to prevent the lost update problem; a false precondition must not run the method and may return 412.
8. Redis documentation: Transactionsredis.ioWATCH-based check-and-set with a nil EXEC on conflict, scripts as the simpler alternative, and SET IFEQ compare-and-set from Redis 8.4.
9. Amazon DynamoDB Developer Guide: Working with items (atomic counters, conditional writes)docs.aws.amazon.comAtomic counters are not idempotent; conditional writes are checked against the most recent version of the item.
10. Django documentation: Query expressions, F() and race conditionsdocs.djangoproject.comWhy `count += 1; save()` races between two threads, and how F() moves the arithmetic into the SQL UPDATE.