The lost update in a database

Level: 201 · anyone who keeps a total in a database and reads it before writing it

One line: A SELECT followed by an UPDATE loses an update exactly as a load followed by a store does; UPDATE … SET total = total + ? keeps both numbers, and so does locking the row before reading it — but a transaction around the read and the write is not enough by itself: SQLite refuses the second writer, and PostgreSQL at its default isolation level lets it overwrite the first.

Keep the total in a database and the first lesson's three steps come back as SQL: a SELECT loads, the application adds, an UPDATE stores. Each statement is atomic, and — as with the atomic load and store — that says nothing about the pair.

The program below opens two connections, A and B, to one SQLite file, and runs their statements in the order that loses an update. It needs no threads: the database is the shared state, and each connection is a client of it. A adds 5 and B adds 10, so every scenario should end at 15.

Verified output of database_sqlite_py.py — regenerated by tools/run_examples.py, never hand-typed.

1. Read the total, add in Python, write the sum back
  A: SELECT total FROM counter WHERE id = 1  -> 0
  B: SELECT total FROM counter WHERE id = 1  -> 0
  A: UPDATE counter SET total = 5 WHERE id = 1
  B: UPDATE counter SET total = 10 WHERE id = 1
  the total is 10, not 15

2. Let the database do the addition, in one statement
  A: UPDATE counter SET total = total + 5 WHERE id = 1
  B: UPDATE counter SET total = total + 10 WHERE id = 1
  the total is 15

3. Read and write inside a transaction
  A: BEGIN
  A: SELECT total FROM counter WHERE id = 1  -> 0
  B: BEGIN
  B: SELECT total FROM counter WHERE id = 1  -> 0
  A: UPDATE counter SET total = 5 WHERE id = 1
  B: UPDATE counter SET total = 10 WHERE id = 1  -> refused: database is locked (SQLITE_BUSY)
  B: ROLLBACK
  A: COMMIT
  B starts again, from the beginning:
  B: BEGIN
  B: SELECT total FROM counter WHERE id = 1  -> 5
  B: UPDATE counter SET total = 15 WHERE id = 1
  B: COMMIT
  the total is 15

4. Take the write lock before reading: BEGIN IMMEDIATE
  A: BEGIN IMMEDIATE
  A: SELECT total FROM counter WHERE id = 1  -> 0
  B: BEGIN IMMEDIATE  -> refused: database is locked (SQLITE_BUSY)
  A: UPDATE counter SET total = 5 WHERE id = 1
  A: COMMIT
  B: BEGIN IMMEDIATE
  B: SELECT total FROM counter WHERE id = 1  -> 5
  B: UPDATE counter SET total = 15 WHERE id = 1
  B: COMMIT
  the total is 15

5. Write only if nobody has written since the read: a version column
  A: SELECT total, version FROM counter WHERE id = 1  -> 0, 0
  B: SELECT total, version FROM counter WHERE id = 1  -> 0, 0
  A: UPDATE counter SET total = 5, version = 1 WHERE id = 1 AND version = 0  -> 1 row(s) changed
  B: UPDATE counter SET total = 10, version = 1 WHERE id = 1 AND version = 0  -> 0 row(s) changed
  B changed nothing, so B reads again and retries:
  B: SELECT total, version FROM counter WHERE id = 1  -> 5, 1
  B: UPDATE counter SET total = 15, version = 2 WHERE id = 1 AND version = 1  -> 1 row(s) changed
  the total is 15

database_sqlite_py.py

database_sqlite_py.py in full — pasted here by tools/run_examples.py from the file CI runs.

"""The lost update in a database. Two connections, A and B, to one SQLite file,
each adding to the same row: A adds 5 and B adds 10, so the total should end at
15. No threads are needed to lose one of them. The program runs the statements
of the two connections in the interleaving that loses an update -- both read
before either writes -- and prints each statement as it runs it.

`timeout=0` makes a connection that finds the database locked fail at once
instead of waiting, and `autocommit=True` (Python 3.12 and later) means each
statement is its own transaction unless the connection says BEGIN.

    python3 database_sqlite_py.py
"""

import os
import sqlite3
import tempfile

workdir = tempfile.TemporaryDirectory()
path = os.path.join(workdir.name, "counter.db")

setup = sqlite3.connect(path, autocommit=True)
setup.execute(
    "CREATE TABLE counter (id INTEGER PRIMARY KEY, total INTEGER NOT NULL, version INTEGER NOT NULL)"
)
setup.execute("INSERT INTO counter VALUES (1, 0, 0)")
setup.close()

a = sqlite3.connect(path, timeout=0, autocommit=True)
b = sqlite3.connect(path, timeout=0, autocommit=True)
name = {a: "A", b: "B"}


def run(conn: sqlite3.Connection, sql: str, *params: int):
    """Run one statement and print it with its parameters filled in.

    Returns the first row of a query, the number of rows an UPDATE changed, or
    "refused" when SQLite refuses the statement.
    """
    shown = sql
    for p in params:
        shown = shown.replace("?", str(p), 1)
    try:
        cursor = conn.execute(sql, params)
    except sqlite3.OperationalError as e:
        print(f"  {name[conn]}: {shown}  -> refused: {e} ({e.sqlite_errorname})")
        return "refused"
    if sql.startswith("SELECT"):
        row = cursor.fetchone()
        print(f"  {name[conn]}: {shown}  -> {', '.join(map(str, row))}")
        return row if len(row) > 1 else row[0]
    if sql.startswith("UPDATE") and "version" in sql:
        print(f"  {name[conn]}: {shown}  -> {cursor.rowcount} row(s) changed")
        return cursor.rowcount
    print(f"  {name[conn]}: {shown}")
    return None


def reset(title: str) -> None:
    a.execute("UPDATE counter SET total = 0, version = 0 WHERE id = 1")
    print(title)


def total() -> int:
    return a.execute("SELECT total FROM counter WHERE id = 1").fetchone()[0]


reset("1. Read the total, add in Python, write the sum back")
seen_a = run(a, "SELECT total FROM counter WHERE id = 1")
seen_b = run(b, "SELECT total FROM counter WHERE id = 1")
run(a, "UPDATE counter SET total = ? WHERE id = 1", seen_a + 5)
run(b, "UPDATE counter SET total = ? WHERE id = 1", seen_b + 10)
print(f"  the total is {total()}, not 15\n")

reset("2. Let the database do the addition, in one statement")
run(a, "UPDATE counter SET total = total + ? WHERE id = 1", 5)
run(b, "UPDATE counter SET total = total + ? WHERE id = 1", 10)
print(f"  the total is {total()}\n")

reset("3. Read and write inside a transaction")
run(a, "BEGIN")
seen_a = run(a, "SELECT total FROM counter WHERE id = 1")
run(b, "BEGIN")
seen_b = run(b, "SELECT total FROM counter WHERE id = 1")
run(a, "UPDATE counter SET total = ? WHERE id = 1", seen_a + 5)
if run(b, "UPDATE counter SET total = ? WHERE id = 1", seen_b + 10) == "refused":
    run(b, "ROLLBACK")
run(a, "COMMIT")
print("  B starts again, from the beginning:")
run(b, "BEGIN")
seen_b = run(b, "SELECT total FROM counter WHERE id = 1")
run(b, "UPDATE counter SET total = ? WHERE id = 1", seen_b + 10)
run(b, "COMMIT")
print(f"  the total is {total()}\n")

reset("4. Take the write lock before reading: BEGIN IMMEDIATE")
run(a, "BEGIN IMMEDIATE")
seen_a = run(a, "SELECT total FROM counter WHERE id = 1")
run(b, "BEGIN IMMEDIATE")
run(a, "UPDATE counter SET total = ? WHERE id = 1", seen_a + 5)
run(a, "COMMIT")
run(b, "BEGIN IMMEDIATE")
seen_b = run(b, "SELECT total FROM counter WHERE id = 1")
run(b, "UPDATE counter SET total = ? WHERE id = 1", seen_b + 10)
run(b, "COMMIT")
print(f"  the total is {total()}\n")

reset("5. Write only if nobody has written since the read: a version column")
total_a, version_a = run(a, "SELECT total, version FROM counter WHERE id = 1")
total_b, version_b = run(b, "SELECT total, version FROM counter WHERE id = 1")
run(a, "UPDATE counter SET total = ?, version = ? WHERE id = 1 AND version = ?",
    total_a + 5, version_a + 1, version_a)
if run(b, "UPDATE counter SET total = ?, version = ? WHERE id = 1 AND version = ?",
       total_b + 10, version_b + 1, version_b) == 0:
    print("  B changed nothing, so B reads again and retries:")
    total_b, version_b = run(b, "SELECT total, version FROM counter WHERE id = 1")
    run(b, "UPDATE counter SET total = ?, version = ? WHERE id = 1 AND version = ?",
        total_b + 10, version_b + 1, version_b)
print(f"  the total is {total()}")

a.close()
b.close()
workdir.cleanup()

1. Read, add, write back

Both connections read 0, A writes 5, and B writes 10 over it. Each statement ran as its own transaction, so each was atomic, and the database did exactly what it was told: set the total to 5, then set it to 10. This is the lost update of the first lesson, one layer down.

2. SET total = total + ?

Here the load, the add and the store happen inside one statement, and the statement runs as one transaction, so B's statement adds 10 to the 5 that A's statement committed. This is the database's atomic add — the fetch_add of SQL — and the fix to reach for first whenever the new value is arithmetic on the old one.

3. A transaction around the read and the write

Both connections begin a transaction and read 0. A's UPDATE takes SQLite's RESERVED lock, which only one connection can hold at a time; B's UPDATE needs the same lock, and because the program opened B with timeout=0, SQLite refuses it at once with SQLITE_BUSY. B rolls back, starts again, and this time reads 5.

The transaction merged nothing. It turned a silent overwrite into an error, and the application had to catch the error and do its work again from the first read. SQLite's File locking and concurrency ↗ describes the locks. With Python's default timeout of five seconds, B's statement would first wait for the lock rather than fail on the spot.

4. BEGIN IMMEDIATE

BEGIN IMMEDIATE ↗ takes the write lock at BEGIN, before the SELECT. B cannot begin until A has committed, and when it does, its read sees 5. This is the lock around all three steps from Keeping every update.

5. A version column

The UPDATE changes the row only if its version is still the one the connection read, and bumps it. A's write changes one row; B's changes none, which is how B learns that someone wrote since its read, and B reads again and retries. It is compare-and-swap in SQL, often called optimistic locking because nothing is locked while the application works out the new value. Because the check and the write are one statement, the same UPDATE … WHERE version = ? protects the total in any SQL database. (Not machine-checked here.)

PostgreSQL

CI runs SQLite, because Python's standard library includes it. PostgreSQL locks rows rather than a whole database, and a writer that finds a row locked waits instead of failing, so its answers are different. demo/postgres.sh runs four of the scenarios against PostgreSQL in Docker with two real sessions: A starts first and keeps its transaction open for three seconds, and B starts one second later. Each session's transcript is printed after both have finished. \gset stores the result of a SELECT in the psql variable :total.

Real runs — PostgreSQL 18.4 in Docker postgres:latest, driven by psql; x86-64 Mac, macOS 26; 1 run, 2026-09-14

1. Let the database do the addition, in one statement
  A: BEGIN;
  A: UPDATE counter SET total = total + 5 WHERE id = 1;
  A:    ... keeps the transaction open for 3 seconds
  A: COMMIT;
  B: UPDATE counter SET total = total + 10 WHERE id = 1 RETURNING total;
  B:    returned 15
  B:    waited 2.0 s
  the total is 15

2. Read and write inside a transaction, at the default READ COMMITTED
  A: BEGIN;
  A: SELECT total FROM counter WHERE id = 1 \gset
  A:    loaded 0
  A: UPDATE counter SET total = :total + 5 WHERE id = 1;
  A:    ... keeps the transaction open for 3 seconds
  A: COMMIT;
  B: BEGIN;
  B: SELECT total FROM counter WHERE id = 1 \gset
  B:    loaded 0
  B: UPDATE counter SET total = :total + 10 WHERE id = 1;
  B:    waited 2.0 s
  B: COMMIT;
  the total is 10

3. The same, at REPEATABLE READ
  A: BEGIN ISOLATION LEVEL REPEATABLE READ;
  A: SELECT total FROM counter WHERE id = 1 \gset
  A:    loaded 0
  A: UPDATE counter SET total = :total + 5 WHERE id = 1;
  A:    ... keeps the transaction open for 3 seconds
  A: COMMIT;
  B: BEGIN ISOLATION LEVEL REPEATABLE READ;
  B: SELECT total FROM counter WHERE id = 1 \gset
  B:    loaded 0
  B: UPDATE counter SET total = :total + 10 WHERE id = 1;
  B:    waited 2.0 s
  B:    error: could not serialize access due to concurrent update
  B: ROLLBACK;
  the total is 5

4. Lock the row when reading it: SELECT ... FOR UPDATE
  A: BEGIN;
  A: SELECT total FROM counter WHERE id = 1 FOR UPDATE \gset
  A:    loaded 0
  A: UPDATE counter SET total = :total + 5 WHERE id = 1;
  A:    ... keeps the transaction open for 3 seconds
  A: COMMIT;
  B: BEGIN;
  B: SELECT total FROM counter WHERE id = 1 FOR UPDATE \gset
  B:    waited 2.0 s
  B:    loaded 5
  B: UPDATE counter SET total = :total + 10 WHERE id = 1;
  B: COMMIT;
  the total is 15

1. total = total + 10 waited, then added to A's 5. B's UPDATE found the row locked by A, waited the two seconds until A committed, and then applied itself to the row as A had left it. PostgreSQL's Transaction isolation ↗ describes this for its default level, READ COMMITTED: a second updater waits for the first, then re-reads the updated row and applies its change to that.
2. At READ COMMITTED, the transaction did not help. B's SELECT read 0, because A had not committed yet. B's UPDATE waited for A's row lock exactly as in the first scenario — but the value it was told to write, :total + 10, had been worked out from B's read before it was sent, and was simply 10. The re-read found the row and wrote 10 over A's 5. The lost update, inside two transactions. In SQLite's third scenario, the same shape was refused.
3. At REPEATABLE READ, it was refused. B's UPDATE again waited for A, and when A committed a change to the row that B had read, B's update failed with could not serialize access due to concurrent update, leaving A's 5. The same page says that applications using this level must be prepared to retry transactions that fail this way.
4. SELECT … FOR UPDATE locked the row at the read. B's read waited for A to commit and then loaded 5, so B computed 15. The row-level locks are described in Explicit locking ↗.

To run it: bash demo/postgres.sh from the lesson folder, with Docker running; pass another image, such as postgres:17, as the argument.

What to do

• Let the database do the arithmetic: UPDATE counter SET total = total + ? WHERE id = ?.
• When the new value needs the application's own logic, lock the row before reading it — SELECT … FOR UPDATE in PostgreSQL, BEGIN IMMEDIATE in SQLite — or write with a version check and retry when the UPDATE changes no rows.
• Do not count on BEGIN alone. Whether a transaction refuses a conflicting write depends on the database and on the isolation level, and at PostgreSQL's default a read-then-write transaction still loses the update.
• Treat a refusal as a retry. SQLITE_BUSY or a serialization failure means: start the transaction again, from its first read.
• Keep an idempotency key in the same transaction as the total, so that a retried request is recognised by the same atomic step that applies it — see When does order change a sum?.

See also

• Is total += n safe on two threads? — the same interleaving in memory.
• Keeping every update — the lock, the atomic add and the single owner, which scenarios 4, 2 and a server with one worker are the database's versions of.
• Python's sqlite3 ↗ — autocommit, timeout, and sqlite_errorname.
• Concepts: Safety failure · Race condition · Compare-and-swap · Multi-version concurrency control