If ACID explains why transactions matter, then isolation levels explain how they behave when multiple users hit the database at the same time.

Most developers know the names — READ COMMITTED, REPEATABLE READ, SERIALIZABLE — but they rarely understand what they actually protect you from. This article breaks them down in simple language with real examples from the Pagila Sample Database.

The Three Problems Isolation Levels Solve

Before explaining isolation levels, you need to know the “bad things” that can happen:

• Dirty reads — seeing another transaction’s uncommitted data
• Non-repeatable reads — rereading a row and getting different results
• Phantom reads — the same query returns more rows the second time

PostgreSQL already avoids dirty reads completely (thanks to MVCC). So really, you’re choosing how much protection you want from the other two problems.

Isolation Level 1: READ COMMITTED (The Default)

This is what PostgreSQL uses unless you change it. Every statement sees a fresh snapshot of the database.

The trade-off: simple and fast, but allows non-repeatable reads.

Example using Pagila’s inventory table:

-- Session A
BEGIN;
SELECT store_id FROM inventory WHERE inventory_id = 100;

-- Session B
UPDATE inventory SET store_id = 2 WHERE inventory_id = 100;
COMMIT;

-- Session A runs the SELECT again
SELECT store_id FROM inventory WHERE inventory_id = 100;
    

Session A sees different results each time. Nothing is “wrong” — that’s exactly what READ COMMITTED allows.

Isolation Level 2: REPEATABLE READ (Stronger Snapshot)

REPEATABLE READ freezes your view of the database for the entire transaction. That means all SELECTs see the same snapshot, even if other transactions commit changes.

Prevents: non-repeatable reads

Still allows: phantom reads

Example:

-- Session A
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT COUNT(*) FROM rental WHERE customer_id = 300;

-- Session B inserts a new rental
INSERT INTO rental (rental_date, inventory_id, customer_id, staff_id)
VALUES (NOW(), 1, 300, 1);
COMMIT;

-- Session A runs the SELECT again
SELECT COUNT(*) FROM rental WHERE customer_id = 300;
    

Session A sees the same count both times. It doesn’t see the new rental until the transaction ends.

Isolation Level 3: SERIALIZABLE (The “Make Everything Correct” Mode)

SERIALIZABLE is the strongest isolation level. It makes transactions behave as if they were run one-at-a-time in perfect order — even though they’re concurrent.

But it doesn’t use locks for everything. PostgreSQL uses predicate locking and conflict detection to keep things consistent.

Prevents: dirty reads, non-repeatable reads, phantom reads

Example: two sessions trying to insert overlapping rentals.

-- Session A
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Both sessions read the same snapshot
SELECT COUNT(*) FROM rental WHERE inventory_id = 500;

-- Session B (in another window)
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT COUNT(*) FROM rental WHERE inventory_id = 500;

-- Both sessions now decide to insert a rental for the same inventory_id

-- Session A
INSERT INTO rental (rental_date, inventory_id, customer_id, staff_id)
VALUES (NOW(), 500, 300, 1);

-- Session B
INSERT INTO rental (rental_date, inventory_id, customer_id, staff_id)
VALUES (NOW(), 500, 300, 1);

-- One of them commits successfully
COMMIT;  -- run this in Session A

-- The other fails at commit time
COMMIT;  -- run this in Session B

-- Result in Session B:
-- ERROR: could not serialize access due to read/write dependencies
-- HINT: The transaction might succeed if retried.

SERIALIZABLE doesn’t block the second transaction — it simply forces it to retry. This guarantees correctness but at the cost of occasional failures.

Quick Summary Table

Isolation Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads
Read Committed	❌ Blocked	✅ Allowed	✅ Allowed
Repeatable Read	❌ Blocked	❌ Blocked	✅ Allowed
Serializable	❌ Blocked	❌ Blocked	❌ Blocked

Which Isolation Level Should You Use?

Most applications should stay with READ COMMITTED. It’s fast and correct for 99% of workloads.

Use REPEATABLE READ when:

• You need stable results inside long-running transactions
• You’re running reports that must not change mid-query

Use SERIALIZABLE when:

• Correctness is more important than performance
• You can retry failed transactions in your application
• You want to prevent phantom reads entirely

Conclusion

Isolation levels let you choose the balance between performance and consistency guarantees. PostgreSQL’s MVCC makes most of the heavy lifting automatic, but understanding these levels helps you avoid subtle bugs in concurrent workloads.

If you understand these three levels, you already know more about database concurrency than most developers!