Transaction Isolation Levels
Concurrency Phenomena
When multiple transactions run at the same time, three distinct "anomalies" can occur:
| Phenomenon | Description | Example |
|---|---|---|
| Dirty Read | Reading uncommitted data from another transaction. | T1 updates age to 20. T2 reads 20. T1 rolls back. T2 now has "fake" data. |
| Non-repeatable Read | Re-reading the same row yields a different value. | T2 reads age=20. T1 updates age to 30 and commits. T2 reads age again and gets 30. |
| Phantom Read | Re-running the same range query yields a different number of rows. | T2 counts users > 18 (Result: 5). T1 inserts a new user (age=25) and commits. T2 counts again (Result: 6). |
The Four Isolation Levels
The SQL standard defines four levels of isolation. Each level up provides more consistency but reduces potential concurrency.
| Level | Dirty Reads | Non-repeatable | Phantom Reads | Mechanism |
|---|---|---|---|---|
| Read Uncommitted (RU) | ✅ Possible | ✅ Possible | ✅ Possible | Low/No locking. |
| Read Committed (RC) | ❌ Prevented | ✅ Possible | ✅ Possible | Read View created on every SELECT. |
| Repeatable Read (RR) | ❌ Prevented | ❌ Prevented | ❌* (Mostly) | Read View created on first SELECT and reused. |
| Serializable (S) | ❌ Prevented | ❌ Prevented | ❌ Prevented | Every SELECT is implicitly locked. |
InnoDB Default: MySQL uses Repeatable Read (RR) by default. Unlike some other databases, InnoDB’s implementation of RR also largely prevents Phantom Reads using MVCC and Gap Locks.
Implementation: RC vs. RR
The core difference lies in the Read View lifecycle within the MVCC system:
- Read Committed (RC): Each
SELECTstatement within a transaction creates its own fresh Read View. This means it can see any new commits made by other users between its first and second query. - Repeatable Read (RR): The Read View is created during the first
SELECTand is frozen for the duration of the transaction. Even if 10,000 other users commit changes, this transaction will only see the state of the database as it existed when it first started reading.
Deep Technical Insights
The RR "Phantom" Gap
While MVCC prevents phantoms in "Snapshot Reads" (Standard SELECT), you can still encounter them in "Current Reads" (e.g., UPDATE, SELECT FOR UPDATE).
Scenario: T1 counts users (gets 0). T2 inserts a user. T1 then runs UPDATE users SET name='X' WHERE id=5. Suddenly, T1 "finds" a row it didn't see before because UPDATE must act on the most current data, not a snapshot.
Performance Trade-offs
Most modern web applications (including those on AWS RDS or GCP Cloud SQL) stick with RR or RC. Serializable is rarely used in high-traffic sites because it turns the database into a bottleneck by forcing read-locks, effectively making the database single-user for overlapping records.