Lesson 20 · Transactions & concurrency

Concurrency anomalies & SSI

The bugs that appear under load — and exactly how to prevent each.

Your win: name the classic anomalies (especially lost update and write skew) and state the precise fix for each — the question that separates "I've heard of isolation levels" from "I understand them."

The anomaly ladder

Anomaly	What happens	Stopped at
Dirty read	see another tx's uncommitted change	always (Postgres)
Non-repeatable read	re-reading a row gives a new value	Repeatable Read
Phantom read	a re-run query gains/loses rows	Repeatable Read (in PG)
Lost update	two read-modify-writes clobber each other	see below
Write skew	two txns each valid alone, together break an invariant	Serializable

Lost update — the everyday one

Two transactions read balance = 100, each adds 10, each writes 110 — one update is lost (should be 120). Read Committed doesn't stop it. Three fixes:¹

SELECT … FOR UPDATE — lock the row before the read-modify-write (Lesson 19), so the second tx waits.
Atomic update — UPDATE t SET balance = balance + 10 does the read and write in one locked statement.
Serializable — the DB detects the conflict and aborts one (retry).

Write skew — the subtle one

Two on-call doctors, rule "at least one must stay on call". Both check "the other is on call → I can leave", both update — now zero are on call. Each transaction was valid in isolation; together they broke the invariant. Neither Read Committed nor Repeatable Read catches this — only Serializable does.¹

SSI: how Serializable catches it Postgres's Serializable uses Serializable Snapshot Isolation — it tracks dangerous read-write dependencies between concurrent transactions and, if they could produce a non-serial outcome, aborts one with SQLSTATE 40001. No extra locks; you just must be prepared to retry.

Anchor Our default is Read Committed, which tolerates these unless you guard against them — so read-modify-write paths use FOR UPDATE (or an atomic UPDATE … SET x = x + …), and UPSERT (ON CONFLICT, Lesson 4) sidesteps insert races. And database.ExecInTxWithRetry exists for exactly one reason: to retry the 40001 a Serializable transaction raises. Now you know what that error is.

PostgreSQL — Transaction Isolation (anomalies & SSI) + Rogov Part I

The docs' worked examples of each anomaly and how Serializable prevents them; Rogov for the deep MVCC/SSI mechanics.

→ postgresql.org/docs — Transaction Isolation
→ PostgreSQL 14 Internals — Part I

Check yourself (from memory)

Q1. A "lost update" happens when two transactions…

Both read a value, both write based on it, one clobbers the other. Fix: FOR UPDATE, atomic update, or Serializable.

Q2. Write skew is prevented by…

Only Serializable (SSI) detects the read-write dependency cycle and aborts one. Lower levels miss write skew.

Q3. One fix for a lost update is…

Lock the row first with FOR UPDATE (or use an atomic UPDATE … SET x = x + …, or Serializable). FOR SHARE isn't enough — it allows other share-readers.

What are lost update and write skew, and how do you prevent each?

recall, then click to reveal

LOST UPDATE — two transactions read the same value, both compute a new value, one overwrites the other (a read-modify-write race). Prevent with SELECT … FOR UPDATE, an atomic UPDATE … SET x = x + …, or SERIALIZABLE. WRITE SKEW — two transactions each read overlapping data and each make a change valid alone but together violating an invariant (the on-call doctors). Only SERIALIZABLE catches it (via SSI, tracking dangerous read-write dependencies), aborting one with SQLSTATE 40001 to retry. Our ExecInTxWithRetry retries exactly that 40001.

🎓 That's Part 5 — transactions & concurrency ACID, the three isolation levels, locks & deadlocks, and the anomalies each level stops — all resting on MVCC. Part 6 is the finale: everything you've learned, back in Go — pgx, the database layer, migrations, and RLS multi-tenancy.

Ready for the final Part 6 (PostgreSQL in Go), or a mixed quiz across Lessons 18–20 first? Ask me.

1. PostgreSQL — Transaction Isolation (Serializable & anomalies).