Isolation Levels in SQL: The Setting Most Developers Never Touch

The bug that only shows up under concurrency

Your order processing system tallies revenue for a billing cycle. It reads total revenue, calculates a tax amount, then writes the invoice. Under load, two concurrent invoice generation jobs read the same revenue total, both calculate independently, and both write. One overwrites the other. You've lost a billing record and neither job reported an error.

This is a non-repeatable read combined with a lost update — classic concurrency anomalies that isolation levels are designed to prevent. The default isolation level in most databases (READ COMMITTED) does not protect against them. Understanding what your isolation level guarantees — and what it doesn't — determines whether this class of bug can affect you.

The four isolation levels

SQL defines four isolation levels, each preventing a different class of anomaly:

READ UNCOMMITTED: a transaction can read rows modified by uncommitted transactions ("dirty reads"). Almost never correct. Not even fully implemented in PostgreSQL (reads committed data regardless).

READ COMMITTED: a transaction only sees rows committed before each statement executes. This is the PostgreSQL default and the MySQL InnoDB default. Prevents dirty reads, but allows non-repeatable reads and phantom reads.

REPEATABLE READ: a transaction sees a consistent snapshot of the database as of when the transaction started. Prevents dirty reads and non-repeatable reads. MySQL's InnoDB also prevents phantom reads at this level (via gap locks). PostgreSQL prevents phantom reads through MVCC snapshots.

SERIALIZABLE: the highest isolation level. Transactions appear to execute serially, even if they run concurrently. Prevents all anomalies. In PostgreSQL this is implemented via Serializable Snapshot Isolation (SSI), which detects and aborts transactions that would have produced non-serializable results.

What the anomalies actually look like

Dirty read (prevented by all levels ≥ READ COMMITTED):

T1: UPDATE accounts SET balance = 1000 WHERE id = 1
T2: SELECT balance FROM accounts WHERE id = 1  -- sees 1000
T1: ROLLBACK  -- T2 read data that never existed

Non-repeatable read (prevented by REPEATABLE READ and above):

T1: SELECT balance FROM accounts WHERE id = 1  -- reads 500
T2: UPDATE accounts SET balance = 1000 WHERE id = 1; COMMIT
T1: SELECT balance FROM accounts WHERE id = 1  -- reads 1000 (changed!)
-- T1 got different results for the same query within the same transaction

Phantom read (prevented by SERIALIZABLE):

T1: SELECT COUNT(*) FROM orders WHERE status = 'pending'  -- returns 10
T2: INSERT INTO orders (status) VALUES ('pending'); COMMIT
T1: SELECT COUNT(*) FROM orders WHERE status = 'pending'  -- returns 11
-- T1 sees a "phantom" row that didn't exist at transaction start

Write skew (only prevented by SERIALIZABLE):

-- Two doctors are on call. At least one must be on call at all times.
T1: SELECT COUNT(*) FROM doctors WHERE on_call = true  -- sees 2
T2: SELECT COUNT(*) FROM doctors WHERE on_call = true  -- sees 2
T1: UPDATE doctors SET on_call = false WHERE id = 1    -- goes off call (1 remaining)
T2: UPDATE doctors SET on_call = false WHERE id = 2    -- goes off call (0 remaining!)
-- Both transactions checked the constraint. Both passed. Both violated it.

Write skew is the subtlest anomaly and can only be prevented by SERIALIZABLE isolation or explicit SELECT FOR UPDATE to lock the rows being checked.

Choosing the right level

READ COMMITTED is correct when:

• Each operation is independent and doesn't depend on a consistent view across multiple reads within the same transaction
• You're doing simple CRUD without multi-step read-then-write logic

REPEATABLE READ is correct when:

• A transaction reads data multiple times and requires consistency between reads
• You're generating reports within a transaction that must see a consistent snapshot

SERIALIZABLE is correct when:

• Your transactions implement "check a condition, then write based on that condition" (the doctor on-call pattern, inventory deduction, balance checks)
• Correctness is more important than throughput

Setting isolation level per transaction in PostgreSQL:

BEGIN ISOLATION LEVEL REPEATABLE READ;
-- ... your transaction ...
COMMIT;

Or for a single session:

SET default_transaction_isolation = 'repeatable read';

The performance cost

Higher isolation levels cost more:

• REPEATABLE READ in PostgreSQL: no additional locking cost (MVCC snapshot is taken once at transaction start instead of per-statement). Very low overhead.
• SERIALIZABLE in PostgreSQL (SSI): tracks read/write dependencies to detect conflicts. Overhead is low for non-conflicting transactions, but serialization failures (transactions aborted due to detected conflicts) require retry logic.

In MySQL InnoDB, REPEATABLE READ uses gap locks and next-key locks, which can increase lock contention compared to READ COMMITTED. This is a meaningful consideration on high-write tables.

The practical default question

Most applications use READ COMMITTED by default and work around its limitations with SELECT FOR UPDATE on specific sensitive operations. This is a reasonable approach when you know which operations need stronger isolation. If you're building a financial system or any system with complex multi-step consistency invariants, evaluate SERIALIZABLE — and build retry logic, because serialization failures are possible and must be handled.