Transaction Isolation Levels: The 4-Step Ladder in MySQL

Hi everyone 👋. I'm Hung Anh.

Picture this: transaction A is reading an account balance to decide whether a withdrawal is allowed, and at that exact moment transaction B subtracts money from the same account and commits. Will A's second SELECT see the old number or the new one? The slightly uncomfortable answer: it depends — on the isolation level you (or your database's default) picked.

Isolation is the I in ACID, and it isn't an on/off switch but a four-step ladder — each step trades how "clean" your reads are against how much concurrency the database allows. Understand this ladder and a whole class of "works sometimes" bugs at the application layer suddenly makes sense.

Today I'll cover: the three read anomalies, the four standard isolation levels, and how InnoDB implements them underneath with MVCC. Let's get started.

The 3 anomalies we're fighting

Every isolation level is defined by which of these three phenomena it blocks (or tolerates):

Dirty read — reading uncommitted data

A reads a value that B has written but not committed. If B rolls back, A has already acted on data that… never existed. This is the most serious anomaly.

Non-repeatable read — same row, different value

A reads a row, B updates that row and commits, A reads the same row again inside the same transaction — and gets a different value. Any "read to check, then act" logic breaks silently.

Phantom read — same query, extra rows

Unlike a non-repeatable read (an existing row changed), a phantom is about the result set changing: A counts 10 orders, B inserts one and commits, A counts again and gets 11 — that eleventh row is a "ghost" that appeared mid-transaction.

The four steps of the ladder

The SQL standard defines four levels, in increasing strictness — the higher you go, the more anomalies are blocked, and the more locking you pay for:

1. READ UNCOMMITTED

Blocks nothing — dirty reads included. A transaction sees other transactions' uncommitted changes. In practice there is almost no reason to use this level with InnoDB: you accept every risk and get back very little performance.

2. READ COMMITTED

You only ever read committed data — dirty reads are gone. But each SELECT sees the database as of the moment that statement runs, so two reads in one transaction can still disagree (non-repeatable reads and phantoms still happen).

This is the default in PostgreSQL, Oracle and SQL Server — and a popular choice for write-heavy systems, because it holds fewer locks than REPEATABLE READ.

3. REPEATABLE READ — MySQL's default

Every read in the transaction looks at one snapshot taken at the first read. Read the same thing a hundred times and you get the same answer — non-repeatable reads are gone. In InnoDB this snapshot also blocks most phantoms (explained in the MVCC section below).

4. SERIALIZABLE

The strictest level: results are equivalent to running the transactions one after another. InnoDB implements it by silently turning every plain SELECT into SELECT ... FOR SHARE — reads take locks too, so concurrency drops sharply and deadlocks become more likely. Reserve it for cases where correctness is absolutely critical and the load allows it.

How InnoDB pulls this off — MVCC

The most interesting part: at READ COMMITTED and REPEATABLE READ, InnoDB lets ordinary reads run without any locks thanks to MVCC (Multi-Version Concurrency Control):

• When a row is modified, InnoDB doesn't overwrite it in place — it keeps the old version in the undo log.
• Each transaction reads through a read view — a record of which transactions were open at snapshot time. When a row's version is "too new", InnoDB walks back through the undo log and returns the version that read view is allowed to see.
• The only difference between the two levels is when the read view is taken: READ COMMITTED takes a fresh one per statement; REPEATABLE READ takes one per transaction.

Same machinery, different snapshot timing — completely different behaviour. I find this one of InnoDB's most elegant designs.

For locking reads (SELECT ... FOR UPDATE, UPDATE, DELETE) at REPEATABLE READ, InnoDB additionally uses next-key locks — locking both the row and the gap before it — so no other transaction can insert a "ghost" into a range you've scanned. This is why InnoDB blocks phantoms already at REPEATABLE READ, something the SQL standard doesn't require.

Verify it yourself with two terminals

The fastest way to internalise this is to produce the anomaly with your own hands. Open two MySQL connections:

-- Terminal 1
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT balance FROM accounts WHERE id = 1;   -- → 100

-- Terminal 2
BEGIN;
UPDATE accounts SET balance = 90 WHERE id = 1;
COMMIT;

-- Terminal 1 (still inside the old transaction)
SELECT balance FROM accounts WHERE id = 1;   -- → 90 (non-repeatable read!)

Now switch Terminal 1 to REPEATABLE READ and replay the scenario: the second read still returns 100 — the snapshot preserves the world as of the start of the transaction, even though Terminal 2 committed 90 long ago.

Inspecting and changing the level:

SELECT @@transaction_isolation;                       -- current level

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;   -- this session
SET GLOBAL  TRANSACTION ISOLATION LEVEL READ COMMITTED;   -- new connections

Which level should your application use?

A few rules of thumb I've settled on:

• Stay on REPEATABLE READ (the default) unless you have a concrete reason to move — stable behaviour, lock-free reads, the most anomalies blocked at low cost.
• Consider READ COMMITTED for write-heavy systems, long transactions, or workloads suffering lock waits / deadlocks from gap locks — in exchange, your application must tolerate two reads disagreeing.
• Don't use isolation levels as a substitute for explicit locking: "read then decide to write" logic (stock checks, balance deductions…) belongs with SELECT ... FOR UPDATE or conditional UPDATEs — no snapshot, however pretty, stops two transactions from both deciding based on stale pictures.
• SERIALIZABLE is the last resort, once you've shown the lower levels genuinely aren't correct enough.

Conclusion

An isolation level is a trade-off dial: turn it up and your reads get cleaner while the database locks more; turn it down and concurrency improves while your application absorbs some read anomalies. MySQL's default position — REPEATABLE READ on top of MVCC — is an excellent equilibrium, and most of the time you shouldn't move it. What matters is knowing which step you're standing on, and which anomalies that step allows to reach your code.

See you in the next posts. If you found this useful, don't forget to share it and leave a comment below 👇.

References

• MySQL 8.0 Reference Manual — Transaction Isolation Levels
• MySQL 8.0 Reference Manual — InnoDB Multi-Versioning
• MySQL 8.0 Reference Manual — InnoDB Locking (next-key locks)
• Further reading: the MySQL section of xiaolincoding.com and the database series on blog.algomaster.io