SQL Transaction Isolation Levels: From Read Committed to Serializable

Why Isolation Levels Exist

When multiple transactions run concurrently, they can interfere with each other. Isolation levels define how much interference is allowed. Stricter isolation means fewer surprises but lower throughput. The SQL standard defines four levels, from loosest to strictest.

The Three Anomalies

Before examining each level, understand the problems they address:

Dirty Read

Transaction A reads uncommitted data from Transaction B. If B rolls back, A has read data that never existed.

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

-- Transaction A reads the uncommitted change
SELECT balance FROM accounts WHERE id = 1;  -- Returns 0 (dirty!)

-- Transaction B rolls back
ROLLBACK;  -- balance is actually still 1000

Non-Repeatable Read

Transaction A reads a row, Transaction B modifies and commits it, then Transaction A reads the same row again and gets a different value.

-- Transaction A
SELECT balance FROM accounts WHERE id = 1;  -- Returns 1000

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

-- Transaction A reads again
SELECT balance FROM accounts WHERE id = 1;  -- Returns 500 (different!)

Phantom Read

Transaction A runs a query, Transaction B inserts/deletes rows that match A’s filter and commits, then Transaction A runs the same query and gets a different set of rows.

-- Transaction A
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- Returns 5

-- Transaction B inserts a new pending order and commits
INSERT INTO orders (status) VALUES ('pending');
COMMIT;

-- Transaction A runs the same query
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- Returns 6 (phantom!)

The Four Isolation Levels

Read Uncommitted

The loosest level. Allows dirty reads, non-repeatable reads, and phantom reads.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

In practice, PostgreSQL treats Read Uncommitted the same as Read Committed (it never allows dirty reads). MySQL’s InnoDB does support true Read Uncommitted.

Read Committed (PostgreSQL Default)

Each statement within a transaction sees only data committed before that statement started. Prevents dirty reads but allows non-repeatable reads and phantoms.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- Sees committed data
-- Another transaction commits a change here
SELECT balance FROM accounts WHERE id = 1;  -- May see different value
COMMIT;

This is the default in PostgreSQL and Oracle. It is a good general-purpose choice because it avoids dirty reads while keeping contention low.

Repeatable Read

A transaction sees a snapshot of the database taken at the start of the transaction. All reads within the transaction return the same data. Prevents dirty reads and non-repeatable reads.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- Returns 1000
-- Another transaction updates and commits
SELECT balance FROM accounts WHERE id = 1;  -- Still returns 1000
COMMIT;

In PostgreSQL, Repeatable Read also prevents phantom reads (it uses a full transaction-level snapshot). In MySQL InnoDB, phantoms are possible at this level.

If a concurrent transaction modifies a row that this transaction also tries to modify, PostgreSQL raises a serialization error:

-- Transaction A (Repeatable Read)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Transaction B (already committed an update to the same row)
-- Transaction A gets: ERROR: could not serialize access due to concurrent update

Serializable

The strictest level. Guarantees that the result of concurrent transactions is equivalent to running them one after another in some serial order.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN;
SELECT SUM(balance) FROM accounts;
-- Business logic based on the sum
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

PostgreSQL implements Serializable using Serializable Snapshot Isolation (SSI). It tracks read and write dependencies and aborts transactions that would create a cycle. The application must be prepared to retry:

-- Application pseudocode
LOOP
  BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  -- run queries
  COMMIT;
  EXIT LOOP;  -- success
EXCEPTION WHEN serialization_failure THEN
  -- retry the entire transaction
END LOOP;

Comparison Table

Level	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	Possible	Possible	Possible
Read Committed	No	Possible	Possible
Repeatable Read	No	No	Possible*
Serializable	No	No	No

*PostgreSQL prevents phantoms at Repeatable Read. The SQL standard allows them.

How PostgreSQL Implements Isolation (MVCC)

PostgreSQL uses Multi-Version Concurrency Control. Each row has hidden xmin and xmax columns tracking which transaction created and deleted it. When you UPDATE a row, PostgreSQL creates a new version rather than overwriting the old one.

At Read Committed, each statement gets a fresh snapshot. At Repeatable Read and Serializable, the snapshot is taken at the start of the first statement in the transaction.

This means readers never block writers and writers never block readers. Conflicts only arise when two transactions try to modify the same row.

Choosing the Right Level

Use Read Committed (default) when:

• Most queries are independent reads
• Your application can tolerate non-repeatable reads
• You want maximum throughput

Use Repeatable Read when:

• A transaction makes decisions based on data it read earlier
• Reports must be internally consistent
• You can handle occasional serialization errors

Use Serializable when:

• Correctness is more important than throughput
• You have complex invariants across multiple tables
• Your application already has retry logic

Setting Isolation Levels

-- Per transaction
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- ... queries ...
COMMIT;

-- Per session
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- System-wide default (postgresql.conf)
-- default_transaction_isolation = 'read committed'

Summary

Isolation levels control the trade-off between consistency and concurrency. Read Committed is the right default for most applications. Step up to Repeatable Read when you need consistent snapshots within a transaction, and to Serializable when you need full serializability guarantees. Always build retry logic when using Repeatable Read or Serializable, as the database will abort transactions to maintain correctness.