Isolation Levels and Locks

Introduction

When many clients write at once, isolation levels define what one transaction can see of another's uncommitted work. Locks enforce those rules under InnoDB. This chapter explains dirty reads, non-repeatable reads, and phantoms, sets MySQL's four levels, and introduces row locks and gap locks at a conceptual level.

Prerequisites

Transactions Basics
Two mysql sessions (two terminals) helpful for demos

Phenomena

Phenomenon	Description
Dirty read	Read uncommitted data another transaction may rollback
Non-repeatable read	Same row read twice, different values (committed update between)
Phantom read	Same range query twice, different row count (new inserts)

Four Isolation Levels

Level	Dirty read	Non-repeatable	Phantom
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	No	Possible	Possible
REPEATABLE READ	No	No	Possible*
SERIALIZABLE	No	No	No

*InnoDB REPEATABLE READ often prevents phantoms via next-key locks—MySQL behavior stricter than standard minimum.

Check default:

sql

SELECT @@transaction_isolation;

Usually REPEATABLE-READ on InnoDB.

Set Session Isolation Level

sql

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Global (requires privilege; affects new sessions):

sql

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

Levels for next transaction only:

sql

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
-- ...
COMMIT;

Demo: Non-Repeatable Read (READ COMMITTED)

Session A:

sql

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE name = 'Alice';

Session B:

sql

UPDATE accounts SET balance = balance + 5 WHERE name = 'Alice';
COMMIT;

Session A again:

sql

SELECT balance FROM accounts WHERE name = 'Alice';
COMMIT;

Second read differs—non-repeatable read allowed at READ COMMITTED.

At REPEATABLE READ, first read snapshot usually repeats until transaction ends.

REPEATABLE READ Default

MySQL InnoDB default—good balance for most apps. ORMs often assume repeatable reads within one transaction.

sql

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM accounts WHERE name = 'Alice';
-- other session updates and commits
SELECT * FROM accounts WHERE name = 'Alice';
-- same result as first select in same transaction
COMMIT;

SERIALIZABLE

Strictest—reads lock rows; concurrent writes block more:

sql

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM accounts;
COMMIT;

Use sparingly—throughput drops.

Lock Types (Concept)

Lock	Abbrev	Who
Shared	S	Readers (`SELECT … LOCK IN SHARE MODE`)
Exclusive	X	Writers (`UPDATE`, `DELETE`, `SELECT … FOR UPDATE`)

Intention locks at table level—InnoDB knows row locks coming.

Row lock—locks index record.

Gap lock—locks gap between index values—blocks inserts in range.

Next-key lock = row + gap—implements REPEATABLE READ range protection.

SELECT FOR UPDATE

Explicit exclusive lock in transaction:

sql

START TRANSACTION;
 
SELECT id, balance FROM accounts WHERE name = 'Alice' FOR UPDATE;
 
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
 
COMMIT;

Second session blocking on same row until first commits—inventory checkout pattern.

Shared lock:

sql

SELECT COUNT(*) FROM accounts LOCK IN SHARE MODE;

Deadlock (Awareness)

Two transactions lock rows in opposite order—InnoDB kills one:

text

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

App retries transaction—see MySQL Troubleshooting.

Choosing a Level

Scenario	Suggestion
General OLTP web app	Default REPEATABLE READ
Report needing latest committed	READ COMMITTED (some teams set globally)
Financial strict serial	SERIALIZABLE or app-level locking
Never	READ UNCOMMITTED in production

FAQ

Isolation vs lock?

Isolation = visibility rules; locks = implementation mechanism.

Change global isolation?

Affects new connections—test before production change.

Phantom in REPEATABLE READ?

InnoDB next-key locks reduce phantoms on indexed searches.

MVCC?

InnoDB keeps row versions—consistent reads without locking readers in many cases.

Lock wait timeout?

sql

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

Default 50 seconds—then error.

Gap locks disabled?

READ COMMITTED reduces gap locking—trade-off more phantoms.