Transaction Isolation Levels

Isolation is one of the four ACID properties, but it is not all-or-nothing — SQL defines four standard isolation levels that trade off consistency guarantees against performance. Stricter isolation prevents more anomalies but generally requires more locking or overhead, which can reduce concurrency (how many transactions can run at once without blocking each other).

Understanding isolation levels means understanding three specific anomalies they are designed to prevent: dirty reads, non-repeatable reads, and phantom reads.

Why it matters

When two users change the same data at the same moment, the result depends on how strictly the database keeps their transactions apart. Too little isolation gives wrong results, such as two people buying the last item; too much slows everything down. Choosing a level, and knowing your database's default, is part of building any system with concurrent users.

The Three Anomalies

Each anomaly describes a specific way that concurrent transactions can produce surprising, incorrect-looking results if isolation is too weak.

• Dirty read — a transaction reads data that another transaction has written but not yet committed. If that other transaction rolls back, the first transaction acted on data that never actually existed.
• Non-repeatable read — a transaction reads the same row twice and gets different values, because another transaction committed an UPDATE to that row in between the two reads.
• Phantom read — a transaction re-runs the same query twice and gets a different set of rows (extra or missing rows), because another transaction committed an INSERT or DELETE matching the query's condition in between.

The Four Standard Isolation Levels

From weakest to strongest, each level prevents more anomalies than the one before it, at the cost of more restrictive concurrency control.

• Read Uncommitted — the weakest level; allows dirty reads, non-repeatable reads, and phantom reads. Rarely used in practice; mainly useful for approximate reporting where perfect accuracy isn't required.
• Read Committed — prevents dirty reads (you only ever see committed data), but non-repeatable reads and phantom reads can still occur. This is the default isolation level in PostgreSQL and Oracle.
• Repeatable Read — prevents dirty reads and non-repeatable reads (a row you've already read will return the same values if read again), but phantom reads can still occur in the SQL standard's strict definition. This is the default isolation level in MySQL's InnoDB engine.
• Serializable — the strongest level; prevents dirty reads, non-repeatable reads, and phantom reads by making concurrent transactions behave as though they ran one after another in some serial order. It offers the strongest guarantees but has the highest risk of transactions being blocked or forced to retry.

Examples

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT COUNT(*) FROM seats WHERE flight_id = 42 AND is_booked = false;
-- Application decides there is a free seat and proceeds to book it

UPDATE seats SET is_booked = true
WHERE flight_id = 42 AND is_booked = false
LIMIT 1;

COMMIT;

count: 1
UPDATE 1
COMMIT
(Under SERIALIZABLE, a concurrent transaction attempting the same booking is forced to wait or retry, preventing two passengers from being assigned the same last seat.)

-- Transaction 1
BEGIN;
SELECT price FROM products WHERE id = 10;  -- returns 49.99

-- (Transaction 2, running concurrently, commits: UPDATE products SET price = 39.99 WHERE id = 10;)

SELECT price FROM products WHERE id = 10;  -- returns 39.99, a different value in the same transaction
COMMIT;

price: 49.99   -- first read
price: 39.99   -- second read, changed because another transaction committed in between

Exercise

This exercise needs a real database server and two connections, so it cannot run in the playground. In PostgreSQL, write a transaction that runs at the REPEATABLE READ level and reads the balance of account 1 twice, so that both reads are guaranteed to return the same value even if another session commits a change in between.

Expected result: both SELECT statements return the same balance; at the default READ COMMITTED level the second could differ.

-- TODO: begin a transaction

-- TODO: set the isolation level to REPEATABLE READ

-- TODO: read the balance of account 1

-- (another session updates and commits the balance here)

-- TODO: read the balance again, then commit

BEGIN;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT balance FROM accounts WHERE id = 1;

-- (another session updates and commits the balance here)

SELECT balance FROM accounts WHERE id = 1;

COMMIT;

Quick quiz

Interview questions