Isolation Levels

When multiple transactions run at the same time, they can interfere with each other. The isolation level controls how much one transaction can see of another transaction’s uncommitted or freshly committed changes.

Think of it like working in an office. Full isolation means everyone works in their own soundproof room. No isolation means everyone’s shouting over each other. SQL gives us four levels in between.

The Three Anomalies

Before we look at the levels, let’s understand what can go wrong:

Dirty Read

Transaction A reads data that Transaction B has written but NOT yet committed. If B rolls back, A just read data that never existed.

Non-Repeatable Read

Transaction A reads a row, Transaction B updates that row and commits, then A reads the same row again and gets a different value. Same query, different result.

Phantom Read

Transaction A reads a set of rows matching a condition, Transaction B inserts a new row that matches that condition and commits, then A runs the same query and gets an extra row it didn’t see before.

The Four Isolation Levels

Read Uncommitted

The loosest level. We can see other transactions’ uncommitted changes. Almost nobody uses this because dirty reads are dangerous.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Transaction A:
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- sees $1000

-- Transaction B (not committed yet):
BEGIN;
UPDATE accounts SET balance = 500 WHERE id = 1;

-- Transaction A reads again:
SELECT balance FROM accounts WHERE id = 1;  -- sees $500 (dirty read!)

-- Transaction B rolls back:
ROLLBACK;

-- Transaction A just made a decision based on data that never existed.

Note: PostgreSQL doesn’t actually support Read Uncommitted. Even if we set it, PostgreSQL uses Read Committed instead. It’s that dangerous.

Read Committed (PostgreSQL default)

Each statement sees only data that was committed before it started. No dirty reads. But if we run the same SELECT twice, we might get different results if another transaction committed in between.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Transaction A:
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- sees $1000

-- Transaction B commits:
UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT;

-- Transaction A reads again:
SELECT balance FROM accounts WHERE id = 1;  -- sees $500 (non-repeatable read)
COMMIT;

This is the default in PostgreSQL and is fine for most applications.

Repeatable Read (MySQL InnoDB default)

Once we read a row, we’ll get the same value for the rest of our transaction, even if another transaction commits changes to it.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Transaction A:
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- sees $1000

-- Transaction B commits:
UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT;

-- Transaction A reads again:
SELECT balance FROM accounts WHERE id = 1;  -- still sees $1000!
COMMIT;

But phantom reads are still possible — new rows matching our WHERE condition can appear.

Note: In PostgreSQL, Repeatable Read actually also prevents phantom reads (it uses snapshot isolation under the hood). In MySQL InnoDB, phantom reads are prevented by gap locks.

Serializable

The strictest level. Transactions behave as if they ran one after another, not concurrently. No anomalies of any kind. But it’s the slowest because the database has to detect and prevent conflicts.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- If two transactions conflict, one will be rolled back
-- with a serialization failure error.
-- Our application code needs to catch this and retry.

Setting Isolation Levels

-- PostgreSQL: set for the current transaction
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- ... queries ...
COMMIT;

-- PostgreSQL: set for the whole session
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- MySQL: set for the next transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- MySQL: set for the whole session
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Check current level (PostgreSQL)
SHOW transaction_isolation;

-- Check current level (MySQL)
SELECT @@transaction_isolation;

Which Level Should We Use?

• Read Committed — good default for most web applications. Each statement sees the latest committed data.
• Repeatable Read — good when we need a consistent snapshot within a transaction (reports, balance calculations).
• Serializable — when correctness matters more than performance (financial systems, inventory management). Must handle serialization failures in application code.
• Read Uncommitted — almost never. Just pretend it doesn’t exist.

In simple language, isolation levels are a trade-off between correctness and performance. Higher isolation = fewer surprises but more overhead. For most web apps, Read Committed is perfectly fine. Step up to Repeatable Read or Serializable only when we need the guarantees.