Transaction Isolation Levels
debt(d9/e5/b7/t7)
Closest to 'silent in production until users hit it' (d9). The term's detection_hints list only laravel-debugbar and mysql-slow-query-log, neither of which actually detect wrong isolation levels — they show query timing, not concurrency bugs. The code_pattern notes 'phantom reads or non-repeatable reads causing business logic errors' which only manifest under concurrent load. These bugs are nearly impossible to catch with tooling; they appear intermittently in production when multiple transactions race.
Closest to 'touches multiple files / significant refactor in one component' (e5). While the quick_fix suggests 'Use READ COMMITTED' as a simple configuration change, fixing isolation-level-related bugs in practice requires understanding which transactions need what guarantees, potentially restructuring transaction boundaries, and adding explicit locking where needed. It's not a one-line fix because you must audit all affected code paths.
Closest to 'strong gravitational pull' (b7). The term applies to web, cli, and queue-worker contexts — every database interaction is shaped by this choice. Wrong isolation levels create load-bearing assumptions throughout the codebase. Transaction boundaries, retry logic, and concurrent access patterns all depend on understanding the isolation semantics. Changing isolation levels mid-project requires auditing every transactional code path.
Closest to 'serious trap' (t7). The misconception explicitly states 'READ COMMITTED is always safe — it still allows non-repeatable reads.' Common_mistakes reinforce this: developers assume MySQL and PostgreSQL use the same defaults (they don't), or that READ UNCOMMITTED is acceptable. The behavior contradicts what developers expect from 'committed' reads, and the per-session/per-transaction scoping surprises those used to global settings.
Also Known As
TL;DR
Explanation
The four standard isolation levels from weakest to strongest: READ UNCOMMITTED (dirty reads allowed), READ COMMITTED (no dirty reads, default in PostgreSQL), REPEATABLE READ (no non-repeatable reads, default in MySQL InnoDB), SERIALIZABLE (full isolation, no phantom reads). Higher isolation prevents more anomalies but increases lock contention. Most applications work correctly with READ COMMITTED; SERIALIZABLE is reserved for financial operations requiring strict consistency.
Diagram
flowchart TD
subgraph Weakest
RU[READ UNCOMMITTED<br/>Dirty reads allowed]
end
subgraph Mid1
RC[READ COMMITTED<br/>No dirty reads<br/>PostgreSQL default]
end
subgraph Mid2
RR[REPEATABLE READ<br/>No non-repeatable reads<br/>MySQL default]
end
subgraph Strongest
SR[SERIALIZABLE<br/>No phantom reads<br/>Full isolation]
end
RU --> RC --> RR --> SR
SR -.->|higher isolation = more locking| PERF[Lower Concurrency]
style RU fill:#f85149,color:#fff
style SR fill:#238636,color:#fff
style RC fill:#d29922,color:#fff
style RR fill:#d29922,color:#fff
Common Misconception
Why It Matters
Common Mistakes
- Assuming MySQL and PostgreSQL use the same default — MySQL uses REPEATABLE READ, PostgreSQL uses READ COMMITTED.
- Using READ UNCOMMITTED in production — dirty reads return data that may be rolled back.
- SERIALIZABLE on every transaction — massive lock contention; use only where strictly required.
- Not understanding that isolation level is set per-session or per-transaction, not globally.
Code Examples
// READ UNCOMMITTED — sees uncommitted data from other transactions:
$pdo->exec('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED');
$pdo->beginTransaction();
$balance = $pdo->query('SELECT balance FROM accounts WHERE id = 1')->fetchColumn();
// $balance may reflect a transaction that will be rolled back — dirty read// SERIALIZABLE for financial operations:
$pdo->exec('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
$pdo->beginTransaction();
try {
$balance = $pdo->query('SELECT balance FROM accounts WHERE id = 1 FOR UPDATE')->fetchColumn();
if ($balance >= $amount) {
$pdo->exec('UPDATE accounts SET balance = balance - ' . $amount . ' WHERE id = 1');
}
$pdo->commit();
} catch (Exception $e) { $pdo->rollBack(); throw $e; }