Isolation_levels

Chapter 36: Isolation Levels

Controlling Transaction Visibility

---

36.1 Understanding Isolation Levels

Isolation levels control how transactions see each other’s changes.

                    PostgreSQL Isolation Levels
    ========================================================================

    ┌─────────────────────────────────────────────────────────────────────┐
    │                   Isolation Levels                                    │
    │                                                                      │
    │   READ UNCOMMITTED (not fully supported in PostgreSQL)              │
    │   ────────────────────────────────────                             │
    │   • Can see uncommitted changes from other transactions             │
    │   • Dirty reads possible                                            │
    │                                                                      │
    │   READ COMMITTED (default)                                          │
    │   ─────────────────────────────                                     │
    │   • Only see committed changes                                      │
    │   • Non-repeatable reads possible                                  │
    │   • PostgreSQL default                                             │
    │                                                                      │
    │   REPEATABLE READ                                                  │
    │   ─────────────────                                                │
    │   • Same query returns consistent results                          │
    │   • May see serialization failures                                 │
    │                                                                      │
    │   SERIALIZABLE                                                     │
    │   ────────────────                                                 │
    │   • Full isolation                                                 │
    │   • Transactions appear to run serially                            │
    │   • May fail with serialization failures                           │
    │                                                                      │
    └─────────────────────────────────────────────────────────────────────┘

---

36.2 Setting Isolation Levels

-- Set at transaction level
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Do work
COMMIT;

-- Or use full syntax
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Set for session
SET default_transaction_isolation = 'serializable';

-- Check current setting
SHOW transaction_isolation;
SELECT current_setting('transaction_isolation');

---

36.3 Understanding Concurrency Problems

-- Dirty Read (not possible in PostgreSQL)
-- Transaction A reads uncommitted changes from Transaction B

-- Non-repeatable Read
-- Transaction A reads, Transaction B updates, Transaction A reads again = different value

-- Phantom Read
-- Transaction A reads with WHERE clause, Transaction B inserts new row, Transaction A reads again = different rows

-- Serializable Anomaly
-- Two transactions both read and write, one must fail

---

36.4 Practical Examples

-- READ COMMITTED (default)
-- Session 1:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- Session 2 (will see uncommitted change or wait)
SELECT balance FROM accounts WHERE account_id = 1;
-- Will see either old or new value depending on commit status

-- REPEATABLE READ
-- Session 1:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT SUM(balance) FROM accounts;  -- 1000

-- Session 2:
BEGIN;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
COMMIT;

-- Session 1 (will see same sum as before)
SELECT SUM(balance) FROM accounts;  -- Still 1000 (old snapshot)
COMMIT;

-- Now will see new value
SELECT SUM(balance) FROM accounts;  -- 1500

---

Summary

Level	Dirty Read	Non-repeatable	Phantom
READ COMMITTED	✓	✓	✓
REPEATABLE READ	✓	✓	✗
SERIALIZABLE	✓	✗	✗

---

Next: Chapter 37: Locking & Concurrency Control