Isolation Levels — Explained with Examples

Database isolation levels control how transaction concurrency is managed, balancing consistency against performance across four defined levels.

Isolation Levels are part of the ACID model (the “I”). They define how transaction changes are visible to other concurrent transactions. The SQL standard defines four levels, each trading consistency for performance.

The Four Levels

Dirty Read — reading uncommitted changes from another transaction.

Non-Repeatable Read — reading the same row twice and getting different values (another transaction modified it between reads).

Phantom Read — running the same query twice and seeing different rows (another transaction inserted or deleted rows between runs).

Why Isolation Levels Matter

Higher isolation means more locking and blocking, reducing throughput. Lower isolation means faster performance but risk of anomalies. Choosing the right level depends on your application’s tolerance for inconsistent data.

Real-World Analogy

Read Uncommitted — a whiteboard anyone can read while it’s being erased. Read Committed — a published report that only shows final numbers. Repeatable Read — you get a printed snapshot; if numbers change, your copy doesn’t. Serializable — everyone forms a single line; only one person accesses the data at a time.

Example: Isolation Levels in PostgreSQL

-- Read Committed (default in PostgreSQL)
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;
-- Another transaction can modify balance between our reads
SELECT balance FROM accounts WHERE id = 1;  -- May differ!
COMMIT;

-- Serializable — strictest
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE id = 1;
-- No other transaction can modify this data until we commit
SELECT balance FROM accounts WHERE id = 1;  -- Guaranteed same
COMMIT;

Related Terms

ACID, Database Transaction, BASE