Transactions and isolation levels · System design

All or nothing

A transaction wraps several statements so they succeed or fail together:

sql

BEGIN;
INSERT INTO links (code, long_url, owner_id) VALUES ('b2Qe', 'https://…', 42);
INSERT INTO idempotency_keys (account_id, key, status, response) VALUES (42, 'K', 201, '{…}');
COMMIT;

If the server crashes between the two inserts, neither survives. The guarantees are usually summed up as ACID:

Letter	Means	In practice
Atomic	All of the transaction happens, or none of it	No half-created links
Consistent	Constraints hold before and after	No link without an owner
Isolated	Concurrent transactions don't see each other's half-done work	The subject of this lesson
Durable	Once committed, it survives crashes	04.06 Write-ahead logs and crash recovery

Atomicity and durability are easy to state and mostly just work. Isolation is where the subtle bugs live.

Two users, one custom code

Ana and Ben both try to claim the custom code launch at the same moment. Snip's first version checks, then inserts:

sql

SELECT 1 FROM links WHERE code = 'launch';   -- nobody has it…
INSERT INTO links (code, …) VALUES ('launch', …);

Both checks run before either insert, both see "free", and both insert. Without protection, one silently overwrites or duplicates the other. Here the fix is simple: code is the primary key, so the database rejects the second insert, and Snip tells Ben the code is taken. A unique constraint is the cheapest concurrency control there is.

Not every rule fits in a constraint, though.

Write skew: the bug constraints can't catch

Free accounts may have at most 1,000 links. Ana has 999 and creates two links at once from two browser tabs:

Two transactions, each checking a limit

4 messages. Use Next and Back to follow them in order.

Text version

Tab 1 → Database: BEGIN; COUNT links → 999 — Tab 1 counts Ana's links: 999. Under the limit.
Tab 2 → Database: BEGIN; COUNT links → 999 — Tab 2 counts at the same moment and also sees 999. Neither can see the other's uncommitted insert.
Tab 1 → Database: INSERT; COMMIT — Tab 1 inserts link 1,000 and commits.
Tab 2 → Database: INSERT; COMMIT (fails) — Tab 2 inserts too. Ana now has 1,001 links. Each transaction was correct on its own; together they broke the rule.

Each transaction read the same data, made a decision based on it, and wrote something different (two new rows, not the same row). That's write skew. No unique constraint can stop it, and many isolation levels allow it.

Isolation levels

Databases offer a menu of isolation levels: stronger levels prevent more anomalies and cost more waiting or retries.

Level	Prevents	Still allows
Read committed (Postgres's default)	Seeing uncommitted data	A row changing between two reads in one transaction; lost updates; write skew
Snapshot isolation ("repeatable read" in Postgres)	The above, plus: every read sees one consistent snapshot	Write skew
Serializable	Everything: the result is as if transactions ran one at a time	Nothing, but some transactions fail and must be retried

Fixes for Snip's link limit, from most to least targeted:

Lock the thing you're deciding about. SELECT … FROM users WHERE id = 42 FOR UPDATE at the start makes the second tab wait until the first commits, then it counts 1,000 and refuses.
Run that transaction at SERIALIZABLE. The database detects the conflict and aborts one transaction; the app retries it (and the retry sees 1,000).
Make it a single write. Keep a link_count on the user row and UPDATE users SET link_count = link_count + 1 WHERE id = 42 AND link_count < 1000. One statement on one row can't be skewed.

Seen in production

Jepsen

2020

PostgreSQL 12.3

Jepsen's tests found that PostgreSQL's serializable level allowed G2-item anomalies, a form of write skew, under normal operation: six cases in a two-minute run. The bug, present in every version tested, was fixed with a patch for the next minor release. The analysis also notes that Postgres's "repeatable read" is actually snapshot isolation, which allows write skew, and recommends the documentation say so.

What we foundEven a mature database had a serializability bug, and its 'repeatable read' is really snapshot isolation.

Jepsen: PostgreSQL 12.3jepsen.io ↗

Predict first

Two transactions at snapshot isolation each read that a doctor's shift has two people on call, and each removes a different person. Both commit. What happened?

Commit to an answer before you read on.

Recap

Interview questions

MidfreqMust-knowConcept

Explain transaction isolation levels and give an anomaly each one allows.