Lost updates
Read a value, modify it in application code, write it back — run two of these concurrently and one deposit vanishes outright: no error, no log line, nothing. Why the innocent read-modify-write pattern loses data is Concepts: the lost update problem; this page proves the loss on PostgreSQL, then shows the isolation level that refuses to play along.
Watch a deposit disappear
Two app servers process a +10 deposit each: read the balance, add 10 in code, write it back.
A> BEGIN;
BEGIN
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
B> BEGIN;
BEGIN
B> SELECT balance FROM accounts WHERE id = 1; -- B reads the same 100 — A hasn't committed
balance
---------
100
(1 row)
A> UPDATE accounts SET balance = 100 + 10 WHERE id = 1;
UPDATE 1
A> COMMIT;
COMMITB computed 100 + 10 from its stale read. Nothing stops the write — A's transaction is long gone.
B> UPDATE accounts SET balance = 100 + 10 WHERE id = 1;
UPDATE 1
B> COMMIT;
COMMIT
A> SELECT balance FROM accounts WHERE id = 1; -- two +10 deposits, but only one survived
balance
---------
110
(1 row)A's deposit vanished without any error. Fixes: atomic UPDATE, SELECT FOR UPDATE, or REPEATABLE READ — see the lesson.
Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
No error to catch
There is nothing to handle, retry, or alert on — at the default level the loss is invisible to your code, your logs, and your monitoring. You find it in the books, weeks later.
REPEATABLE READ turns it into an error
The same interleaving, one isolation level up. PostgreSQL detects that B's write would overwrite a row modified after B's snapshot — and refuses:
The same two +10 deposits — but this time at REPEATABLE READ.
A> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
B> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
A> UPDATE accounts SET balance = 100 + 10 WHERE id = 1;
UPDATE 1
A> COMMIT;
COMMITB's snapshot predates A's commit, so B's write is refused instead of silently clobbering it.
B> UPDATE accounts SET balance = 100 + 10 WHERE id = 1; -- serialization_failure
ERROR: 40001: could not serialize access due to concurrent update
B> ROLLBACK;
ROLLBACK
A> SELECT balance FROM accounts WHERE id = 1; -- A's deposit is safe; B retries and lands on 120
balance
---------
110
(1 row)Retrying B from scratch reads the fresh 110 and correctly produces 120.
Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
If you remember one thing from this chapter, make it this: read-modify-write through application code at READ COMMITTED loses updates silently. Move one level up and REPEATABLE READ (or SERIALIZABLE) turns that silent loss into SQLSTATE 40001 — the data is safe and the losing transaction retries. Raising the isolation level is only one of the fixes, and often not the best one: fixing lost updates walks through atomic updates, FOR UPDATE, and version columns, each with its own transcript.