ACID Isolation Levels Explained

Every database developer has written a transaction, but few truly understand what isolation level governs the space between your BEGIN and COMMIT. The SQL standard defines four isolation levels, each a tradeoff between correctness and throughput. Choosing the wrong one can produce phantom rows, lost updates, or lock contention that tanks your p99 latency.

The Isolation Spectrum

The SQL standard (ANSI/ISO 92) defines four levels, ordered from least to most restrictive:

PostgreSQL's REPEATABLE READ actually prevents phantoms in most cases due to its MVCC implementation, which is why the SQL standard describes it as "implementation-dependent" rather than guaranteed.

Read Uncommitted: The Myth

Read uncommitted is theoretically the most permissive level — a transaction can read rows modified by other uncommitted transactions. In practice, PostgreSQL implements it identically to read committed. SQL Server and Oracle do the same.

The reason is simple: dirty reads are almost never useful. If transaction A writes value X=10 and transaction B reads it, then A rolls back, B has acted on a value that never existed. You've created a bug that only manifests under concurrency.

-- PostgreSQL: read uncommitted behaves like read committed
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT balance FROM accounts WHERE id = 1;  -- Returns consistent value
-- Even if another session writes and hasn't committed
ROLLBACK;

Read Committed: The Default Workhorse

Most databases default to read committed. Each statement sees only data committed before the statement began — not the transaction. This means within a single transaction, two reads of the same row can return different values if another transaction commits between them.

This is the non-repeatable read phenomenon. It's acceptable for most OLTP workloads where you're reading and writing independent rows.

-- Session A
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;  -- Returns 1000
-- Session B updates and commits
UPDATE accounts SET balance = 1500 WHERE id = 1;
COMMIT;
SELECT balance FROM accounts WHERE id = 1;  -- Returns 1500!
-- The same query, same transaction, different result
COMMIT;

Repeatable Read: Snapshot Isolation

Repeatable read guarantees that if you read a row twice within a transaction, you get the same value. In PostgreSQL, this is achieved through snapshot isolation: the transaction takes a snapshot at its first query and all subsequent reads see that snapshot.

-- PostgreSQL implementation detail
-- Each transaction gets an xmin (the snapshot point)
-- All visibility checks compare row versions against this xmin
 
-- Transaction A (xmin = 100)
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM products WHERE category = 'electronics';
-- Returns 50 rows, all from snapshot at xmin=100
 
-- Transaction B inserts a new electronics product and commits
-- Transaction A runs the same query
SELECT * FROM products WHERE category = 'electronics';
-- Still returns 50 rows from the snapshot!
-- The new product is invisible

The key insight: snapshot isolation prevents non-repeatable reads but not write skew anomalies. Consider two doctors checking if there's at least one on-call colleague before signing off — both see one on-call, both sign off, and now nobody is on-call.

Serializable: The Gold Standard

Serializable prevents all anomalies by ensuring that the result of executing transactions concurrently is equivalent to some serial execution. PostgreSQL's implementation uses SSI (Serializable Snapshot Isolation), which is a significant innovation.

SSI doesn't take exclusive locks. Instead, it tracks read-write dependencies between transactions and aborts one if a cycle is detected. This means it only aborts transactions that would actually produce an anomaly, not all concurrent transactions.

-- PostgreSQL SSI detects write skew
-- Session A
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM doctors WHERE on_call = true;  -- Returns 1
UPDATE doctors SET on_call = false WHERE name = 'Alice';
COMMIT;  -- Succeeds if no conflict detected
 
-- Session B (concurrent)
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM doctors WHERE on_call = true;  -- Returns 1
UPDATE doctors SET on_call = false WHERE name = 'Bob';
COMMIT;  -- ERROR: could not serialize access due to read/write dependencies

Phantom Reads: The Subtle Trap

A phantom read occurs when a transaction runs the same query twice and gets a different set of rows because another transaction inserted or deleted rows between the queries. This is distinct from a non-repeatable read, which affects existing rows.

-- Phantom read demonstration
-- Session A
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM orders WHERE total > 1000;  -- Returns 10 rows
 
-- Session B inserts a new high-value order and commits
INSERT INTO orders (total, customer_id) VALUES (5000, 42);
COMMIT;
 
-- Session A
SELECT * FROM orders WHERE total > 1000;  -- Returns 11 rows in PostgreSQL!
-- In SQL standard REPEATABLE READ, this should still be 10
-- PostgreSQL's MVCC implementation may or may not show the phantom

Practical Guidance

The choice isn't academic. Every isolation level maps to real performance characteristics:

• Read committed: Best throughput, suitable for most web applications
• Repeatable read: Safe for read-heavy workloads requiring consistency
• Serializable: Required for financial, inventory, or scheduling systems

# Python psycopg2 example
import psycopg2
 
conn = psycopg2.connect("dbname=mydb")
conn.autocommit = False
 
# Set isolation level per-transaction
with conn.cursor() as cur:
    cur.execute("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ")
    cur.execute("SELECT * FROM accounts WHERE id = %s", (account_id,))
    # ... business logic ...
    conn.commit()

Synthesis

Isolation levels are the database's contract with your application about what anomalies are permissible. Read committed is the pragmatic default, repeatable read is the safe middle ground, and serializable is the correctness guarantee. Understanding these tradeoffs — and the MVCC mechanisms behind them — lets you choose the right level for each workload rather than defaulting to the safest (slowest) option.