Two accounts, checking and savings, hold 50 each. The rule is that together they always add up to 100. Transaction A is a report that reads both. Transaction B moves money from one to the other.
Two reads, one transaction, and a total that never existed. I thought I understood isolation levels until I tried to draw this.
Scroll to advance. Steps 1–7 are the idea, steps 8–12 are how Postgres does it. You can switch the isolation level at any step.
{{ warmFb }}
Two accounts, checking and savings, hold 50 each. The rule is that together they always add up to 100. Transaction A is a report that reads both. Transaction B moves money from one to the other.
A reads checking and gets 50. So far nothing is wrong.
Before A reads again, B takes 40 out of checking, puts it into savings and commits. At no committed moment did the total stop being 100. Before B it was 50 + 50. After B it's 10 + 90.
{{ fb3 }}
Now A reads savings. Under READ COMMITTED, each statement sees what had been committed when that statement started. B has committed, so A gets 90.
A adds up what it read: 50 + 90 = 140. Each number was true at the moment A read it. Put together, they describe a database that never existed. That is read skew: two reads in one transaction that come from two different moments.
READ COMMITTED protects each statement, not the transaction. The report ran two statements, so it got two views of the world. Nothing was dirty and no rule was broken.
That's also why it's hard to catch. It only shows up when a change lands between two reads, and in a test with one user that almost never happens.
It's the default level in Postgres. Most code runs like this without anyone having chosen it.
{{ fb6 }}
REPEATABLE READ takes one snapshot at A's first statement and keeps it until A ends. B still commits, and every other session sees the new balances. A doesn't. It reads savings as 50, and the total comes out as 100.
Switch between the levels above and watch A's second read.
You can stop here and you'll know what read skew is. The next five steps are how Postgres actually keeps a snapshot, and what it costs.
No lock was taken. B's update didn't overwrite the rows. It wrote new versions stamped with its transaction id, 102, in a field called xmin, and marked the old versions with xmax 102.
A's snapshot is a list of which transactions had committed when it was taken. 102 isn't on it. So for A, the new versions don't exist yet and the old ones were never replaced.
Not at BEGIN. Postgres takes it when the transaction runs its first query. If B committed between A's BEGIN and its first SELECT, A would see all of B's changes, and that's still correct: it just means A started after B.
In the playground below, move B's COMMIT above A's first read and see what happens to the total.
{{ fb9 }}
Read skew needs at least two statements. A single statement always runs on one snapshot, even under READ COMMITTED. Here A asks for SELECT sum(balance) in the middle of B's transfer and gets 100.
A lot of reports can be one query. When they are, the level doesn't matter.
Old versions have to stay on disk as long as some snapshot might still need them. A report that keeps a REPEATABLE READ transaction open for an hour stops vacuum from cleaning up anything newer than its snapshot, in every table of the database.
Tables grow, indexes fill with dead entries, and queries that have nothing to do with the report get slower.
To find who's holding things back: the oldest backend_xmin in pg_stat_activity.
A snapshot is easy for reading. Writing is where it gets awkward. If A, under REPEATABLE READ, tried to update checking after B had changed it, Postgres would refuse to let A overwrite a value it never saw.
A would get could not serialize access due to concurrent update, SQLSTATE 40001, and would have to start again. Part 4 is about that collision.
Every idea in the series, and every step where it appears. To review, pick a term, jump to a step, and come back.
| Anomaly | Read committed | Repeatable read | Serializable | What stops it |
|---|---|---|---|---|
| Dirty read | prevented | prevented | prevented | Uncommitted versions are invisible · part 2, step 8 |
| Read skewthis note | allowed | prevented | prevented | One snapshot per transaction · part 3, step 7 |
| Lost update (read, then write) | allowed | one fails · 40001 | one fails · 40001 | Refusing to overwrite a newer row · part 4, step 7 |
| Write skew | allowed | allowed | one fails · 40001 | Tracking what was read (SSI) · part 5, step 6 |
| Phantom read | allowed | prevented | prevented | One snapshot per transaction · part 6, step 5 |
| Write skew on new rows | allowed | allowed | one fails · 40001 | Predicate locks · part 6, step 9 |
{{ q.q }}
{{ q.fb }}
{{ q.model }}