Two at once
T1 is a report that reads a balance. T2 is a transfer that changes it. Both run at the same time on the same row. The isolation level decides how much each one sees of the other’s work.
The SQL standard names four levels by the read problems, called phenomena, that each one still allows. PostgreSQL runs three: Read Committed (the default), Repeatable Read and Serializable. Asking for Read Uncommitted gives Read Committed.
Read Committed
Read Committed first. T1 begins and reads row 1. PostgreSQL keeps versions of every row; right now there is one, and T1 sees 100.
T2 sets the balance to 70 but has not committed. PostgreSQL does not overwrite the row: it adds a new version, 70, marked as T2’s unfinished work. The old version stays.
T1 reads again and still gets 100. Work that is not committed is invisible to everyone else, at every level in PostgreSQL. Seeing it would be a dirty read. And T1 did not have to wait for T2.
T2 commits. T1 runs the same query a third time and now gets 70. Read Committed takes a fresh snapshot (the committed work a statement may see) for every statement, so one transaction read two values: a nonrepeatable read.
Repeatable Read
Now T1 uses Repeatable Read. It reads 70, and that first read fixes its snapshot. T2 writes 40 and commits, yet T1 reads 70 again: its whole transaction sees one frozen moment.
T1 now tries to take 10 from a balance it thinks is 70. But T2 changed that row after T1’s snapshot. Writing over it would lose T2’s update, so PostgreSQL stops T1 with error 40001. T1 must roll back and retry.
Serializable
New rule: the two balances together must not drop below 0. Both transactions read the sum, 140, and each takes 100, from a different row. No row is written twice, so Repeatable Read would commit both: total −60.
Serializable also tracks what each transaction read. Each one read a row the other changed, so no one-at-a-time order fits both. T1 commits; T2’s COMMIT fails with 40001 and its version is thrown away. The total is 40: the rule holds.
The app retries T2 from the start. It reads the sum, now 40. Taking 100 would break the rule, so it stops. Code that uses Repeatable Read or Serializable must catch error 40001 and retry.
What happens
;; step 1/11 · two CREATE TABLE acct (id int PRIMARY KEY, bal int); INSERT INTO acct VALUES (1, 100), (2, 100); T1 a report: reads the balance of row 1 T2 a transfer: changes the balance of row 1 both are transactions: BEGIN ... COMMIT they run at the same time, in two sessions isolation level = how much of the other transaction’s work each one can see
T1 and T2 are two database sessions. In the Repeatable Read and Serializable parts each one starts a fresh transaction with BEGIN; the names stay the same to keep the picture simple. In Read Committed an UPDATE does not fail like this: it waits for the other writer, then works on the newest committed version of the row.