# DDIA Ch. 8: compare-and-set — the loser silently gets `UPDATE 0`

## 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.

### Where to learn the prerequisites

- **Compare-and-set and lost updates (#1–#2):** DDIA §"Preventing Lost Updates" →
  §"Compare-and-set"; PostgreSQL manual §13.2 "Transaction Isolation".
- **The UPDATE-WHERE re-check (#3):** PostgreSQL manual §13.2.1 (Read Committed —
  "EvalPlanQual", how `UPDATE` re-reads a concurrently-updated row) and §13.2.2
  (Repeatable Read — why it raises `serialization_failure` instead).

If only one is new, make it **#3** — the moment an `UPDATE`'s `WHERE` is checked
against the latest committed row rather than your snapshot is the whole mechanism.

## Environment

Deterministic, so the rowcounts and the error reproduce exactly:

- PostgreSQL 16.14 (Debian, aarch64) in Docker on macOS 26.5.2 (Darwin 25.5.0),
  arm64; captured via psycopg[binary] on Python 3.12 (uv); deterministic.
- Shared server `ddia-ch08-pg` on `localhost:5432`, user `postgres`, trust auth;
  this exercise works in its own database `ex_ch08_cas`.
- One row: `docs(id=1, content='draft', version=5)`.

## 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.

## Steps

### Step 1 — both sessions read the same version

**Predict.** In two separate `psql` sessions, both run the same read before
anyone writes. What version does each see?

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

**Observe.**

```
Session A:  SELECT version FROM docs WHERE id=1;
 version 
---------
       5
(1 row)

Session B:  SELECT version FROM docs WHERE id=1;
 version 
---------
       5
(1 row)
```

Both see **5** — the shared starting point. Each is about to compute a new
document from this same version 5. That is the exact setup for a lost update; the
guard is what will stop it.

### Step 2 — both run the guarded write (the surprise)

**Predict.** Both sessions now save with a compare-and-set: guard on the version
they read (5), bump to 6. Session A runs first, then Session B. Predict the tag
each `UPDATE` prints. (Most people say `UPDATE 1` for both — they both read 5, and
5 is what they're checking for.)

```
-- 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;
```

**Observe.**

```
Session A:  UPDATE docs SET content='A wins', version=6 WHERE id=1 AND version=5;
UPDATE 1

Session B:  UPDATE docs SET content='B wins', version=6 WHERE id=1 AND version=5;
UPDATE 0
```

**Not both — `UPDATE 1` then `UPDATE 0`.** A's guard matched (the row was still at
5), so A wrote and the version is now 6. When B runs the *identical* statement, its
`WHERE … AND version=5` no longer matches any row — the latest committed version
is 6. B changes nothing, gets `UPDATE 0`, and would re-read and retry. No lock was
taken, no one waited, and B's blind overwrite of A never happened.

### Step 3 — the snapshot sub-surprise, under REPEATABLE READ

**Predict.** Same race, but Session A now runs inside a REPEATABLE READ
transaction: it `SELECT`s version 5 (taking its snapshot), *then* Session B
commits a bump to 6 in its own transaction, *then* Session A — still in the same
transaction — first re-`SELECT`s the version, then runs its guarded `UPDATE …
WHERE version=5`. Predict two things: what does A's second `SELECT` show, and what
does A's `UPDATE` report?

```
-- 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;
```

**Observe.**

```
Session A:  BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN
Session A:  SELECT version FROM docs WHERE id=1;
 version 
---------
       5
(1 row)

Session B:  UPDATE docs SET content='B bumped', version=6 WHERE id=1 AND version=5;
UPDATE 1

Session A:  SELECT version FROM docs WHERE id=1;     -- still the snapshot
 version 
---------
       5
(1 row)

Session A:  UPDATE docs SET content='A wins', version=6 WHERE id=1 AND version=5;
ERROR:  could not serialize access due to concurrent update
Session A:  COMMIT;
ROLLBACK
```

**The `SELECT` still says 5, but the `UPDATE` doesn't return `UPDATE 0` — it
throws.** The frozen snapshot is real: A's re-read still shows version 5, because
REPEATABLE READ pins every plain read to the transaction's opening snapshot. But
the `UPDATE`'s guard is checked against the *latest committed* row, which B moved
to 6. Under REPEATABLE READ, Postgres will not silently re-evaluate that guard
against a row your snapshot can't see — that would violate snapshot isolation — so
it refuses the write and aborts the whole transaction with SQLSTATE **40001**
(`serialization_failure`). `COMMIT` on an aborted transaction reports `ROLLBACK`.
Same lost update prevented; a louder retry signal.

## 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.

**The boundary — the guard protects nothing unless *every* writer uses it.**
Compare-and-set is a cooperative protocol: the version column only stops lost
updates because all writers agree to read it, guard on it, and bump it. A single
blind `UPDATE docs SET content=… WHERE id=1` (no `version` in the `WHERE`, or one
that doesn't advance it) sails straight through and clobbers a concurrent CAS
writer — the guard it skipped was the only thing making the row safe. And the trap
DDIA names still applies to other engines: a database whose `UPDATE … WHERE`
*does* read the guard from a stale snapshot would let B's `version=5` pass against
already-changed data, losing the update anyway. That is why the book says to
verify your database's compare-and-set is safe rather than assume it — on Postgres
16 it is, by re-checking the guard against the latest committed row (READ
COMMITTED) or refusing to proceed at all (REPEATABLE READ).

### Go deeper

1. **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.
2. **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.
3. **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.