Lost updates
Try two-writer practice before reading the results below.
Two transactions can read the same balance, compute a new value in application code, and write that stale value back. The concept lesson defines the lost-update problem. These PostgreSQL 18.6 Scenarios compare that specific read-modify-write operation at READ COMMITTED and REPEATABLE READ.
Watch a deposit disappear
Both transactions read 100 and write 110. Both commit, and the asserted final balance is 110 rather than the intended 120.
Two app servers process a +10 deposit each: read the balance, add 10 in code, write it back.
A> BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
B> BEGIN ISOLATION LEVEL READ COMMITTED;
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.6 · Run it yourself · Scenario source
No SQL error for this overwrite
The database accepts both writes in this schedule. The Scenario does not inspect application logs or monitoring, and does not show that every application must miss the loss.
REPEATABLE READ turns it into an error
With REPEATABLE READ snapshots established before A commits, B's stale UPDATE returns 40001. The balance is then 110: B's deposit has not committed. The Scenario executes B again in a fresh transaction, rereads 110, and asserts a final balance of 120.
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; -- only A's deposit has committed so far
balance
---------
110
(1 row)Retrying B from scratch reads the fresh 110 and correctly produces 120.
B> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
110
(1 row)
B> UPDATE accounts SET balance = 110 + 10 WHERE id = 1;
UPDATE 1
B> COMMIT;
COMMIT
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
120
(1 row)Verified against PostgreSQL 18.6 · Run it yourself · Scenario source
This retry succeeds under the displayed schedule. The PostgreSQL 18 updating-command contract explains the post-snapshot target-row conflict. Raising isolation does not protect a stale value read outside the transaction whose UPDATE uses it. Recovery must rerun the reads and decision, and other schedules can require more retries.
Fixing lost updates demonstrates atomic SQL updates, FOR UPDATE, and version checks with their writer-participation requirements.