Isolation levels — READ UNCOMMITTED through SERIALIZABLE
Concept
Isolation levels define how much one transaction can "see" the work of concurrent transactions. Higher isolation = more correctness, more locking/overhead. Lower isolation = more concurrency, more anomalies.
Anomalies (problems that isolation levels protect against):
- Dirty Read: Transaction A reads data written by uncommitted Transaction B. B later rolls back — A read phantom data.
- Non-Repeatable Read: Transaction A reads a row, Transaction B updates and commits, Transaction A reads the same row and gets a different value.
- Phantom Read: Transaction A queries rows matching a condition, Transaction B inserts/deletes rows matching that condition, Transaction A re-queries and gets different rows.
The four standard isolation levels (SQL standard, from lowest to highest):
| Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
| READ UNCOMMITTED | Possible | Possible | Possible |
| READ COMMITTED | Protected | Possible | Possible |
| REPEATABLE READ | Protected | Protected | Possible |
| SERIALIZABLE | Protected | Protected | Protected |
MySQL/InnoDB default: REPEATABLE READ. InnoDB also protects against phantom reads via gap locking even at REPEATABLE READ.
PostgreSQL default: READ COMMITTED.
Most applications: Use READ COMMITTED. Anomalies are rare in practice; the reduced overhead is worth it.
SERIALIZABLE: Transactions behave as if they ran one at a time. Correctness guaranteed. Slow for high concurrency — many transactions may be aborted and retried.
Code Example
-- Set isolation level for the current session (MySQL)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Set for the next transaction only
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
-- ... your queries ...
COMMIT;
-- PostgreSQL
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Demonstrate a dirty read (requires READ UNCOMMITTED)
-- Session A:
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
-- (Session B runs: UPDATE accounts SET balance = 9999 WHERE id = 1; — NOT COMMITTED)
SELECT balance FROM accounts WHERE id = 1; -- sees 9999 (dirty read!)
-- (Session B runs: ROLLBACK;)
-- The balance we read never really existed
-- Non-repeatable read demo (at READ COMMITTED)
-- Session A:
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- sees 500
-- (Session B: UPDATE accounts SET balance = 600 WHERE id = 1; COMMIT;)
SELECT balance FROM accounts WHERE id = 1; -- sees 600 (non-repeatable!)
COMMIT;
-- Phantom read demo (at REPEATABLE READ without gap locks)
-- Session A:
START TRANSACTION;
SELECT COUNT(*) FROM orders WHERE status = 'pending'; -- 5
-- (Session B: INSERT INTO orders (...) VALUES (..., 'pending'); COMMIT;)
SELECT COUNT(*) FROM orders WHERE status = 'pending'; -- 6 (phantom!)
COMMIT;<?php
// Laravel — set isolation level
DB::statement('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
DB::transaction(function () {
// Critical financial operation requiring full serializability
$account = Account::lockForUpdate()->findOrFail(1);
$account->decrement('balance', 100);
});
// Or configure globally in config/database.php for PostgreSQL
'pgsql' => [
'driver' => 'pgsql',
// ...
'options' => [
\PDO::ATTR_STRINGIFY_FETCHES => false,
],
// Run after connection: SET default_transaction_isolation TO 'read committed';
]