Transaction Isolation Levels
In this page:
Understanding Isolation Levels
Isolation levels determine how visible one transaction's in-progress changes are to other transactions running at the same time, controlling the tradeoff between data consistency and performance under concurrent access.
Example: Understanding Isolation Levels
SELECT @@transaction_isolation;READ UNCOMMITTED (Dirty Reads)
READ UNCOMMITTED is the loosest isolation level, allowing a transaction to see another transaction's changes before they've even been committed — a so-called dirty read that can expose data that later gets rolled back.
Example: READ UNCOMMITTED (Dirty Reads)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT * FROM accounts;
COMMIT;READ COMMITTED (Non-Repeatable Reads)
READ COMMITTED guarantees you'll never see uncommitted data from another transaction, but running the same query twice within one transaction can still return different results if another transaction commits in between.
Example: READ COMMITTED (Non-Repeatable Reads)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT * FROM accounts;
COMMIT;REPEATABLE READ (Default in MySQL)
REPEATABLE READ is MySQL's default isolation level, and it guarantees that if you read the same row twice inside one transaction, you'll get identical values both times, even if another transaction changes that row in the meantime.
Example: REPEATABLE READ (Default in MySQL)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;
SELECT balance FROM accounts WHERE id = 1;
COMMIT;SERIALIZABLE (Highest Isolation)
SERIALIZABLE is the strictest level available, effectively forcing concurrent transactions to behave as if they ran one after another rather than simultaneously — it eliminates concurrency anomalies entirely but can noticeably slow down a busy system.
Example: SERIALIZABLE (Highest Isolation)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM accounts;
COMMIT;Chapter Quiz — Complete all 4 topics to unlock
0/4 topics done
Complete these topics first: