Chapter 18: Isolation Levels — Read Committed to Serializable

Full isolation (every transaction sees a perfect snapshot) is expensive. Databases offer levels of isolation — trade correctness for performance.

The Concurrency Problems

Problem	Description	Example
Dirty Read	Read uncommitted data from another transaction	See a transfer that gets rolled back
Non-Repeatable Read	Same query returns different results within one transaction	Read balance=1000, read again=900 (someone committed between)
Phantom Read	New rows appear that match a previous query's WHERE	COUNT(*) returns 10, then 11 (someone inserted)
Lost Update	Two transactions read-modify-write, one overwrites the other	Both read balance=1000, both write 900. Should be 800.

The Four Isolation Levels (SQL Standard)

Level	Dirty Read	Non-Repeatable	Phantom	Performance
Read Uncommitted	Possible	Possible	Possible	Fastest
Read Committed	Prevented	Possible	Possible	Fast (PostgreSQL default)
Repeatable Read	Prevented	Prevented	Possible*	Medium (MySQL default)
Serializable	Prevented	Prevented	Prevented	Slowest

Read Committed (PostgreSQL Default)

Each statement sees only data committed before that statement started. Different statements in the same transaction can see different snapshots.

-- Transaction A (Read Committed)
BEGIN;
SELECT balance FROM accounts WHERE id=1;  → 1000
-- Transaction B commits: UPDATE balance=900 WHERE id=1
SELECT balance FROM accounts WHERE id=1;  → 900 (sees B's commit!)
COMMIT;

Repeatable Read

The transaction sees a snapshot from its start. All queries see the same data, regardless of other commits.

-- Transaction A (Repeatable Read)
BEGIN;
SELECT balance FROM accounts WHERE id=1;  → 1000
-- Transaction B commits: UPDATE balance=900 WHERE id=1
SELECT balance FROM accounts WHERE id=1;  → 1000 (still sees old snapshot!)
COMMIT;

Serializable

Transactions behave as if they ran one after another (serial order). The database detects conflicts and aborts one transaction.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- If a conflict is detected:
-- ERROR: could not serialize access due to concurrent update
-- Your app must RETRY the transaction

Setting Isolation Level

-- Per transaction
BEGIN ISOLATION LEVEL REPEATABLE READ;

-- Per session
SET default_transaction_isolation = 'serializable';

-- Globally (postgresql.conf)
default_transaction_isolation = 'read committed'

Mark as Complete