Isolation Levels

Isolation Levels

Isolation Levels and the Anomalies They Prevent

The SQL standard names three read anomalies. A dirty read sees another transaction's uncommitted change; a non-repeatable read gets a different value when it reads a row again; a phantom is a row that appears in a repeated range query. Four isolation levels trade them against concurrency; SET SESSION TRANSACTION ISOLATION LEVEL ... picks one, and @@transaction_isolation shows it.

In this run Session B restocks one Databases title and adds another inside a transaction, while Session A sums the category three times:

No dirty read, then a non-repeatable read and a phantom, under READ COMMITTEDSQL

Session A> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Session A> START TRANSACTION;
Session A> SELECT COUNT(*), SUM(stock) FROM products WHERE category_id = 3\G
Session A: *************************** 1. row ***************************
Session A:   COUNT(*): 2
Session A: SUM(stock): 12
Session B> START TRANSACTION;
Session B> UPDATE products SET stock = 20 WHERE id = 2;
Session B: Query OK, 1 row affected
Session B> INSERT INTO products VALUES (9, 'BK-SQL-03', 'Query Tuning', 3, 38.00, 5, NULL);
Session B: Query OK, 1 row affected
Session A> SELECT COUNT(*), SUM(stock) FROM products WHERE category_id = 3\G
Session A: *************************** 1. row ***************************
Session A:   COUNT(*): 2
Session A: SUM(stock): 12
Session B> COMMIT;
Session A> SELECT COUNT(*), SUM(stock) FROM products WHERE category_id = 3\G
Session A: *************************** 1. row ***************************
Session A:   COUNT(*): 3
Session A: SUM(stock): 25
Session A> COMMIT;

A's second read ignored B's uncommitted work; its third saw B's commit, so the sum changed (a non-repeatable read) and a row appeared (a phantom). The same file at the other levels gave:

What Session A's reads returned at each isolation level on MySQL 9.7 524
Level	Second read (B uncommitted)	Third read (B committed)	Session B
READ UNCOMMITTED	3 rows, 25: dirty read	3 rows, 25	Not blocked
READ COMMITTED	2 rows, 12	3 rows, 25	Not blocked
REPEATABLE READ (default)	2 rows, 12	2 rows, 12	Not blocked
SERIALIZABLE	2 rows, 12	2 rows, 12	Waited for A's COMMIT

The standard permits phantoms under REPEATABLE READ, but InnoDB's snapshot hides them from plain reads, and gap locks keep them out of locking reads (Row and Gap Locks). SERIALIZABLE turns each plain SELECT inside a transaction into SELECT ... FOR SHARE, which made B wait. READ COMMITTED, the usual alternative, skips gap locks and so suffers fewer lock waits and deadlocks.

MENU