Isolation Levels — MySQL

MySQL Transaction Isolation Levels

Isolation levels control what data a transaction can see when other transactions are running concurrently. MySQL supports the four standard SQL isolation levels, each making a different trade-off between consistency guarantees and concurrency performance.

The Concurrency Problems Isolation Levels Address

Problem	Description	Example
Dirty read	Reading uncommitted data from another transaction	You read an order total that was updated but not yet committed — the other transaction rolls back
Non-repeatable read	Reading the same row twice yields different values	You read a price at $10, another transaction commits a $15 update, you read again and see $15
Phantom read	A range query returns different rows on two reads	You count 5 pending orders; another transaction inserts one; you count 6
Lost update	Two transactions read-modify-write the same value; one overwrites the other	Both sessions read stock=10, both subtract 1, both write 9 — net result should be 8

The Four Isolation Levels

Level	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Prevented	Possible	Possible
REPEATABLE READ (MySQL default)	Prevented	Prevented	Mostly prevented (gap locks)
SERIALIZABLE	Prevented	Prevented	Prevented

Setting the Isolation Level

SQL

-- Check current isolation level
SHOW VARIABLES LIKE 'transaction_isolation';

-- Set for the current session only
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Or shorter:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Set globally (affects all new sessions, not the current one)
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Set in my.cnf for persistence across restarts
-- [mysqld]
-- transaction-isolation = READ-COMMITTED

Note

Changing the isolation level affects only new transactions. A transaction already in progress uses the level that was active when it started.

READ UNCOMMITTED

The weakest isolation level. A transaction can read rows that have been modified by another transaction but not yet committed — called a dirty read. If the other transaction rolls back, you have read data that never officially existed.

SQL

-- Session A
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

START TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1;
-- Returns 1000

-- Session B (concurrently)
-- START TRANSACTION;
-- UPDATE accounts SET balance = 500 WHERE account_id = 1;
-- (not yet committed)

-- Session A reads again
SELECT balance FROM accounts WHERE account_id = 1;
-- Returns 500 (dirty read! Session B hasn't committed)

-- Session B rolls back
-- ROLLBACK;

-- Session A reads one more time
SELECT balance FROM accounts WHERE account_id = 1;
-- Returns 1000 again — but we already acted on 500 erroneously

Warning

READ UNCOMMITTED is almost never the right choice. Avoid it for any data that affects business decisions.

READ COMMITTED

A transaction only sees data that has been committed by other transactions. This prevents dirty reads, but the same row can return different values if another transaction commits a change between two reads within your transaction — a non-repeatable read.

SQL

-- Session A
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;
SELECT price FROM products WHERE product_id = 1;
-- Returns 29.99

-- Session B commits an update
-- UPDATE products SET price = 39.99 WHERE product_id = 1;
-- COMMIT;

-- Session A reads again — sees the newly committed value
SELECT price FROM products WHERE product_id = 1;
-- Returns 39.99 (non-repeatable read!)

COMMIT;

READ COMMITTED is the default in PostgreSQL and Oracle. Many high-traffic applications use it because it holds fewer locks than REPEATABLE READ, improving concurrency for write-heavy workloads.

REPEATABLE READ (MySQL Default)

InnoDB's default level. Once a transaction reads a row, it will see the same value for that row for the entire duration of the transaction — even if another transaction commits updates to it. InnoDB achieves this with MVCC (Multi-Version Concurrency Control): your transaction gets a consistent snapshot of the database at its start time.

SQL

-- Session A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION;
SELECT price FROM products WHERE product_id = 1;
-- Returns 29.99 — snapshot established

-- Session B commits an update
-- UPDATE products SET price = 39.99 WHERE product_id = 1;
-- COMMIT;

-- Session A reads again — still sees the snapshot value
SELECT price FROM products WHERE product_id = 1;
-- Returns 29.99 (repeatable read — snapshot is preserved)

COMMIT;  -- Snapshot released

Phantom reads and gap locks: InnoDB's REPEATABLE READ also largely prevents phantom reads through gap locks and next-key locks on range queries:

SQL

-- Session A
START TRANSACTION;
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Returns 5 — and InnoDB places a gap lock on the 'pending' range

-- Session B tries to insert a pending order
-- INSERT INTO orders (status) VALUES ('pending');
-- BLOCKED! Gap lock held by Session A prevents the insert

-- Session A reads again — still 5 (no phantom)
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- Returns 5

COMMIT;  -- Gap lock released; Session B can now insert

SERIALIZABLE

The strictest level. Every SELECT implicitly becomes a SELECT ... LOCK IN SHARE MODE, acquiring shared locks on all rows read. This completely prevents dirty reads, non-repeatable reads, and phantom reads — but serializes access so heavily that concurrent transactions queue up and throughput drops significantly.

SQL

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION;

-- This SELECT now acquires shared locks on all matching rows
SELECT * FROM orders WHERE status = 'pending';
-- Other transactions cannot modify these rows until we commit

-- Session B trying to insert a pending order will WAIT
-- until Session A commits or rolls back

COMMIT;  -- Shared locks released

Tip

SERIALIZABLE is appropriate for critical financial operations where you must guarantee that no concurrent transaction can interfere at all — for example, generating a regulatory report that must match exactly what was committed at the start of the query.

Isolation Level Comparison

Level	Use Case	Performance Impact
READ UNCOMMITTED	Approximate analytics where stale reads are acceptable	Highest concurrency, lowest consistency
READ COMMITTED	High-traffic OLTP; write-heavy workloads (PostgreSQL default)	Good concurrency, prevents dirty reads
REPEATABLE READ	General-purpose OLTP (MySQL default)	Balanced — MVCC keeps readers non-blocking
SERIALIZABLE	Financial audits, critical consistency requirements	Lowest concurrency, highest consistency

MVCC — How InnoDB Maintains Snapshots

InnoDB stores multiple versions of each row using hidden system columns:

DB_TRX_ID — the transaction ID that last modified this row version
DB_ROLL_PTR — a pointer into the undo log for the previous version of this row
DB_ROW_ID — a hidden auto-increment ID used when no primary key exists

When you start a transaction, InnoDB records the current system transaction ID as your read view high-watermark. When you read a row, InnoDB checks if that row's DB_TRX_ID is newer than your watermark. If so, it follows the DB_ROLL_PTR chain through the undo log until it finds a version that was committed before your transaction started. This means reads are always consistent and never block writers.

Current Reads vs Consistent Reads

There is an important distinction in InnoDB:

Read Type	What it sees	Example statements
Consistent read (snapshot)	Data as of the transaction snapshot — uses MVCC	Plain SELECT
Current read (locking)	The latest committed version of the row — acquires locks	SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, DELETE, INSERT

SQL

START TRANSACTION;

-- Consistent read (MVCC snapshot) — does NOT lock rows
SELECT balance FROM accounts WHERE account_id = 1;

-- Current read — locks the row for update, sees latest committed value
SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;

-- UPDATE always performs a current read before writing
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

COMMIT;

Practical: Choosing the Right Level

SQL

-- For a web application backend (most common choice):
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Fewer locks, higher concurrency, acceptable for most OLTP workloads

-- For a nightly report that must be internally consistent:
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
-- All reads in this transaction see the same snapshot
SELECT * FROM orders WHERE DATE(order_date) = CURDATE() - INTERVAL 1 DAY;
SELECT SUM(total_amount) FROM orders WHERE DATE(order_date) = CURDATE() - INTERVAL 1 DAY;
COMMIT;

-- For a critical balance check before a financial transaction:
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT SUM(balance) FROM accounts WHERE user_id = 42 FOR UPDATE;
-- Guaranteed no concurrent change can happen
UPDATE accounts SET balance = balance - 100 WHERE account_id = 5;
COMMIT;

Isolation Level and Binary Log Format

The combination of isolation level and binary log format matters for replication correctness:

Isolation Level	Recommended binlog_format	Reason
REPEATABLE READ	STATEMENT or ROW	STATEMENT-based replication is safe because reads are consistent
READ COMMITTED	ROW (required)	STATEMENT-based can produce different results on replica due to non-repeatable reads
SERIALIZABLE	ROW	ROW format captures exact changes regardless of isolation behavior

SQL

-- Check current binary log format
SHOW VARIABLES LIKE 'binlog_format';

-- Switch to ROW-based binary logging (recommended for READ COMMITTED)
SET GLOBAL binlog_format = 'ROW';

-- Verify the combination
SHOW VARIABLES LIKE 'transaction_isolation';
SHOW VARIABLES LIKE 'binlog_format';

Verifying Isolation with a Two-Session Test

SQL

-- Setup
CREATE TABLE iso_test (id INT PRIMARY KEY, val INT);
INSERT INTO iso_test VALUES (1, 100), (2, 200);

-- === REPEATABLE READ TEST ===
-- Session A:
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT val FROM iso_test WHERE id = 1;   -- 100

-- Session B (concurrent):
-- UPDATE iso_test SET val = 999 WHERE id = 1; COMMIT;

-- Session A reads again:
SELECT val FROM iso_test WHERE id = 1;   -- Still 100 (snapshot preserved)
COMMIT;

-- Session A starts new transaction and reads:
START TRANSACTION;
SELECT val FROM iso_test WHERE id = 1;   -- Now 999 (new snapshot after COMMIT)
COMMIT;

-- === READ COMMITTED TEST ===
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT val FROM iso_test WHERE id = 1;   -- 999

-- Session B commits another change:
-- UPDATE iso_test SET val = 500 WHERE id = 1; COMMIT;

-- Session A reads again (non-repeatable read):
SELECT val FROM iso_test WHERE id = 1;   -- Now 500 (sees the new commit)
COMMIT;

Best Practices

Leave REPEATABLE READ as the default for general applications — it is well-balanced
Switch to READ COMMITTED on write-heavy workloads to reduce gap lock contention and deadlocks
Never use READ UNCOMMITTED for any data that affects business logic or financial calculations
Use SERIALIZABLE only for tightly scoped critical-path transactions — not application-wide
When using READ COMMITTED with binary log format = STATEMENT, switch to ROW-based binary logging to avoid replication inconsistencies
Monitor isolation-related blocking via performance_schema.data_lock_waits
Document the isolation level used by your application in runbooks — it affects behavior in subtle ways that are hard to debug later