MySQL Isolation Levels: Understanding the Differences

1. Why isolation levels exist in the first place

The isolation level of a database defines how much parallel running transactions are allowed to see of each other while working on the same data at the same time. Without any isolation, every transaction would immediately see every change made by every other transaction, even before it was committed, leading to unpredictable and often wrong results. Full isolation, where transactions are completely invisible to each other, would be safe, but in practice so slow that parallel processing becomes barely feasible.

The SQL standard therefore defines four isolation levels as a trade-off between consistency and throughput: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. Each level prevents an additional class of anomalies, but costs more locking overhead and reduces the possible parallelism. MySQL implements all four levels in InnoDB, with one important difference compared to other database systems such as PostgreSQL or Oracle: the default isolation level is REPEATABLE READ, not READ COMMITTED.

This article walks through each of the four isolation levels concretely, with SQL examples for the respective anomalies they allow or prevent, and explains in detail how InnoDB technically implements its default REPEATABLE READ through a consistent snapshot.

2. READ UNCOMMITTED: dirty reads in detail

READ UNCOMMITTED is the weakest isolation level and allows so-called dirty reads: a transaction sees changes from another transaction before they were committed. If the other transaction is later rolled back, the reading transaction has already worked with data that never actually existed in the database. This behaviour is undesirable in almost all production use cases.

In practice this isolation level is almost never used deliberately, except for very specific monitoring or debugging queries where a rough overview of the data matters more than correctness, and where locks held by other transactions must never block under any circumstances. InnoDB implements READ UNCOMMITTED technically by skipping consistency checks through MVCC when reading, reading the current, possibly not yet committed version of a row directly instead.

-- Session A: sets isolation level and starts a transaction
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- reads 500, an uncommitted value

-- Session B (concurrently, not yet committed):
-- START TRANSACTION;
-- UPDATE accounts SET balance = 500 WHERE id = 1;
-- -- no COMMIT yet, or later a ROLLBACK

-- Session A saw the uncommitted 500 even though
-- session B might roll back afterwards

3. READ COMMITTED: behaviour and use cases

READ COMMITTED prevents dirty reads: a transaction only ever sees data that was already committed at the time of the respective read. The decisive difference from REPEATABLE READ lies in the frequency of the snapshot: under READ COMMITTED, InnoDB creates a new snapshot for every single SELECT statement, not just once at the start of the transaction. Running the same query twice within the same transaction, with another transaction committing a change in between, yields different results for both queries. This phenomenon is called a non-repeatable read.

This isolation level is the pragmatic choice in many web applications when reads should always reflect the latest committed state, for example in dashboards or reporting queries that read multiple times during a running transaction and expect current data each time. Another practical effect: READ COMMITTED noticeably reduces the number of gap locks set by InnoDB compared to REPEATABLE READ, which lowers the likelihood of unexpected lock waits.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

SELECT balance FROM accounts WHERE id = 1;  -- e.g. reads 500

-- Another session commits a change to this row in between

SELECT balance FROM accounts WHERE id = 1;  -- may now read a different value

COMMIT;

In practice this difference is particularly visible in reporting queries that compute several subtotals within the same transaction. Under READ COMMITTED, two consecutive aggregations over the same table can yield different results if another transaction commits data between the two queries, which can lead to seemingly inconsistent intermediate results in financial reports, even though each individual query is correct on its own.

4. REPEATABLE READ: the MySQL default

REPEATABLE READ is the default isolation level in InnoDB and differs fundamentally from the implementation in other database systems. While the SQL standard only guarantees for REPEATABLE READ that already read rows will not change within the same transaction, InnoDB's implementation goes further: the entire dataset is seen for the full duration of the transaction as it looked at the time of the first read operation. This prevents not only non-repeatable reads, but in practice also most phantom reads for simple SELECT queries.

The distinction between read and write access is important here: a plain SELECT without FOR UPDATE reads through the snapshot and therefore consistently sees the state from the start of the transaction. An UPDATE, DELETE or SELECT ... FOR UPDATE, on the other hand, always works with the latest committed state of the affected rows, independent of the snapshot. This exact difference frequently causes confusion in practice, when a developer expects an UPDATE to operate on the same old state as a previous SELECT within the same transaction.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

SELECT balance FROM accounts WHERE id = 1;  -- reads 500, snapshot is fixed now

-- Another session commits balance = 300 in between

SELECT balance FROM accounts WHERE id = 1;  -- still reads 500 (same snapshot)

UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- this UPDATE operates on the latest committed value (300), not on 500

SELECT balance FROM accounts WHERE id = 1;  -- reads 250, own change is visible

COMMIT;

5. SERIALIZABLE: strongest isolation, highest cost

SERIALIZABLE is the strictest isolation level and guarantees that the result of parallel transactions would be identical to the result of running all transactions strictly one after another. InnoDB implements this by implicitly converting every plain SELECT statement into a SELECT ... LOCK IN SHARE MODE, provided autocommit is disabled. This means even a simple read acquires shared locks, which prevents other transactions from writing to the same rows.

This strictness has a direct cost: parallelism drops noticeably, because reading and writing transactions block each other, leading to significantly more lock waits and potentially more deadlocks under heavy load. SERIALIZABLE fits cases where absolute correctness matters more than throughput, for example complex financial calculations with several interdependent read operations, where even the consistency offered by REPEATABLE READ is not enough.

In practice, SERIALIZABLE is rarely set permanently for an entire application, but instead activated deliberately for individual critical transactions, for example through SET TRANSACTION ISOLATION LEVEL SERIALIZABLE directly before the affected transaction. That way the rest of the application stays on REPEATABLE READ with higher parallelism, while only the few spots with particularly high consistency requirements benefit from the stricter guarantee.

6. Consistent reads under REPEATABLE READ: MVCC in detail

The mechanism behind the consistent-read behaviour of REPEATABLE READ is called multi-version concurrency control, or MVCC for short. Every row in InnoDB carries internal hidden metadata, including a transaction ID that indicates which transaction last modified the row. On the first read of a transaction, InnoDB creates a read view that records which transactions were already committed at that point and which are still active. All subsequent reads within the same transaction use this read view to decide which version of a row is visible.

If the latest version of a row was written by a transaction that, according to the read view, was not yet committed, InnoDB follows the chain in the undo log backwards until it finds a version that was already visible at the time the read view was created. This mechanism explains why reading transactions under this isolation level are never blocked by writing transactions: they simply read an older version stored in the undo log, instead of waiting for the current row.

This behaviour has an important practical side effect: a very long running transaction under REPEATABLE READ forces InnoDB to keep all undo log entries relevant to its read view, even if those rows have long since been modified by newer transactions. This lets the undo tablespace grow, and is one of the main reasons why long open transactions under REPEATABLE READ should be avoided.

7. Setting isolation level: session, global, transaction

MySQL allows the isolation level to be configured on three levels. Globally with SET GLOBAL TRANSACTION ISOLATION LEVEL ... changes the default for all new sessions, existing sessions remain unaffected. At the session level with SET SESSION TRANSACTION ISOLATION LEVEL ..., the value applies to all subsequent transactions on that connection. At the transaction level with SET TRANSACTION ISOLATION LEVEL ..., right before START TRANSACTION, the value applies only to the single next transaction.

In practice it is advisable not to change the isolation level globally for the entire database, but to set it deliberately per use case at the transaction level. A reporting query that needs READ COMMITTED should request that level explicitly for its own transaction, without affecting the default for other parts of the application that may rely on REPEATABLE READ behaviour.

-- Check the current session and global isolation level
SELECT @@transaction_isolation, @@global.transaction_isolation;

-- Set isolation level only for the very next transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- ... statements using READ COMMITTED semantics ...
COMMIT;

-- Back to the session default (usually REPEATABLE READ)
START TRANSACTION;
-- ... statements using REPEATABLE READ semantics again ...
COMMIT;

8. Practical impact: phantom reads and lost updates

A phantom read occurs when a transaction runs a range query twice and sees additional rows on the second run that another transaction inserted and committed in the meantime. Under READ COMMITTED this phenomenon happens regularly, because every query creates a new snapshot. Under REPEATABLE READ, InnoDB prevents phantom reads for simple SELECT queries through the fixed snapshot, while SELECT ... FOR UPDATE additionally relies on next-key locks that block new inserts within the locked range for as long as the transaction stays open.

A lost update occurs when two transactions read the same value, compute independently and write, with the second write overwriting the first without knowing about it. No isolation level alone reliably prevents this pattern if the application performs a plain read-modify-write without explicit locking. The reliable solution is either SELECT ... FOR UPDATE, which locks the read row until COMMIT, or optimistic locking through a version column that is checked and incremented on every UPDATE.

9. Isolation level and replication

The chosen isolation level also affects replication, especially in interplay with the binlog format. With statement-based replication (SBR), the actual SQL statement is transferred to the replicas, where it is executed again. Under READ COMMITTED, the same statement can lead to different results on primary and replica, because the visible dataset can differ between the two execution times. MySQL therefore recommends row-based replication (RBR) as a rule for READ COMMITTED, where the actually changed rows are transferred, not the statement itself.

REPEATABLE READ, on the other hand, works reliably with both SBR and RBR, since the snapshot mechanism guarantees deterministic results for the same transaction. In most modern MySQL installations RBR is the default setting anyway, which makes this difference a less frequent problem in practice, but when migrating older systems that use SBR, the chosen isolation level should be checked explicitly.

One additional practical note for environments with GTID-based replication: the isolation level itself is not synchronised through GTIDs, it remains a purely session or connection level setting. Anyone who deliberately wants to use READ COMMITTED on replicas for reporting workloads, while the primary stays on REPEATABLE READ, can do so safely as long as RBR is active, since both levels are configured independently of each other.

10. Summary

The four isolation levels in MySQL form a spectrum between throughput and consistency. READ UNCOMMITTED allows dirty reads and is almost never suitable for production use. READ COMMITTED creates a new snapshot on every query and fits cases that should always see the latest data. REPEATABLE READ, the default in InnoDB, keeps a fixed snapshot for the entire transaction and thereby prevents most anomalies for simple reads. SERIALIZABLE guarantees full correctness at the cost of noticeably reduced parallelism.

The decisive technical mechanism behind REPEATABLE READ is MVCC: InnoDB uses the undo log to give reading transactions older, consistent row versions without blocking them through parallel write operations. Anyone who deliberately picks the appropriate isolation level per use case, instead of blindly relying on the default, avoids both unnecessary lock waits and subtle data consistency bugs.

11. FAQ: Isolation Levels in MySQL