Isolation Levels and Read Phenomena

Concept

Isolation is the only ACID property you can dial up or down. The isolation level decides which concurrency anomalies your transaction may observe. Higher isolation means fewer anomalies and less concurrency.

Syntax

-- For the next transaction only
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- For the whole session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Check the current setting (MySQL 8 / MariaDB)
SELECT @@transaction_isolation;

The three phenomena

Dirty read

-- Session A                            -- Session B (READ UNCOMMITTED)
START TRANSACTION;                       START TRANSACTION;
UPDATE accounts
   SET balance = 0 WHERE id = 1;
                                         SELECT balance FROM accounts WHERE id = 1;
                                         -- reads 0: a value that was never committed
ROLLBACK;                                -- session B acted on data that never existed

Non repeatable read

-- Session A                            -- Session B (READ COMMITTED)
                                         START TRANSACTION;
                                         SELECT salary FROM employees WHERE id = 2;  -- 92000
UPDATE employees
   SET salary = 99000 WHERE id = 2;
COMMIT;
                                         SELECT salary FROM employees WHERE id = 2;  -- 99000
                                         -- same query, same transaction, different answer
                                         COMMIT;

Phantom read

-- Session A                            -- Session B
                                         START TRANSACTION;
                                         SELECT COUNT(*) FROM employees WHERE dept_id = 10;  -- 3
INSERT INTO employees (...)
VALUES (..., dept_id = 10, ...);
COMMIT;
                                         SELECT COUNT(*) FROM employees WHERE dept_id = 10;  -- 4
                                         -- a new row appeared inside the transaction
                                         COMMIT;

The levels

Defaults differ, and it matters

Porting an application from PostgreSQL to MySQL silently changes its isolation level. Code that relied on seeing other sessions' commits mid transaction will behave differently, and nothing will warn you.

Two InnoDB specifics worth knowing

• InnoDB's REPEATABLE READ prevents most phantoms, using next key locks that lock gaps between index entries as well as the rows. The SQL standard permits phantoms at this level; InnoDB is stricter.
• Consistent reads are snapshots, but writes are not. A plain SELECT reads the snapshot taken at the transaction's first read. An UPDATE in the same transaction reads the latest committed row - so an update can act on data your SELECT never showed you. This is the "write skew" surprise.

-- Force a locking read when the value you read must not change beneath you
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;       -- exclusive lock
SELECT balance FROM accounts WHERE id = 1 FOR SHARE;        -- shared lock (MySQL 8)
-- MySQL 5.7 and MariaDB spell the shared form LOCK IN SHARE MODE

Important rules

• The level applies to your transaction - it controls what you see, not what others do.
• Higher isolation costs concurrency: more locking, more waiting, more deadlocks.
• Changing the level mid transaction is not allowed; set it before START TRANSACTION.
• A read-modify-write sequence is not safe at any level without a locking read or an atomic update.
• SERIALIZABLE in InnoDB turns plain SELECTs into locking reads.

Common mistakes

• Assuming the default is the same on every product.
• Reading a balance, computing a new one in application code, then writing it back - without FOR UPDATE. Two sessions doing that lose one update.
• Raising the level to SERIALIZABLE to fix a bug, and creating deadlocks instead.
• Expecting REPEATABLE READ to make your own UPDATE see the snapshot.
• Using READ UNCOMMITTED for reporting and publishing numbers from rolled back transactions.

Best practices

• Stay on the product default unless you have a specific reason to move.
• Prefer a single atomic statement - UPDATE accounts SET balance = balance - 100 - over read-then-write in application code.
• Where read-then-write is unavoidable, use SELECT ... FOR UPDATE.
• Keep transactions short; isolation problems grow with transaction length.
• Test concurrency with two real sessions, not by reasoning alone.

Practice

1. Open two clients and reproduce a non repeatable read under READ COMMITTED.
2. Repeat it under REPEATABLE READ and explain the difference.
3. Rewrite a read-then-write balance update as one atomic statement.