Exercise 03 of 3/Applied/1 hour 15 minutes
Two doctors go off call at once
A hospital on-call roster with one rule: at least one doctor must always be on call. Two doctors, each checking the rule and then removing themselves, at the same instant. Both checks pass. Both writes succeed. The rule is now broken and no row was ever written twice.
Every anomaly so far has involved two transactions fighting over one row. Snapshot isolation handles those well, which is why REPEATABLE READ feels like the end of the story.
It is not. There is a whole class of bug where two transactions read the same rows, each decides an action is safe, and then each writes a different row. No write conflicts with any other write. Every transaction is individually correct. The invariant they were both protecting is broken anyway.
This is write skew, it is why SERIALIZABLE exists, and once you have seen it you will start finding it in code you already shipped.
Build a roster with one rule
Set this up first
Same containers as exercise 01 — if they are still running, skip to the table.
docker run --rm -d --name iso-pg -e POSTGRES_PASSWORD=secret -p 5440:5432 postgres:16
docker run --rm -d --name iso-my -e MYSQL_ROOT_PASSWORD=secret -p 3307:3306 mysql:8You need two terminals, each holding its own connection:
docker exec -it iso-pg psql -U postgres # PostgreSQL
docker exec -it iso-my mysql -uroot -psecret # MySQLRemember that MySQL needs VARCHAR(64) where Postgres takes TEXT for a keyed
column, and START TRANSACTION where Postgres takes BEGIN. Tear down with
docker rm -f iso-pg iso-my.
Do this
Create a doctors table with two doctors on call, and write the shift-swap the careful way: inside a transaction, check that at least two are on call, and only then take yourself off.
CREATE TABLE doctors (
name TEXT PRIMARY KEY,
on_call BOOLEAN NOT NULL
);
INSERT INTO doctors VALUES ('alice', true), ('bob', true);The application rule, written exactly as a careful developer would write it:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call = true; -- must be >= 2 to leave
-- if count >= 2:
UPDATE doctors SET on_call = false WHERE name = 'alice';
COMMIT;Read that and convince yourself it is safe. It guards the write with exactly the right check, inside a transaction, at an isolation level stronger than the Postgres default.
Read that transaction. It guards the write with exactly the right check, inside a transaction, at a level stronger than the Postgres default. Is it safe?
AnswerCommit to one first
No — and it is worth sitting with how correct it looks. There is nothing to point at in the code. The check is right, the ordering is right, the isolation level is above the default.
Everything about this transaction is fine in isolation. That is precisely the problem: it is only wrong in the presence of another transaction it can never see.
Break the invariant
Do this
Interleave two sessions at REPEATABLE READ, so both read the count before either writes, then each removes a different doctor. Commit both.
-- A -- B
BEGIN ISOLATION LEVEL REPEATABLE READ; BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors
WHERE on_call; -- 2, ok to go
SELECT count(*) FROM doctors
WHERE on_call; -- 2, ok to go
UPDATE doctors SET on_call=false
WHERE name='alice';
UPDATE doctors SET on_call=false
WHERE name='bob';
COMMIT; COMMIT;
SELECT count(*) FROM doctors WHERE on_call; -- 0Write skew at REPEATABLE READ — step through it
BEGIN ISOLATION LEVEL REPEATABLE READ;BEGIN ISOLATION LEVEL REPEATABLE READ;SELECT count(*) FROM doctors WHERE on_call;→ 2SELECT count(*) FROM doctors WHERE on_call;→ 2UPDATE doctors SET on_call=false WHERE name='alice';UPDATE doctors SET on_call=false WHERE name='bob';COMMIT;COMMIT;
The same sequence at SERIALIZABLE
BEGIN ISOLATION LEVEL SERIALIZABLE;BEGIN ISOLATION LEVEL SERIALIZABLE;SELECT count(*) FROM doctors WHERE on_call;→ 2SELECT count(*) FROM doctors WHERE on_call;→ 2UPDATE doctors SET on_call=false WHERE name='alice';UPDATE doctors SET on_call=false WHERE name='bob';→ ERROR: could not serialize access due to
read/write dependencies among transactionsCOMMIT;
Nobody is on call. And look at what did not happen: no dirty read, no non-repeatable read, no lost update — nothing was overwritten, because A wrote alice's row and B wrote bob's. There is no write-write conflict for the database to detect.
This is why REPEATABLE READ cannot help. Snapshot isolation gives you two guarantees, and both of them held here:
- Each transaction sees a consistent snapshot.
- Concurrent writers to the same row conflict.
Neither covers this. The premise each transaction relied on — "there are two doctors on call" — was invalidated by a write to a row that transaction never touched.
A snapshot is a promise about what you read. It is not a promise that what you read is still true when you write.
Work out why the level did not help
You raised the level to REPEATABLE READ and it changed nothing. Which guarantee did you think you were buying?
AnswerCommit to one first
Snapshot isolation gives you two things, and both held:
- Each transaction sees a consistent snapshot.
- Concurrent writers to the same row conflict.
A wrote the alice row. B wrote the bob row. Different rows, so there was no write-write conflict for the database to detect — and the premise each relied on was invalidated by a write it never touched.
A snapshot is a promise about what you read. It is not a promise that what you read is still true when you write.
Hint 1Name the dependency the database cannot see
A's write invalidates B's read, and B's write invalidates A's. That is a read-write dependency in both directions — a cycle. Nothing in snapshot isolation looks for those.
Hint 2Why FOR UPDATE is not the general fix
Try SELECT ... FROM doctors WHERE on_call FOR UPDATE. It works here. Now consider the version of this bug where the transaction's decision depends on rows that do not exist yet — booking a meeting room by checking for overlapping bookings, then inserting one. There is no row to lock. What would you lock instead?
Fix it properly
Do this
Fix it twice: once with SERIALIZABLE, and once without — for the case where you are on MySQL, or cannot change the level, or cannot accept the abort rate.
Solution — SERIALIZABLE, and the fix for when you cannot have itTry it first
The real fix: true serializability
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE on_call = true;
UPDATE doctors SET on_call = false WHERE name = 'alice';
COMMIT;One of the two transactions now fails:
ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during write.
HINT: The transaction might succeed if retried.
(The DETAIL wording varies — "during write" or "during commit attempt" — depending on when Postgres spots the cycle. The SQLSTATE is 40001 either way, and that is what your retry handler should key on, never the message.)
Postgres implements Serializable Snapshot Isolation. On top of snapshot isolation it tracks the read-write dependencies between concurrent transactions and looks for the cycle that write skew requires. When it finds one, it aborts a participant — the "pivot" — rather than blocking anybody. This is why SSI is cheap for read-mostly workloads: no extra locks, no waiting, just bookkeeping and an occasional abort.
The price is non-negotiable, and it is the thing teams get wrong: SERIALIZABLE means your application must retry.
A 40001 is not an error in the normal sense. It is the database telling you to run the transaction again.
Deploy SERIALIZABLE without a retry wrapper and you have converted a rare silent corruption into a frequent visible 500 — at which point you roll it back and conclude serializable is unusable. What the wrapper needs:
- Retry on
40001(serialization failure) and40P01(deadlock detected). - A bounded number of attempts, with jittered backoff.
- A transaction body that is safe to run twice.
The fix when you cannot use SERIALIZABLE: materialize the conflict
Sometimes you are on MySQL, or on a level you cannot change, or the abort rate is unacceptable. Then you have to create a row for the transactions to fight over, so that the invisible read-write dependency becomes a visible write-write one:
CREATE TABLE oncall_rota (shift_id INT PRIMARY KEY, lock_token INT NOT NULL);
INSERT INTO oncall_rota VALUES (1, 0);
BEGIN;
SELECT lock_token FROM oncall_rota WHERE shift_id = 1 FOR UPDATE; -- serialise here
SELECT count(*) FROM doctors WHERE on_call = true;
UPDATE doctors SET on_call = false WHERE name = 'alice';
COMMIT;The oncall_rota row carries no data. It exists purely so that two transactions touching the same shift must queue. This is materializing conflicts, and it is ugly on purpose: you are hand-encoding a dependency the database would have found for you. Use it when you must, document why the row exists, and choose its granularity carefully — one row per shift serialises only doctors on that shift; one row for the whole table serialises your entire hospital.
Why FOR UPDATE on the read is not the general answer
SELECT ... FROM doctors WHERE on_call FOR UPDATE does fix this instance, because the rows being read are the rows being written. It fails as a general technique the moment the decision depends on the absence of rows:
-- book a meeting room if nothing overlaps
SELECT count(*) FROM bookings
WHERE room = 'A' AND during OVERLAPS ('14:00','15:00') FOR UPDATE; -- 0 rows
INSERT INTO bookings VALUES ('A', '14:00', '15:00');FOR UPDATE locks the rows it returns. It returned none, so it locked nothing, and two transactions both book the room. This is write skew over a phantom, it is the same bug, and it is why the general solutions are SSI or a materialized conflict row — or, better here, a real constraint: EXCLUDE USING gist (room WITH =, during WITH &&), which pushes the invariant into the database where no isolation level can route around it.
The hierarchy worth remembering. If the invariant can be a constraint — unique, check, exclusion, foreign key — make it a constraint; it holds at every isolation level and against every client, including the psql session someone opens at 2am. If it cannot, use SERIALIZABLE with retries. If you cannot have that, materialize the conflict. Never rely on a check-then-write in application code.
Learn to spot it in a code review
You add FOR UPDATE to the SELECT and it fixes the roster. Why is that not the general answer?
AnswerCommit to one first
Because FOR UPDATE locks the rows it returns.
Change the scenario to booking a meeting room: you check for overlapping bookings, get zero rows, and insert. Zero rows returned means zero rows locked, so two transactions both book the room.
That is write skew over a phantom — the same bug, where the thing you depended on was the absence of rows. You cannot lock what does not exist yet.
What shape in a code review should make you suspect write skew?
AnswerCommit to one first
A count(*) or an existence check, followed by an insert or update, inside one transaction — where the rule spans multiple rows or the absence of rows.
Booking systems. Inventory with a reservation step. Approval flows needing two approvers. Shift rosters. Anything that counts rows and then writes one.
Ten minutes on that shape is worth it even when nobody has complained, because write skew produces a small number of impossible records rather than an outage — so it is usually found months later by someone reconciling a report.
Who prevents write skew, and what it costs
PostgreSQL — SSI at SERIALIZABLE
Prevents write skew properly from 9.1 onward, including the phantom case, by tracking read-write dependencies and aborting the pivot transaction. No extra locking, so readers still do not block. Costs: a 40001 retry obligation, some memory for predicate locks (which can escalate from row to page granularity under load and raise the false-positive abort rate), and it does not work across SERIALIZABLE and non-SERIALIZABLE transactions mixed — a READ COMMITTED writer is invisible to the detector, so the guarantee only holds among transactions that all opt in.
MySQL / InnoDB
Its default REPEATABLE READ permits write skew, as above. Its SERIALIZABLE is not SSI — it implicitly converts every plain SELECT into SELECT ... LOCK IN SHARE MODE, so isolation is achieved by locking rather than by detection. That does prevent write skew, and it means readers block writers, deadlocks become common rather than rare, and throughput falls sharply. In practice MySQL shops do not run SERIALIZABLE; they materialize conflicts with FOR UPDATE on a sentinel row, or use unique constraints. InnoDB's gap locks at REPEATABLE READ do block some phantom insertions, which prevents the meeting-room case by accident — good luck reasoning about which of your queries take gap locks.
Distributed stores — CockroachDB, Spanner, FoundationDB, Yugabyte
These default to serializable and are the interesting counterexample to "serializable is too slow to use". Spanner uses two-phase locking with TrueTime; CockroachDB and FoundationDB use optimistic concurrency with transaction retries — which is why both ship a retry loop in the client library and tell you in the first page of the docs that your transaction must be retryable. The industry direction is clear: make serializable the default and make retries the application's normal path. If you are writing new code, adopting the retry wrapper now is how you stay portable to any of them.
How real systems actually use this. Almost no team running Postgres at READ COMMITTED — which is almost every team running Postgres — is protected from write skew, and most of them are fine, because most of their invariants are per-row and the engine handles those. The ones that get burned share a shape: a rule spanning multiple rows or the absence of rows, checked in application code. Booking systems. Inventory with a reservation step. Approval flows needing two approvers. Shift rosters. Anything counting rows and then writing one. When you find that shape in a code review, it is worth ten minutes even if nobody has complained yet — write skew produces a small number of impossible records rather than an outage, so it is usually discovered months later by someone reconciling a report.
Before you move on
You should be able to explain each of these without looking.
- Why
REPEATABLE READcannot prevent write skew, in one sentence about which row each transaction wrote. - What SSI tracks that snapshot isolation does not.
- Why
SERIALIZABLEis unusable without a retry wrapper, and which SQLSTATEs the wrapper catches. - Why
FOR UPDATEfixes the roster and not the meeting room. - Why MySQL's
SERIALIZABLEand Postgres's have the same name and completely different performance characteristics. - The shape of code that should make you suspect write skew in review.
Go further
- Implement the meeting-room booking three ways — SSI, a materialized conflict row, and a Postgres exclusion constraint — and benchmark all three under 200 concurrent booking attempts. The constraint will win, which is the real lesson.
- Write the retry wrapper: catch
40001, jittered exponential backoff, bounded attempts, and a metric for the retry rate. Then deliberately raise contention until the retry rate becomes your bottleneck, and find where SSI stops being the right tool. - Take the bank from exercise 03 and add the invariant "the sum of all balances is constant". Now express a transfer as two single-row updates in one transaction and confirm that this invariant is not subject to write skew. Understanding why it is safe is as instructive as the cases that are not.
- Go and look for the shape in a repository you work on: a
count(*)or an existence check, followed by an insert or update, inside one transaction. Write down what happens if two requests arrive simultaneously.