Compare-and-Set: the Loser Silently Gets UPDATE 0

Two sessions both read version = 5 and both run the same guarded write UPDATE docs SET content=…, version=6 WHERE id=1 AND version=5. Predict both report UPDATE 1. Only one does — the loser reports UPDATE 0 and must retry, a lost update prevented with no lock. Raise the isolation level to REPEATABLE READ and the same write instead aborts with SQLSTATE 40001.

Concept

Two clients open the same document, both read version = 5, both edit, both save. To prevent one from silently clobbering the other (a lost update), each save is written as a compare-and-set: UPDATE docs SET content=…, version=6 WHERE id=1 AND version=5 — write only if the row is still at the version I read. Run both saves against a real PostgreSQL 16 and only one reports UPDATE 1. The loser reports UPDATE 0: by the time it runs, the winner has already bumped the row to version 6, so the loser's WHERE … AND version=5 matches nothing. A lost update is prevented with no lock at all — the loser just learns it lost and must re-read and retry. Then raise the isolation level to REPEATABLE READ and the same compare-and-set does something different again: instead of UPDATE 0 it aborts the whole transaction with ERROR: could not serialize access due to concurrent update (SQLSTATE 40001).

Provenance

Designing Data-Intensive Applications, 2nd ed. (Kleppmann & Riccomini, 2025), Chapter 8 — Transactions, §"Conditional writes (compare-and-set)":

The purpose of this operation is to avoid lost updates by allowing an update to happen only if the value has not changed since you last read it. If the current value does not match what you previously read, the update has no effect, and the read-modify-write cycle must be retried.

The book also flags the trap: if the database lets the WHERE clause read from an old snapshot, the compare can pass against stale data and the update is lost anyway — so "check whether your database's compare-and-set operation is safe before relying on it." This exercise checks Postgres's, and finds it safe two different ways depending on isolation level.

Prerequisites

The surprise turns on when an UPDATE's WHERE clause is evaluated. Each line notes where it shows up.

1. Optimistic concurrency control / compare-and-set — instead of locking a row while you think, you read it, prepare your write, and commit the write guarded by a check that nothing changed. It shows up as the version column in every WHERE clause.
2. The read-modify-write race (lost update) — two transactions read the same value, both compute a new one from it, and the second write overwrites the first with no trace. It shows up as the thing the guard is there to stop — and as the UPDATE 0 that stops it.
3. The UPDATE-WHERE visibility rule (the subtle one) — an UPDATE's WHERE is not simply evaluated against your transaction's read snapshot. Postgres finds candidate rows by the snapshot, then, before writing, re-checks each against the latest committed version of the row. Under READ COMMITTED that re-check quietly filters the row out (UPDATE 0); under REPEATABLE READ it can't quietly proceed, so it aborts with SQLSTATE 40001. This rule is the whole reason compare-and-set is safe here — and it's the one most people predict wrong.

Mental model

Compare-and-set is one sentence: "write only if this row is unchanged since I read it." The version column is how "unchanged" is spelled — every writer reads the version, then guards its write with WHERE id=1 AND version=<what I read> and sets version = <that + 1>.

• If nobody wrote in between, the guard matches, the row is updated, and the version advances: UPDATE 1.
• If a concurrent writer already committed, they moved the version past what you read, so your guard matches zero rows — you don't overwrite them, you get UPDATE 0 and retry. The lock-free part is exactly this: no writer ever waits; the loser simply discovers, after the fact, that its precondition is stale.

The subtlety is how the loser's guard "sees" the concurrent commit. A plain SELECT in a REPEATABLE READ transaction is frozen on a snapshot and would still show the old version — but an UPDATE's WHERE is re-checked against the newest committed row, which is why the guard works even where a bare read wouldn't.

code/compare_and_set.py is the capture harness: it drives these same two sessions programmatically (psycopg) so the transcripts below are real, verbatim output. You run the exercise by hand in two psql sessions.

Setup

# The shared server (already running for this lab -- do not restart it):
docker run -d --name ddia-ch08-pg -p 5432:5432 \
  -e POSTGRES_HOST_AUTH_METHOD=trust postgres:16

# Create your own database and the guarded table:
docker exec ddia-ch08-pg psql -U postgres -c "CREATE DATABASE ex_ch08_cas;"
docker exec ddia-ch08-pg psql -U postgres -d ex_ch08_cas -c \
  "CREATE TABLE docs (id int PRIMARY KEY, content text, version int);
   INSERT INTO docs VALUES (1, 'draft', 5);"

# Open TWO psql sessions, in two terminals -- this is Session A and Session B:
#   Session A:  docker exec -it ddia-ch08-pg psql -U postgres -d ex_ch08_cas
#   Session B:  docker exec -it ddia-ch08-pg psql -U postgres -d ex_ch08_cas

To re-capture the transcripts programmatically instead of by hand:

uv run --python 3.12 --with "psycopg[binary]" python3 code/compare_and_set.py

Do not read the output yet — make each prediction first.

-- Session A:
SELECT version FROM docs WHERE id=1;
-- Session B:
SELECT version FROM docs WHERE id=1;

-- Session A:
UPDATE docs SET content='A wins', version=6 WHERE id=1 AND version=5;
-- Session B (after A):
UPDATE docs SET content='B wins', version=6 WHERE id=1 AND version=5;

-- Session A:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT version FROM docs WHERE id=1;
-- Session B (autocommit), in between:
UPDATE docs SET content='B bumped', version=6 WHERE id=1 AND version=5;
-- Session A again, same transaction:
SELECT version FROM docs WHERE id=1;
UPDATE docs SET content='A wins', version=6 WHERE id=1 AND version=5;
COMMIT;

What you should see

• Step 1: both sessions read version = 5.
• Step 2: Session A's guarded write returns UPDATE 1; the identical write in Session B returns UPDATE 0 — the loser's guard no longer matches.
• Step 3: under REPEATABLE READ, Session A's SELECT still shows 5 (frozen snapshot) while the guarded UPDATE aborts with ERROR: could not serialize access due to concurrent update (SQLSTATE 40001) — not UPDATE 0.
• Under READ COMMITTED (the default, and Step 2's mode), the same interleaving yields UPDATE 0 instead of the abort.

Why

A blind write — UPDATE docs SET content=…, version=6 WHERE id=1 — has no memory of what you read. Two of them racing is the textbook lost update: whoever writes second wins, and the first edit is gone with nothing logged. Compare-and-set turns that blind write into a guarded one by folding the precondition into the WHERE: AND version=5 says "…but only if this row is still the one I read." The guard is a predicate the database evaluates atomically as part of the write, so it can't be raced the way a separate "check, then write" could.

The mechanism that makes it correct is the one people mispredict: an UPDATE's WHERE is evaluated against the latest committed version of the row, not against your read snapshot. Postgres finds candidate rows using your snapshot, but before modifying one it re-reads the newest committed tuple and re-applies the predicate to that. So once A commits version 6, B's AND version=5 is tested against a row that now says 6, matches nothing, and B writes nothing. The guard changed underneath B precisely because a concurrent writer changed the row — which is exactly the condition compare-and-set exists to detect. That B read 5 a moment earlier is irrelevant; the write path re-checks against reality.

The two isolation levels differ only in how they deliver the "you lost, retry" message. Under READ COMMITTED, that re-check against the latest row is allowed to simply drop the non-matching row, so B gets a quiet UPDATE 0. Under REPEATABLE READ, silently substituting a newer row for the one in your snapshot would break the guarantee that your transaction sees a single consistent snapshot — so Postgres instead aborts with serialization_failure (40001) and makes you retry the whole transaction. Both are safe compare-and-set; one hands you a rowcount to branch on, the other hands you an error to catch.

Go deeper

• Watch the abort become a no-op. Re-run Step 3 but change Session A's BEGIN to BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED. Predict the second SELECT and the UPDATE: the snapshot un-freezes (the re-read now shows 6) and the guarded UPDATE returns UPDATE 0 instead of aborting. Same guard, same safety, different retry signal.
• Handle the retry. Wrap the compare-and-set in a loop: read the version, attempt the guarded update, and if it reports UPDATE 0 (or raises 40001), re-read and try again with the fresh version. This is the full optimistic concurrency cycle — confirm two concurrent loopers both eventually succeed.
• Break the guard. Have Session B save with a blind UPDATE docs SET content='B clobbers' WHERE id=1 (no version check) after A commits. Predict UPDATE 1, and confirm A's write is silently lost — the lost update compare-and-set was preventing, reintroduced by one writer opting out.

Sources: DDIA 2e, Ch. 8, §"Preventing Lost Updates", §"Conditional writes (compare-and-set)" · PostgreSQL 16 manual, §13.2 "Transaction Isolation" (Read Committed / Repeatable Read).