Isolation levels in MySQL
45 min · Advanced
Need help? Ask a tutor on WhatsApp (opens in a new tab)An isolation level decides how much a transaction sees of the work of other transactions running at the same time. Stronger levels prevent more anomalies but make sessions wait for each other more often. MySQL offers four levels, and you can choose one per session or per transaction.
REPEATABLE READ: the InnoDB default
REPEATABLE READ is InnoDB's default isolation level. Every plain SELECT in a transaction reads from one snapshot, a frozen view of the committed data, so reading again gives the same answer.
A report that reads the same row twice should see the same answer. First check which level your session uses.
SELECT @@transaction_isolation;+-------------------------+ | @@transaction_isolation | +-------------------------+ | REPEATABLE-READ | +-------------------------+
InnoDB builds the snapshot from older row versions that it keeps anyway for rollbacks. So readers never wait for writers, and writers never wait for readers. This design is called MVCC, multi-version concurrency control. In InnoDB it also stops phantom reads for plain SELECTs, although the SQL standard allows phantoms at this level.
The surprise: writes do not use the snapshot. An UPDATE, a DELETE or a locking read works on the newest committed row. So a transaction can read a stock of 8 and then update a row whose real stock is 5.
Example 1 · REPEATABLE READ: the second read matches the first
-- Step 1 · Session B: default levelSTART TRANSACTION;SELECT product_id, name, stock FROM products WHERE product_id = 3;
-- Step 2 · Session A: committed at onceUPDATE products SET stock = 5 WHERE product_id = 3;
-- Step 3 · Session BSELECT product_id, name, stock FROM products WHERE product_id = 3;COMMIT;SELECT product_id, name, stock FROM products WHERE product_id = 3;-- Step 1 · Session B +------------+-----------+-------+ | product_id | name | stock | +------------+-----------+-------+ | 3 | iPhone 15 | 8 | +------------+-----------+-------+ -- Step 3 · Session B +------------+-----------+-------+ | product_id | name | stock | +------------+-----------+-------+ | 3 | iPhone 15 | 8 | +------------+-----------+-------+ +------------+-----------+-------+ | product_id | name | stock | +------------+-----------+-------+ | 3 | iPhone 15 | 5 | +------------+-----------+-------+
What this code does
- Inside its transaction, B reads 8 twice, although A committed 5 in between: B reads its snapshot. This is the non-repeatable read from the last lesson, now prevented.
- After COMMIT, B's next SELECT is a new transaction with a new snapshot, so it shows 5.
Example 2 · Writes use the newest data, not the snapshot
-- Step 1 · Session BSTART TRANSACTION;SELECT product_id, name, stock FROM products WHERE product_id = 3;
-- Step 2 · Session AUPDATE products SET stock = 5 WHERE product_id = 3;
-- Step 3 · Session B: B still reads 8, then sells oneSELECT product_id, name, stock FROM products WHERE product_id = 3;UPDATE products SET stock = stock - 1 WHERE product_id = 3;SELECT product_id, name, stock FROM products WHERE product_id = 3;COMMIT;-- Step 1 · Session B +------------+-----------+-------+ | product_id | name | stock | +------------+-----------+-------+ | 3 | iPhone 15 | 8 | +------------+-----------+-------+ -- Step 3 · Session B +------------+-----------+-------+ | product_id | name | stock | +------------+-----------+-------+ | 3 | iPhone 15 | 8 | +------------+-----------+-------+ +------------+-----------+-------+ | product_id | name | stock | +------------+-----------+-------+ | 3 | iPhone 15 | 4 | +------------+-----------+-------+
What this code does
- B's snapshot says 8, but its UPDATE subtracts from the newest committed value, 5, so the result is 4, not 7. After its own write, B sees the row as it now is.
- This is why
stock = stock - 1is safe and "read 8, write 7" is not: the in-place update never uses a stale snapshot.
Predict the output
Predict the second read
The session runs at the default level. What does the second SELECT return?
READ COMMITTED and READ UNCOMMITTED
READ COMMITTED is an isolation level that gives every SELECT a fresh snapshot of the latest committed data. Dirty reads are impossible, but non-repeatable and phantom reads can happen.
A report may need the newest committed prices on every query. That is READ COMMITTED; READ UNCOMMITTED goes further and is rarely right.
Example 3 · Setting the level, and error 1568
SELECT @@transaction_isolation;SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT @@transaction_isolation;
START TRANSACTION;SELECT stock FROM products WHERE product_id = 3;SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- too late: a transaction is open+-------------------------+ | @@transaction_isolation | +-------------------------+ | REPEATABLE-READ | +-------------------------+ +-------------------------+ | @@transaction_isolation | +-------------------------+ | READ-COMMITTED | +-------------------------+ +-------+ | stock | +-------+ | 8 | +-------+ ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progress
What this code does
@@transaction_isolationshows the session's level with dashes:REPEATABLE-READ,READ-COMMITTED.SET SESSIONchanges it for every later transaction in this window.- The last line fails with error 1568 because the transaction has already started. Put
SET TRANSACTIONbeforeSTART TRANSACTION.
READ COMMITTED is the default in PostgreSQL, Oracle and SQL Server, and some MySQL applications choose it because updates lock fewer rows. READ UNCOMMITTED reads the newest version of every row, committed or not, so dirty reads happen; it is almost never the right choice.
Three ways to set a level: SET SESSION TRANSACTION ISOLATION LEVEL ... for the rest of the session, SET TRANSACTION ISOLATION LEVEL ... for the next transaction only, and SET GLOBAL TRANSACTION ISOLATION LEVEL ... for sessions that connect later (it needs an admin privilege). An application usually sets it on its connection, or in its database settings. You cannot change the level of an open transaction: SET TRANSACTION ... inside it fails with error 1568, and SET SESSION ... only takes effect from the next transaction.
SERIALIZABLE
SERIALIZABLE is the strictest isolation level: the result is as if the transactions had run one after another. InnoDB does it with locks.
Strict isolation costs waiting: a plain SELECT takes a shared lock (SELECT ... FOR SHARE), so nobody can change a row you have read.
Example 4 · SERIALIZABLE: a plain read blocks a writer
-- Step 1 · Session BSET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;START TRANSACTION;SELECT product_id, name, stock FROM products WHERE product_id = 3;
-- Step 2 · Session AUPDATE products SET stock = 5 WHERE product_id = 3;
-- Step 3 · Session BCOMMIT;
-- Step 4 · Session ASELECT product_id, name, stock FROM products WHERE product_id = 3;-- Step 1 · Session B +------------+-----------+-------+ | product_id | name | stock | +------------+-----------+-------+ | 3 | iPhone 15 | 8 | +------------+-----------+-------+ -- Step 2 · Session A (waited until step 3) -- Step 4 · Session A +------------+-----------+-------+ | product_id | name | stock | +------------+-----------+-------+ | 3 | iPhone 15 | 5 | +------------+-----------+-------+
What this code does
- B's plain SELECT took a shared lock on the row, so A's UPDATE in step 2 waits. At REPEATABLE READ it would not have waited.
- Once B commits, the lock is released and A's UPDATE finishes. If B never ended, A would give up after
innodb_lock_wait_timeoutseconds (50 by default).
The price is waiting, and deadlocks: two transactions that read the same row and then both update it block each other, and InnoDB cancels one with error 1213. With autocommit on, a lone SELECT is its own transaction and keeps no lock.
What each level prevents in InnoDB: READ UNCOMMITTED: none of the three read anomalies; READ COMMITTED: dirty reads; REPEATABLE READ: dirty and non-repeatable reads, and phantoms for plain SELECTs; SERIALIZABLE: all three, by locking what you read. Lost updates turn into waits or deadlocks.
Consistent snapshots and when they start
A consistent snapshot is the frozen view of committed data that a REPEATABLE READ transaction reads from. InnoDB takes it at the transaction's first read, not at START TRANSACTION.
Changes committed before your first read are visible. Changes committed after it are not.
Example 5 · The snapshot starts at the first read
-- Step 1 · Session ASTART TRANSACTION;
-- Step 2 · Session B: a price change, committed at onceUPDATE products SET price = 64900.00 WHERE product_id = 3;
-- Step 3 · Session A: A's first read: the snapshot is taken nowSELECT product_id, price FROM products WHERE product_id = 3;
-- Step 4 · Session BUPDATE products SET price = 59900.00 WHERE product_id = 3;
-- Step 5 · Session ASELECT product_id, price FROM products WHERE product_id = 3;COMMIT;-- Step 3 · Session A +------------+----------+ | product_id | price | +------------+----------+ | 3 | 64900.00 | +------------+----------+ -- Step 5 · Session A +------------+----------+ | product_id | price | +------------+----------+ | 3 | 64900.00 | +------------+----------+
What this code does
- A started its transaction before B's first change, yet A sees 64900.00: the snapshot was taken at A's first SELECT in step 3, after that change was committed.
- B's second change, 59900.00, came after the snapshot, so A still reads 64900.00 in step 5.
Example 6 · WITH CONSISTENT SNAPSHOT freezes the view at once
-- Step 1 · Session ASTART TRANSACTION WITH CONSISTENT SNAPSHOT;
-- Step 2 · Session BUPDATE products SET price = 64900.00 WHERE product_id = 3;
-- Step 3 · Session ASELECT product_id, price FROM products WHERE product_id = 3;COMMIT;SELECT product_id, price FROM products WHERE product_id = 3;-- Step 3 · Session A +------------+----------+ | product_id | price | +------------+----------+ | 3 | 69900.00 | +------------+----------+ +------------+----------+ | product_id | price | +------------+----------+ | 3 | 64900.00 | +------------+----------+
What this code does
- The snapshot was taken in step 1, before B's change, so A reads the old 69900.00 inside its transaction.
- After COMMIT, a new read sees 64900.00. Compare Example 5, where the same order of steps showed the new price.
Use START TRANSACTION WITH CONSISTENT SNAPSHOT when a report over many tables must show one exact moment; backup tools such as mysqldump --single-transaction rely on it.
At READ COMMITTED there is no transaction-wide snapshot: each SELECT takes its own, which is exactly why repeated reads can differ there.
Next step
Check your understanding
12 quick questions