Transaction Isolation Levels
In this page:
SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE;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;- Assuming MySQL's default is
READ COMMITTED, when the InnoDB default isREPEATABLE READ. - Using
SET TRANSACTION ISOLATION LEVELinside a running transaction, or expecting it to last, when it applies only to the next transaction. - Using
READ UNCOMMITTEDin real code and reading data that may later be rolled back.
- A transaction groups statements so they succeed or fail together.
COMMITsaves changes, andROLLBACKundoes them.SAVEPOINTmarks a partial rollback point, and isolation levels control how transactions see each other's changes.
Chapter Quiz — Complete all 4 topics to unlock
0/4 topics done
Complete these topics first: