Aaliyah has $1000 across two accounts. She reads checking ($500), a transfer of $100 commits behind her back, she reads savings ($400) — her two reads sum to $900 and $100 has apparently vanished. Under PostgreSQL's default READ COMMITTED each statement takes a fresh snapshot; raise Session A to REPEATABLE READ and both reads come from one frozen snapshot: $1000, consistent.
Aaliyah has $1000 across two bank accounts, $500 in checking and $500 in savings. She opens a transaction and reads checking — $500. Meanwhile another transaction moves $100 from savings to checking and commits. She then reads savings — $400. Her two reads sum to $900: $100 has apparently vanished. Nothing is corrupt and nobody lost money — she read checking before the transfer and savings after it, and under PostgreSQL's default READ COMMITTED each statement gets a fresh snapshot, so her single transaction saw two different points in time. This is read skew, a non-repeatable read. Re-run the identical interleaving under REPEATABLE READ and both reads come from the snapshot taken at her transaction's start: $500 + $500 = $1000, internally consistent. You will reproduce both on a real PostgreSQL 16 server, in two hand-typed psql sessions.
Designing Data-Intensive Applications, 2nd ed. (Kleppmann & Riccomini, 2025), Chapter 8 — Transactions, §"Snapshot Isolation and Repeatable Read":
Aaliyah is not doing anything wrong here [...] the difference between the two queries is only a fraction of a second. This anomaly is called a nonrepeatable read or read skew.
The book names the anomaly and its fix: snapshot isolation (which PostgreSQL calls REPEATABLE READ) gives each transaction a consistent view of the database as of the moment it started. Here you trigger the anomaly under the default level, then close it by raising the isolation level one notch.
The surprise is entirely about when a read's snapshot is taken. These help; each line notes where it shows up.
BEGIN ISOLATION LEVEL … you type in Session A.psql sessions against the same server; code/read_skew.py is the capture harness that drives the identical two-session interleaving programmatically and prints the numbers quoted below.
One server, two accounts, two concurrent transactions. Only Session A's isolation level changes between the two runs.
SELECT sees everything committed as of the moment that statement began. So if a transaction reads checking, waits while another transaction commits a transfer, then reads savings, the second read sees the post-transfer world while the first saw the pre-transfer one. The two reads are consistent with no single moment — that gap is read skew.Both runs use the same tables and the same interleaving; the capture harness is one script, code/read_skew.py. The Steps you run by hand in two psql sessions.
The server is already running (a shared postgres:16 container). Give yourself your own database and two accounts, then open two psql sessions side by side — call them Session A (the reader) and Session B (the transfer).
# one-time: the shared server, if you are standing your own up
docker run -d --name ddia-ch08-pg -e POSTGRES_HOST_AUTH_METHOD=trust \
-p 5432:5432 postgres:16
# your own database + seed data
psql -h localhost -U postgres -d postgres \
-c "DROP DATABASE IF EXISTS ex_ch08_readskew; CREATE DATABASE ex_ch08_readskew;"
psql -h localhost -U postgres -d ex_ch08_readskew <<'SQL'
CREATE TABLE accounts (id int PRIMARY KEY, name text, balance int);
INSERT INTO accounts VALUES (1, 'checking', 500), (2, 'savings', 500);
SQL
# two sessions, one per terminal:
# Session A (reader): psql -h localhost -U postgres -d ex_ch08_readskew
# Session B (transfer): psql -h localhost -U postgres -d ex_ch08_readskewThe capture harness code/read_skew.py does all of this itself (drop + recreate + seed + both interleavings) and is what produced the numbers below:
uv run --python 3.12 --with "psycopg[binary]" python3 code/read_skew.pyDo not read ahead — make each prediction first.
Predict. In Session A, open a READ COMMITTED transaction and read checking. Then, in Session B, transfer $100 from savings to checking and commit. Back in Session A (still the same transaction), read savings. What does each read return, and what do Aaliyah's two reads sum to?
-- Session A: open the transaction, read checking
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1; -- checking-- Session B: move $100 savings -> checking, commit
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;-- Session A: same transaction, now read savings
SELECT balance FROM accounts WHERE id = 2; -- savings$500 + $400 = $900 — $100 has apparently vanished. Session A never left its transaction, yet its two reads disagree about a moment in time: it read checking before the transfer landed and savings after. Each SELECT under READ COMMITTED took a fresh snapshot, and Session B's commit slipped into the gap between them.
Predict. Session A is still inside the same READ COMMITTED transaction. Re-read checking. Will it show the $500 it read in Step 1, or the $600 that Session B committed?
-- Session A: still the same transaction, re-read checking
SELECT balance FROM accounts WHERE id = 1; -- checking again$600 — the committed value, not the $500 it saw seconds ago. This is the mechanism laid bare: under READ COMMITTED the same query in the same transaction returns a different answer because each statement re-snapshots. The first read wasn't wrong and the third isn't either; there simply is no single snapshot tying them together. ROLLBACK; when you're done — Session A only read.
Predict. Reset both accounts to $500. Now run the identical interleaving, but open Session A with ISOLATION LEVEL REPEATABLE READ. After Session B's transfer commits, what does Session A read for savings — and what do its two reads sum to?
-- (reset) any session:
UPDATE accounts SET balance = 500;
-- Session A: REPEATABLE READ this time
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1; -- checking -> ?
-- Session B: same transfer as before, commit
-- Session A: read savings, then re-read checking
SELECT balance FROM accounts WHERE id = 2; -- savings -> ?
SELECT balance FROM accounts WHERE id = 1; -- checking -> ?$500 + $500 = $1000, and checking is still $500 on the re-read. The transfer did commit — but Session A's snapshot was frozen at its first query, so Session B's later commit is invisible to it. Every read in the transaction agrees with a single instant: the start. The total is consistent again.
READ COMMITTED … 900 (read skew) / REPEATABLE READ … 1000 (consistent).Read skew is a timing anomaly, not a corruption. The database is always internally consistent — the transfer debits savings and credits checking as one atomic unit, so the true total is $1000 at every committed instant. What breaks is a reader that takes more than one snapshot inside a single logical operation. Under READ COMMITTED, PostgreSQL takes a new snapshot at the start of every statement: SELECT balance WHERE id=1 sees the world as of its own start, and the next SELECT balance WHERE id=2, milliseconds later, sees a different, newer world. If a transfer commits in that sub-millisecond gap, the first read reflects the pre-transfer database and the second the post-transfer one. Add them and you are summing two different points in time — $500 (before) + $400 (after) = $900 — which corresponds to no real state the accounts ever held. Step 2 makes the cause undeniable: re-reading checking in the same transaction returns $600, because that third statement snapshots again and this time the commit is already there.
REPEATABLE READ fixes it by taking one snapshot per transaction, fixed at the first query, and answering every subsequent read from it. This is snapshot isolation: the transaction reads a consistent, frozen view of all data committed as of that instant, and ignores anything committed later. Session B's transfer still happens and still commits — but it committed after Session A's snapshot, so it is simply not in Session A's view. Both reads therefore describe the same instant (the start), and $500 + $500 = $1000 every time. PostgreSQL implements this with MVCC: each row version is tagged with the transaction that created it, and a transaction's snapshot determines which versions are visible — READ COMMITTED recomputes visibility per statement, REPEATABLE READ computes it once.
SELECT sum(balance) FROM accounts always returns $1000 regardless of isolation level. The anomaly only appears when a transaction reads, pauses, and reads again, and a concurrent commit lands in the pause. That is why the fix is either "raise the isolation level so all your reads share one snapshot" or "do the read in a single statement." It also bounds the danger: purely single-statement reads (most OLTP point lookups) are immune; long-running, multi-read transactions (analytics, backups, consistency checks) are exactly the ones that need REPEATABLE READ.
SELECT sum(balance) FROM accounts — during a concurrent transfer. Predict whether it can ever return 900 (it cannot), and confirm read skew requires two separate reads.SELECT xmin, balance FROM accounts to each read and watch the xmin (creating transaction id) change on checking after the transfer under READ COMMITTED but stay fixed under REPEATABLE READ — MVCC visibility in the raw.Sources: DDIA 2e, Ch. 8, §"Snapshot Isolation and Repeatable Read", §"Weak Isolation Levels" · PostgreSQL 16 manual, ch. 13 "Concurrency Control".