What isolation is for

Moving 40 from checking to savings takes two writes: take 40 out of one account, put 40 into the other. If the server dies between them, 40 has vanished.

A transaction wraps both writes so they count as one. After COMMIT both are saved. If anything fails before that, neither is.

The two accounts add up to 100 before the transfer and 100 after it. In between, for a moment, they don’t: after the first write checking holds 10 and savings still holds 50.

That in-between moment is fine as long as only the transfer itself can see it. Here a report, B, runs after the transfer has finished and reads 10 + 90.

Now the report runs first and the transfer second. The report reads 50 + 50. Either order gives a correct answer, because each transaction saw the other either completely done or not started.

Running transactions one after another is called a serial order. Every serial order is correct by definition.

Isolation is the rule that makes interleaving safe. Its strongest form, SERIALIZABLE, promises that whatever the database does inside, the result is the same as some serial order.

Switch the level to SERIALIZABLE. The report reads 100, as if it had run entirely before the transfer.

You can stop here and you’ll know what the rest of the series is about. The next five steps are how Postgres gives each transaction its own view.

The SQL standard defines four levels by listing what each one must prevent: dirty reads, non-repeatable reads, phantoms. The definitions were written with lock-based databases in mind.

In 1995, Berenson and his colleagues showed that those definitions miss things, lost updates and write skew among them. That’s why two databases can both offer REPEATABLE READ and behave differently. This series only describes Postgres.

Postgres doesn’t change a row in place when you update it. It writes a new version and marks the old one as replaced. Each version records which transaction created it, in a field called xmin, and which one replaced it, in xmax.

Here the transfer is transaction 101. After it commits, both versions of each row are still on disk.

To decide which versions it can see, a transaction uses a snapshot: the set of transactions that had committed at a given instant. A version is visible if its creator is in that set and its replacer isn’t.

At this moment the transfer, 101, hasn’t committed. The report’s snapshot doesn’t include it, so the report sees the old version of checking. It didn’t wait for anything to get it.

Postgres stores a snapshot compactly: the oldest id still running, the next id to be handed out, and the ids in between that were still in progress.

The level mostly decides when snapshots are taken. READ COMMITTED takes a new one for every statement. REPEATABLE READ and SERIALIZABLE take one at the first statement and keep it until the end.

Switch between them on this order. The same two reads give 140 or 100 depending only on that choice.

Readers never wait for writers, and writers never wait for readers. Two writers on the same row are different: the second one waits until the first commits or rolls back.

What the second writer does after that wait also depends on the level. That’s the source of lost updates, in part 4.