What Are Isolation Levels?

What are isolation levels?

Isolation is the "I" in ACID. Perfect isolation means concurrent transactions behave as if executed one at a time (serially); real systems offer weaker levels because full serializability historically cost too much throughput. SQL-92 defined the four standard levels by which anomalies they permit.

The standard's definitions proved incomplete. Berenson et al.'s 1995 paper "A Critique of ANSI SQL Isolation Levels" showed the anomaly list misses cases and described snapshot isolation — a level most MVCC databases actually implement — which prevents all three standard anomalies yet still permits write skew.

Implementations diverge from the standard in ways worth stating precisely. PostgreSQL accepts READ UNCOMMITTED syntactically but runs it as READ COMMITTED; its REPEATABLE READ is snapshot isolation (which also prevents phantom reads but allows write skew); its SERIALIZABLE, since version 9.1, is Serializable Snapshot Isolation (SSI), which detects dangerous patterns and aborts one transaction rather than blocking. MySQL's InnoDB defaults to REPEATABLE READ, while PostgreSQL, Oracle, and SQL Server default to READ COMMITTED.

How isolation levels work

Each level defines the snapshot a transaction reads from and the conflicts that force a wait or abort:

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT COUNT(*) FROM active_sessions WHERE user_id = 42;
-- decide based on the count, then act on it:
INSERT INTO active_sessions (user_id) VALUES (42);

COMMIT;  -- may fail with a serialization error; the application retries

Anomalies permitted at each SQL-standard level:

(* Per the standard. PostgreSQL's REPEATABLE READ — snapshot isolation — also prevents phantoms but permits write skew.)

A dirty read sees another transaction's uncommitted data. A non-repeatable read gets a different value when re-reading a row because another transaction committed an update in between. A phantom read gets a different set of rows when re-running a predicate query. Write skew occurs when two transactions read overlapping data, each writes different rows based on what it read, and the combined result violates an invariant neither violated alone.

Why isolation levels matter in real-time systems

The check-then-act example above is the shape of most automated decisions: read some state, apply a rule, commit an action. At READ COMMITTED — the default in most databases — two concurrent transactions can both read the same prior state and both pass a check that only one should pass. That is write skew, and it is not exotic: two simultaneous credit decisions against a shared limit, or two session grants against a per-user cap, hit it directly. Context under concurrency covers this class of failure in decision systems.

The second point engineers underweight: an isolation level is a guarantee within one database. The moment a decision reads context assembled from a cache, a replica, a search index, and a stream-processed aggregate, no isolation level spans those systems — each was populated at a different moment, and the transaction-level guarantee silently disappears. Isolation solves coherence inside the box; architecture determines whether it exists across boxes.

FAQ