MySQL Transaction Isolation Levels
Fast recall
Quick revision
Core answer
Assesses deep understanding of database concurrency.
Interviewers want to know whether the candidate can reason about data consistency vs.
performance trade-offs and configure Spring @Transactional correctly for their use case.
Interview-ready guide
Interview answer
MySQL uses Repeatable Read as its default isolation level — different from PostgreSQL which defaults to Read Committed. That difference matters when porting code between databases or diagnosing unexpected query behavior.
The four levels and what they prevent
- Read Uncommitted allows dirty reads — your transaction can see another transaction's uncommitted changes, which means you might read data that gets rolled back; almost never appropriate
- Read Committed prevents dirty reads — you only see committed data, but two reads of the same row in one transaction can return different values if another transaction commits between them (non-repeatable read)
- Repeatable Read (MySQL default) holds a consistent snapshot from the start of the transaction — the same row returns the same value no matter how many times you read it within one transaction; InnoDB's MVCC makes this efficient without actually locking reads
- Serializable adds predicate locks, preventing phantom rows — new rows inserted by other transactions won't appear in repeated range queries; the safest but highest contention
MySQL's Repeatable Read and gap locks
- Under Repeatable Read, InnoDB uses gap locks to prevent phantom inserts into the range you've read — which means
SELECT ... FOR UPDATEon a range prevents concurrent inserts into that range, even if no rows match - This is the source of many unexpected deadlocks in Spring Boot applications where two transactions both lock ranges and then try to insert into them
When to change the isolation level
- Read Committed is often sufficient for reporting queries — it avoids the gap lock overhead and is fine for analytics where phantom reads don't matter
- Serializable is rarely needed in web applications; most correctness requirements are covered by Repeatable Read plus application-level optimistic locking
MySQL Transaction Isolation Levels
Direct Answer
MySQL InnoDB supports four isolation levels: read uncommitted, read committed, repeatable read (default), and serializable. Higher isolation prevents more concurrency anomalies (dirty reads, non-repeatable reads, phantom reads) but reduces throughput. Spring Boot's @Transactional annotation lets you configure isolation per method.
Code Example
Transaction isolation trades consistency for concurrency. Running these scenarios in two MySQL sessions turns the terminology into muscle memory.
-- Setup (run once)
CREATE TABLE accounts (id INT PRIMARY KEY, balance INT) ENGINE=InnoDB;
INSERT INTO accounts VALUES (1, 1000), (2, 500);
-- =========== READ UNCOMMITTED — dirty reads allowed ===========
-- Session A -- Session B
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN; BEGIN;
UPDATE accounts SET balance = 0
WHERE id = 1; SELECT balance FROM accounts WHERE id = 1;
-- returns 0 ← DIRTY READ (A hasn't committed!)
ROLLBACK; -- B saw a value that never officially existed
-- =========== READ COMMITTED — no dirty, but non-repeatable ===========
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Session A -- Session B
BEGIN; BEGIN;
SELECT balance FROM accounts
WHERE id = 1; -- 1000 UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT;
SELECT balance FROM accounts
WHERE id = 1; -- 500 ← NON-REPEATABLE READ
COMMIT;
-- =========== REPEATABLE READ (InnoDB default) — snapshot per transaction ===========
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Session A -- Session B
BEGIN; BEGIN;
SELECT balance FROM accounts
WHERE id = 1; -- 1000 UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT;
SELECT balance FROM accounts
WHERE id = 1; -- STILL 1000 ✓ -- InnoDB uses MVCC snapshot from A's first read
COMMIT;
-- =========== SERIALIZABLE — full isolation via locks ===========
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT SUM(balance) FROM accounts; -- acquires shared locks on all scanned rows
-- Any concurrent UPDATE in another session blocks until this transaction commits.
COMMIT;
| Level | Dirty read | Non-repeatable read | Phantom read | Locking |
|---|---|---|---|---|
READ UNCOMMITTED | possible | possible | possible | minimal |
READ COMMITTED | never | possible | possible | short read locks |
REPEATABLE READ | never | never | rare (InnoDB prevents) | gap + next-key |
SERIALIZABLE | never | never | never | shared locks on every read |
- InnoDB's default is
REPEATABLE READand uses MVCC, so readers do not block writers even at this level — the higher cost you worry about on other databases does not apply. READ COMMITTEDgives higher concurrency and is the right default for most web applications with short transactions and idempotent retries — Oracle and PostgreSQL default here.- Phantom reads at
REPEATABLE READare prevented in InnoDB by gap locks and next-key locks. This is what letsSELECT ... FOR UPDATEreserve ranges. SERIALIZABLEis safe but slow — everySELECTbecomes a shared lock. Reserve for true invariant-critical paths (ledger posting) and keep the transaction short.
Common Mistakes & Pitfalls
Interviewers want to know whether the candidate can reason about data consistency vs.
Check your understanding
Answer these without looking back at the article.
- How does InnoDB implement REPEATABLE READ using MVCC without acquiring read locks?
- What is a phantom read and how does InnoDB's gap locking prevent it?
- How would you configure different isolation levels for read vs. write transactions in Spring Boot?
Related interview prep categories
2Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.