The lost update problem

Transactions, isolation and locking Mid-level Python 3.13+ PostgreSQL 17+ Reviewed By Best Latest Apps

Fill the blank

Make the increment safe without explicit locks.

SQL · PostgreSQL 17
CREATE TABLE counters (id int PRIMARY KEY, n int NOT NULL);
INSERT INTO counters VALUES (1, 10);
UPDATE counters
SET n = ____
WHERE id = 1;
SELECT n FROM counters;

Options
Hint 1 of 1

Let the database read and write in one step.

Choose an answer, then open Show answer below to check it.

Two sessions both read n = 10 and each writes 11. Under READ COMMITTED both commit and one increment is lost; under REPEATABLE READ and SERIALIZABLE, B's update fails with 40001 and must be retried.

Run on PostgreSQL 17.11 (two sessions)

Text version

READ COMMITTED:

  1. A: SELECT n FROM counters WHERE id = 1 (10)
  2. B: SELECT n FROM counters WHERE id = 1 (10)
  3. A: UPDATE counters SET n = 11 WHERE id = 1 (app computed 11)
  4. A: COMMIT (ok)
  5. B: UPDATE counters SET n = 11 WHERE id = 1 (overwrites A, then COMMIT ok)

REPEATABLE READ:

  1. A: SELECT n FROM counters WHERE id = 1 (10)
  2. B: SELECT n FROM counters WHERE id = 1 (10)
  3. A: UPDATE counters SET n = 11 WHERE id = 1 (app computed 11)
  4. A: COMMIT (ok)
  5. B: UPDATE counters SET n = 11 WHERE id = 1 (ERROR 40001, retry)

SERIALIZABLE:

  1. A: SELECT n FROM counters WHERE id = 1 (10)
  2. B: SELECT n FROM counters WHERE id = 1 (10)
  3. A: UPDATE counters SET n = 11 WHERE id = 1 (app computed 11)
  4. A: COMMIT (ok)
  5. B: UPDATE counters SET n = 11 WHERE id = 1 (ERROR 40001, retry)
Across levels
LevelResultn
READ COMMITTED✕ Happens11
REPEATABLE READ↻ 40001, retry11
SERIALIZABLE↻ 40001, retry11
Show answer

Answer: A n + 1

Correct. The database computes it from the newest committed value, so concurrent increments don't overwrite each other.

SET n = n + 1 is evaluated by the database against the latest committed row, under the row lock the UPDATE takes. A second concurrent increment waits and then adds to 11, not to the stale 10.

Why not the others?
  • B 11. A value computed in the app is exactly the lost-update bug.
  • C (SELECT n FROM counters WHERE id = 1) + 1. A subquery reads a snapshot and can lose updates again under concurrency.
  • D DEFAULT. Resets the column; it doesn't increment it.

30-second answer

Both requests read the same old value, say 10, and both write 11, so one increment disappears. Under PostgreSQL's default READ COMMITTED nothing stops it, because each write looks valid on its own. Fix it by letting the database do the arithmetic in one statement (SET n = n + 1), by locking the row with SELECT ... FOR UPDATE, or with optimistic concurrency: a version column checked in the UPDATE's WHERE clause.

Practise saying it · 40 s. No recording, just a timer

Explain lost updates and three fixes.

Say your answer out loud, then check which of these points you covered:

  • Two reads of the old value
  • Atomic UPDATE n = n + 1
  • SELECT FOR UPDATE
  • Version column, check row count
  • RR turns it into 40001

Deep dive

An atomic UPDATE ... SET n = n + 1 re-reads the newest committed row version, so concurrent increments queue up instead of overwriting.

SELECT ... FOR UPDATE makes the second reader wait until the first commits, then read the new value.

Optimistic: UPDATE ... SET n = $new, version = version + 1 WHERE id = $id AND version = $seen; zero rows updated means someone else won, so reload and retry.

Under REPEATABLE READ, PostgreSQL refuses the second write with 40001 instead of losing it.

Optimistic concurrency with a version column Ran on PostgreSQL 17.11
SQL · PostgreSQL 17
CREATE TABLE docs (
  id int PRIMARY KEY,
  body text,
  version int NOT NULL DEFAULT 1
);
INSERT INTO docs VALUES (1, 'v1 text', 1);
-- Writer A read version 1 and saves first: 1 row updated
UPDATE docs SET body = 'A edit', version = version + 1
WHERE id = 1 AND version = 1;
-- B also read version 1: 0 rows updated, so B reloads and retries
UPDATE docs SET body = 'B edit', version = version + 1
WHERE id = 1 AND version = 1;
SELECT body, version FROM docs;

Common mistakes

  • Doing read-modify-write in an ORM object and saving the whole row.
  • Adding a mutex in one app instance when several instances run.
  • Ignoring the row count returned by an optimistic UPDATE.

Senior take & follow-ups

Show the race as a timeline, then pick the fix by contention: atomic SQL first, optimistic for low contention, locks for high.

Follow-up questions

Think of your answer first, then open the hint.

  1. Which fix scales best for a hot counter?

    Show hint

    None are great for one very hot row; shard the counter into N rows and sum them.

  2. What does the ORM 'version' feature do?

    Show hint

    It adds the optimistic version check to every UPDATE.

Related questions

Suggest a fix if something here is wrong or out of date.