Isolation Levels

The four SQL isolation levels, why InnoDB defaults to REPEATABLE READ, and which read anomaly each one rules out.

SyntaxSET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL {REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE}

Isolation decides what your transaction sees of work happening in other transactions at the same time. SQL defines four levels; InnoDB implements all four, and its default is REPEATABLE READ.

Setting it

Three spellings do the same job, and you will meet all of them in other people's code:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET @@SESSION.transaction_isolation = 'READ-COMMITTED';  -- hyphens, not spaces
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;          -- next transaction only

The first two are equivalent. The third, with no GLOBAL or SESSION keyword, applies to the next single transaction and then reverts, and it is rejected inside an open transaction with error 1568.

What each level prevents

Level	Dirty read	Non-repeatable read	Phantom
READ UNCOMMITTED	possible	possible	possible
READ COMMITTED	no	possible	possible
REPEATABLE READ	no	no	no, in InnoDB
SERIALIZABLE	no	no	no

How REPEATABLE READ achieves it

Plain SELECT is a consistent, nonlocking read: InnoDB serves it from a snapshot established by the first such read in the transaction, sets no locks, and repeats the same answer all transaction long. Under READ COMMITTED each consistent read takes its own fresh snapshot instead. Phantoms are blocked in REPEATABLE READ because locking reads use next-key locks; gap locking is disabled under READ COMMITTED.

Example

-- Session A, default REPEATABLE READ.
START TRANSACTION;

SELECT SUM(amount) FROM payments WHERE order_id = 501;
-- 120.00  <- snapshot is established by THIS first read

-- Meanwhile session B runs, and commits:
--   INSERT INTO payments (order_id, amount) VALUES (501, 30.00);
--   COMMIT;

SELECT SUM(amount) FROM payments WHERE order_id = 501;
-- still 120.00  <- repeatable: same snapshot, B's row is invisible

COMMIT;

SELECT SUM(amount) FROM payments WHERE order_id = 501;
-- 150.00  <- new transaction, new snapshot

-- Under READ COMMITTED the second SELECT inside the transaction would
-- have returned 150.00, because each read takes a fresh snapshot.

When to use it

Making a multi-query report add up by reading everything from one snapshot.
Switching a queue-draining worker to READ COMMITTED to cut gap-lock contention.
Diagnosing why a SELECT in a long-running transaction never sees new rows.
Choosing whether to rely on the default or take explicit row locks for a read-then-write check.

More examples

The dirty read that READ UNCOMMITTED allows

A demonstration of why READ UNCOMMITTED is almost never the right choice. It is occasionally used for rough progress counters on a bulk load where a wrong number is harmless, but any figure that reaches a user or a ledger must not come from it. The reads are nonlocking, but so are plain SELECTs at the default level, so there is rarely anything to gain.

-- Session A
START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 7;
-- no COMMIT yet

-- Session B
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 7;
-- sees the reduced balance, which may never be committed
COMMIT;

-- Session A
ROLLBACK;   -- the value session B reported never existed

SERIALIZABLE turns plain SELECT into a locking read

Reach for SERIALIZABLE only when a read-then-write decision must be airtight and you would rather block than reason about it. The cost is real: every plain SELECT now takes locks, so throughput drops and lock wait timeouts appear. In most cases an explicit SELECT ... FOR UPDATE at the default level gives the same protection on just the rows that need it.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;   -- autocommit is now off for this transaction

-- InnoDB implicitly converts this to SELECT ... FOR SHARE, so it takes
-- shared next-key locks and blocks concurrent writers on the range.
SELECT seat_no FROM seats WHERE screening_id = 44 AND booked = 0;

INSERT INTO bookings (screening_id, seat_no) VALUES (44, '12C');
COMMIT;   -- locks released here

When the snapshot and DML disagree

The single most surprising consequence of MVCC, straight out of the manual. Consistent reads use the snapshot; UPDATE, DELETE and INSERT ... SELECT read the latest committed version so they do not overwrite newer data blindly. If your logic is read-then-decide, make the read a locking read (FOR UPDATE) so the snapshot and the write agree.

-- REPEATABLE READ, session A
START TRANSACTION;
SELECT COUNT(*) FROM t1 WHERE c1 = 'xyz';
-- 0 rows: no such rows in A's snapshot

-- Session B inserts several 'xyz' rows and commits.

DELETE FROM t1 WHERE c1 = 'xyz';
-- deletes B's just-committed rows, even though A cannot SELECT them
COMMIT;

Discussion

Be the first to comment on this lesson.