Isolation level — how much one transaction sees another's uncommitted changes
Intermediate5 min read·eng-15-011
sqlinterview
Concept
Isolation level — a configurable setting that controls how much one transaction can see of another transaction's uncommitted changes. Higher isolation = more correctness, lower concurrency. Lower isolation = more concurrency, more anomalies.
The four anomalies isolation levels protect against:
- Dirty read: Transaction A reads data that Transaction B has written but not yet committed. If B rolls back, A read data that never existed.
- Non-repeatable read: Transaction A reads a row, Transaction B modifies and commits it, Transaction A reads again — different values.
- Phantom read: Transaction A queries a range (
WHERE age > 18), Transaction B inserts a new matching row and commits, Transaction A queries again — different row count. - Serialization anomaly: Transactions executed concurrently produce a result that couldn't happen if they ran one at a time.
The four standard isolation levels (SQL standard):
| Level | Dirty Read | Non-Repeatable | Phantom | Serialization |
|---|---|---|---|---|
| READ UNCOMMITTED | ✓ possible | ✓ possible | ✓ possible | ✓ possible |
| READ COMMITTED | ✗ prevented | ✓ possible | ✓ possible | ✓ possible |
| REPEATABLE READ | ✗ | ✗ prevented | ✓ possible | ✓ possible |
| SERIALIZABLE | ✗ | ✗ | ✗ prevented | ✗ prevented |
MySQL InnoDB default: REPEATABLE READ (with gap locks that also prevent phantoms in most cases). PostgreSQL default: READ COMMITTED.
In practice: Most apps run at READ COMMITTED or REPEATABLE READ. SERIALIZABLE is rarely used due to performance.
Code Example
php
<?php
// Setting isolation level for a specific transaction
\DB::statement('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
\DB::beginTransaction();
// ... operations
\DB::commit();
// Or globally:
\DB::statement('SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ');
// Demonstrating anomalies (conceptual — timing-dependent):
// DIRTY READ (at READ UNCOMMITTED — don't use in production!)
// Thread 1:
\DB::beginTransaction();
\DB::table('accounts')->where('id', 1)->update(['balance' => 9999]); // not committed!
// Thread 2 (at READ UNCOMMITTED reads 9999 — which may never be committed):
$balance = \DB::table('accounts')->where('id', 1)->value('balance'); // 9999!
// Thread 1 rolls back — Thread 2 saw a value that never existed
// NON-REPEATABLE READ (at READ COMMITTED):
// Thread 1:
\DB::beginTransaction();
$balance1 = \DB::table('accounts')->where('id', 1)->value('balance'); // 100
// Thread 2 commits: UPDATE accounts SET balance = 200 WHERE id = 1
$balance2 = \DB::table('accounts')->where('id', 1)->value('balance'); // 200!
// Thread 1 read the same row twice, got different values — non-repeatable read
// PREVENTING with REPEATABLE READ (MySQL InnoDB default):
\DB::statement('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ');
\DB::beginTransaction();
$balance1 = \DB::table('accounts')->where('id', 1)->value('balance'); // 100
// Thread 2 commits: UPDATE balance = 200
$balance2 = \DB::table('accounts')->where('id', 1)->value('balance'); // STILL 100 (snapshot)
\DB::commit();
// SERIALIZABLE — strongest isolation, uses locks
\DB::statement('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
\DB::transaction(function () {
$count = \DB::table('orders')->where('status', 'pending')->count();
// If another transaction tries to INSERT a pending order now → WAIT or deadlock
// No phantom rows possible
});
// In Laravel, set for a specific transaction:
\DB::transaction(function () {
\DB::statement('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ');
$data = \DB::table('critical_data')->get(); // consistent snapshot
// ... process
});