Transactions

A transaction groups several reads and writes into one logical unit: either all of it happens, or none of it does. That sounds simple, but “isolation” — what one transaction is allowed to see of another transaction’s in-progress work — is where almost all of the subtlety (and almost all of the production bugs) live.

ACID, precisely

Atomicity and durability are fairly unambiguous. Isolation is where real implementations diverge wildly, because perfect isolation (serializability) is expensive, so most databases default to something weaker.

Isolation levels, weakest to strongest

• Read committed — you never see another transaction’s uncommitted writes (no dirty reads), and your own writes don’t get partially overwritten (no dirty writes). This is the default in Postgres and most databases. It does not protect against read skew: you can run two queries in the same transaction and see a different snapshot of the database each time, because a concurrent transaction committed in between.
• Snapshot isolation (repeatable read) — your whole transaction sees one consistent snapshot of the database, taken at the start. This fixes read skew entirely. It does not prevent write skew, a subtler bug where two transactions each read overlapping data, then each write to different rows, and the combination violates an invariant neither transaction alone would have violated.
• Serializable — transactions behave as if they ran one at a time, in some order, even though they actually ran concurrently. This is the only level that rules out every race condition, at the highest performance cost.

-- Under snapshot isolation, this is vulnerable to write skew:
BEGIN;
SELECT COUNT(*) FROM doctors WHERE on_call = true;  -- sees 2, both still see 2 concurrently
-- app logic: "count > 1, so it's safe for me to go off call"
UPDATE doctors SET on_call = false WHERE id = current_doctor_id;
COMMIT;

How serializability is actually achieved

• Literal serial execution — run transactions one at a time on a single thread (used by Redis, and by VoltDB for short transactions). Only works if each transaction is fast, since there’s no concurrency to hide latency.
• Two-phase locking (2PL) — the classic pessimistic approach: transactions acquire locks on everything they touch, and hold them until commit. Correct, but prone to deadlocks and lock contention under load.
• Serializable snapshot isolation (SSI) — an optimistic approach: transactions run concurrently on snapshots as normal, but the database tracks read/write dependencies and aborts (forcing a retry) any transaction whose result wouldn’t have been possible in some serial order.