CodePark/ questions / Isolation levels & anomalies
START PRACTICINGPRACTICE
Backend question · Databases & SQL › Isolation levels & anomalies

T1 and T2 run concurrently in PostgreSQL. What does T1's second SELECT return under Read Committed, and under Repeatable Read?

-- T1
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- 100

-- T2 now runs and commits:
-- BEGIN; UPDATE accounts SET balance = 50 WHERE id = 1; COMMIT;

-- T1 again
SELECT balance FROM accounts WHERE id = 1;  -- ?
50 under Read Committed, 100 under Repeatable Read

The answer is 50 under Read Committed and 100 under Repeatable Read.

Read Committed takes a new snapshot (a view of the committed data) for each statement. So the second SELECT sees T2's committed change. This is a non-repeatable read. Repeatable Read takes one snapshot at the first statement of the transaction and keeps using it. So T1 keeps seeing 100.

"Read Committed blocks until T2 commits; Repeatable Read raises a serialization error" is wrong. Under MVCC (each row keeps several versions), plain reads never wait for writers. A serialization error only happens if T1 tries to change the row that T2 changed.