Lesson 0005 · Transactions & Isolation

Snapshot isolation

A snapshot is just a captured set of transaction IDs — so an isolation level isn't a new mechanism, only a choice of when you take that snapshot. Three levels, one idea.

~11 minWarm-up recall + quiz + MySQL contrastCheat sheet: here

Last lesson you learned MVCC decides visibility by comparing a tuple's xmin/ xmax against your snapshot. Now name the snapshot precisely: it is a captured list of which transactions had committed at a chosen instant — effectively a set of XIDs. Given that, the whole topic of isolation collapses to a single question: when do you take the snapshot, and how long do you hold it? Everything else is bookkeeping.

Postgres has three levels — and never dirty-reads

You can ask for any of the four SQL-standard levels, but Postgres implements three: Read Committed (the default), Repeatable Read, and Serializable. Asking for Read Uncommitted quietly gives you Read Committed. — PostgreSQL Docs: "internally only three distinct isolation levels are implemented, i.e., PostgreSQL's Read Uncommitted mode behaves like Read Committed." and "Read Committed is the default isolation level in PostgreSQL." Because every read is served from an MVCC snapshot of committed data, dirty reads are impossible in Postgres at any level — a floor the storage model gives you for free.

Read Committed — a fresh snapshot per statement

At the default level, each statement takes its own new snapshot at the moment it begins. — PostgreSQL Docs: "a SELECT query… sees a snapshot of the database as of the instant the query begins to run." The consequence you must hold onto: two statements in the same transaction can see different data, if someone else commits in between. — PostgreSQL Docs: "two successive SELECT commands can see different data, even though they are within a single transaction, if other transactions commit changes after the first SELECT starts and before the second SELECT starts."

Repeatable Read — one snapshot for the whole transaction

Move the snapshot earlier and hold it: Repeatable Read takes a single snapshot at the start of the transaction's first real statement and every statement uses that same one. — PostgreSQL Docs: "a query in a repeatable read transaction sees a snapshot as of the start of the first non-transaction-control statement in the transaction… successive SELECT commands within a single transaction see the same data." Same machinery as Read Committed — the only change is when the snapshot is taken. That one move buys repeatable reads and no phantoms: Postgres's Repeatable Read is full snapshot isolation.

ERROR: could not serialize access due to concurrent update

Serializable — snapshot isolation that also catches write skew

Snapshot isolation has one famous hole: write skew. Two transactions each read an overlapping set, each checks a rule that currently holds, and each writes based on what it read — individually fine, jointly impossible in any serial order. Repeatable Read permits it; both commit. — PostgreSQL Docs: "If either transaction were running at the Repeatable Read isolation level, both would be allowed to commit; but since there is no serial order of execution consistent with the result, using Serializable transactions will allow one transaction to commit and will roll the other back."

Serializable is Repeatable Read plus a watchdog. It runs the same snapshot machinery but also monitors read/write dependencies between live transactions (via predicate locking) and aborts one if the interleaving couldn't have happened serially. — PostgreSQL Docs: "This level emulates serial transaction execution… as if transactions had been executed one after another"; implemented via "Serializable Snapshot Isolation, which builds on Snapshot Isolation by adding checks for serialization anomalies." The abort you'll see:

ERROR: could not serialize access due to read/write dependencies among transactions

Same deal as RR — you must retry. In fact any serialization failure returns SQLSTATE 40001, so a single retry loop handles both RR and Serializable. — PostgreSQL Docs: "Applications using this level must be prepared to retry transactions due to serialization failures… which always return with an SQLSTATE value of '40001'."

Postgres vs MySQL — same words, different machines

Check yourself

From memory. Two items reach back to the storage pillar on purpose.

-- session A                        -- session B
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT val FROM t WHERE id = 1;        -- snapshot taken here; say val = 10
                                       UPDATE t SET val = 20 WHERE id = 1;  -- commits
SELECT val FROM t WHERE id = 1;        -- STILL 10 — snapshot is held
UPDATE t SET val = val + 1 WHERE id=1; -- ERROR: could not serialize access…