Home/Learn/MySQL/Transaction Isolation Levels

Transaction Isolation Levels

Advanced

Transactions

READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (default), SERIALIZABLE — each trades read anomalies (dirty read, non-repeatable read, phantom) against concurrency performance.

Overview

Transaction isolation levels define how much one transaction can see the uncommitted or in-progress changes of other concurrent transactions. The SQL standard defines four levels and three read anomalies. Choosing the right level is a trade-off: higher isolation prevents more anomalies but reduces concurrency (more locking or MVCC snapshot overhead). MySQL InnoDB defaults to REPEATABLE READ, which prevents dirty reads and non-repeatable reads using MVCC snapshots, and also prevents most phantom reads (unlike the SQL standard definition). Understanding isolation levels is a must-know topic for both system design and Java/DB interviews.

The Four Isolation Levels & Their Anomalies

READ UNCOMMITTED — A transaction can read uncommitted changes from other transactions (dirty reads). Rarely used; too dangerous for most applications.

READ COMMITTED — Only sees data committed before each query. Prevents dirty reads but allows non-repeatable reads (re-reading a row may return different data if another transaction committed between the two reads). Default in PostgreSQL and Oracle.

REPEATABLE READ — InnoDB default. A transaction sees a consistent snapshot of the DB as of its first read. The same row read twice always returns the same data. InnoDB also prevents phantom reads within the same snapshot window using gap locks.

SERIALIZABLE — Transactions execute as if they are the only ones running. Full isolation, maximum locking, lowest concurrency. Use only when absolute correctness is required.

SQL — Isolation Level Demo

-- Set isolation level for the current session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Or for next transaction only
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Read anomaly demonstration

-- ── Dirty Read (READ UNCOMMITTED) ──────────────────────────────
-- Transaction A: UPDATE orders SET total = 1000 WHERE id = 1  (not committed)
-- Transaction B: SELECT total FROM orders WHERE id = 1  → reads 1000 (dirty!)
-- Transaction A: ROLLBACK
-- Transaction B: acted on data that never existed

-- ── Non-Repeatable Read (READ COMMITTED) ───────────────────────
-- Transaction A: SELECT total FROM orders WHERE id = 1  → 500
-- Transaction B: UPDATE orders SET total = 800 WHERE id = 1; COMMIT;
-- Transaction A: SELECT total FROM orders WHERE id = 1  → 800 (changed!)
-- Same row, same query, different result within Transaction A

-- ── Phantom Read (REPEATABLE READ / SQL standard) ───────────────
-- Transaction A: SELECT COUNT(*) FROM orders WHERE status='PENDING'  → 10
-- Transaction B: INSERT INTO orders(...) VALUES(..., 'PENDING'); COMMIT;
-- Transaction A: SELECT COUNT(*) FROM orders WHERE status='PENDING'  → 11 (phantom!)
-- In MySQL InnoDB: gap locks prevent this phantom — still returns 10

MVCC — How InnoDB Implements REPEATABLE READ Without Locking

InnoDB uses Multi-Version Concurrency Control (MVCC) to give each transaction a consistent snapshot without holding read locks. When a transaction starts, InnoDB notes its transaction ID. Each row in InnoDB has hidden trx_id and roll_pointer columns. When a transaction reads a row, InnoDB uses the undo log to reconstruct the version of the row that existed at the transaction's start — other transactions' concurrent writes are invisible.

This means readers never block writers and writers never block readers in InnoDB — a massive concurrency advantage over lock-based isolation.

SQL — MVCC Snapshot Demo

-- Demonstrate REPEATABLE READ snapshot in InnoDB
-- Session A                          | Session B
START TRANSACTION;                    |
SELECT balance FROM wallet WHERE id=1; |  -- → 1000
                                      | START TRANSACTION;
                                      | UPDATE wallet SET balance=2000 WHERE id=1;
                                      | COMMIT;
SELECT balance FROM wallet WHERE id=1; |  -- → still 1000! (snapshot from tx start)
COMMIT;                               |
SELECT balance FROM wallet WHERE id=1; |  -- → 2000 (now sees committed changes)

-- READ COMMITTED sees each query's latest snapshot
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM wallet WHERE id=1;  -- → 1000
-- (Session B commits UPDATE to 2000 here)
SELECT balance FROM wallet WHERE id=1;  -- → 2000 (non-repeatable read!)
COMMIT;

Choosing the Right Isolation Level

Most applications work correctly with REPEATABLE READ (InnoDB default). Use READ COMMITTED for reporting queries that should see the latest data even inside a long-running transaction (avoids stale reads on analytics). Use SERIALIZABLE only for financial operations where phantom reads would cause incorrect totals — and combine with retry logic for deadlocks.

Java / SQL — Choosing Isolation

-- Set isolation in Spring Boot per transaction
@Transactional(isolation = Isolation.READ_COMMITTED)
public List<ReportRow> generateDailyReport() {
    // Sees each query's latest committed data — good for reports
    return reportRepo.queryAll();
}

@Transactional(isolation = Isolation.SERIALIZABLE)
public void transferFunds(Long from, Long to, BigDecimal amount) {
    // Full serialisation — prevents all anomalies, may deadlock under high load
    Account source = accountRepo.findById(from).orElseThrow();
    Account target = accountRepo.findById(to).orElseThrow();
    source.debit(amount);
    target.credit(amount);
}

-- Global default in my.cnf / my.ini
-- transaction-isolation = READ-COMMITTED   # common choice for write-heavy OLTP

Key Points to Remember

1Four levels: READ UNCOMMITTED < READ COMMITTED < REPEATABLE READ (InnoDB default) < SERIALIZABLE — higher level = fewer anomalies, less concurrency.
2Dirty read: reading uncommitted data. Non-repeatable read: re-reading a row gets a different value. Phantom read: a range query returns different rows on re-execution.
3InnoDB REPEATABLE READ uses MVCC snapshots (not locks) for reads — readers and writers do not block each other.
4READ COMMITTED is often used for reporting or OLTP: each query sees the latest committed data, but a transaction sees different data across queries.
5SERIALIZABLE prevents all anomalies using range locks; expect deadlocks under concurrency — always add retry logic.
6MySQL InnoDB REPEATABLE READ also prevents most phantom reads (using gap locks), unlike the SQL standard where phantoms are allowed at this level.

Interview Questions

What are the four SQL transaction isolation levels and what anomaly does each prevent?

MediumAmazon

What is a dirty read? Give a real-world example of why it is dangerous.

EasyTCS

How does InnoDB implement REPEATABLE READ without holding read locks?

HardUber

What is the difference between a non-repeatable read and a phantom read?

MediumFlipkart

When would you use SERIALIZABLE isolation and what are its trade-offs?

HardGoogle

Ask Aria about Transaction Isolation Levels

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

ACID Properties

MVCC (Multi-Version Concurrency Control)