Skip to content

Isolation Levels

In one line

Isolation levels determine transaction visibility: Read Uncommitted, Read Committed, Repeatable Read, Serializable.

In simple words

Transaction isolation levels control how much one transaction's intermediate changes are visible to other concurrent transactions. Higher isolation = more consistency but less concurrency.

The four standard levels (from lowest to highest):

  1. Read Uncommitted — can read uncommitted changes (dirty reads). Rarely used.
  2. Read Committed — only see committed changes. No dirty reads. Default in PostgreSQL/Oracle.
  3. Repeatable Read — same query returns same result within a transaction. No dirty/non-repeatable reads. Default in MySQL.
  4. Serializable — transactions behave as if executed one at a time. Strongest isolation, lowest concurrency.

Each level prevents specific anomalies: dirty reads, non-repeatable reads, phantom reads, serialization anomalies. Higher levels prevent more anomalies but reduce concurrency.

Example

sql
-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
  -- Transaction A reads balance
  SELECT balance FROM accounts WHERE id = 1; -- 100
  -- Meanwhile, Transaction B commits: UPDATE accounts SET balance = 200 WHERE id = 1;
  -- With READ COMMITTED: re-read shows 200 (non-repeatable read)
  -- With REPEATABLE READ: re-read still shows 100 (consistent snapshot)
  SELECT balance FROM accounts WHERE id = 1;
COMMIT;

-- PostgreSQL: check current level
SHOW transaction_isolation; -- read committed

-- MySQL: set globally
SET GLOBAL transaction_isolation = 'REPEATABLE-READ';

Higher isolation prevents more anomalies. Read Committed allows non-repeatable reads; Repeatable Read prevents them. Serializable prevents all anomalies but is slowest.

Common confusions

  • Confused with: ACID

    The difference: ACID is a set of transaction properties (Atomicity, Consistency, Isolation, Durability). Isolation is the 'I' in ACID — it's one property. Isolation levels control how much isolation a transaction gets. ACID is the goal; isolation levels are the configuration.

Related terms

Related Resources

Related Lessons

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors

← Back to glossary