How much should two concurrent transactions see of each other’s intermediate state? With perfect isolation, one transaction sees none of another’s changes until it commits. The result is identical to running every transaction one after another, which is the strongest correctness guarantee there is. But it also removes almost all concurrency. Processing one transaction at a time drops throughput sharply.
So an RDB offers isolation as levels, not a single on/off switch. When you first learn about isolation levels, you memorize the four of them as a grid of yes/no cells — and that grid never shows why each level allows or blocks a given phenomenon. Each level is a choice about which anomaly to tolerate, and a stronger level tolerates fewer anomalies at the cost of more concurrency. That trade-off is the subject here.
Anomalies
Four phenomena show up between concurrent transactions when isolation is loose.
Dirty Read. Transaction T1 modifies a value and hasn’t committed yet, and T2 reads it. If T1 rolls back, T2 has acted on a value that never existed.
sequenceDiagram
participant T1
participant DB
participant T2
T1->>DB: UPDATE balance = 500 (uncommitted)
T2->>DB: SELECT balance
DB-->>T2: 500 (uncommitted value)
T1->>DB: ROLLBACK
Note over T2: 500 is now invalid
Non-Repeatable Read. A transaction reads the same row twice and gets different values, because another transaction updated or deleted that row and committed in between. The assumption that data stays consistent within a single transaction breaks.
Phantom Read. A transaction queries a range, and between two reads another transaction inserts a row matching that range and commits. The second read returns a row that wasn’t there before. It resembles a Non-Repeatable Read, but the difference is that the number of rows in the result set changes rather than the value of one row.
Lost Update. Two transactions read the same value, each computes from it, and each writes back — so one update overwrites the other and disappears. Two transactions read stock 100, each sells one and writes 99, and stock ends at 99 even though two units were sold.
sequenceDiagram
participant T1
participant DB
participant T2
T1->>DB: SELECT stock → 100
T2->>DB: SELECT stock → 100
T1->>DB: UPDATE stock = 100 - 1 = 99
T2->>DB: UPDATE stock = 100 - 1 = 99
Note over DB: two sold, but stock is 99
Lost Update isn’t in the ANSI grid we’ll see next, but it’s the anomaly you hit most often in practice. The first three are about what a read sees; Lost Update is about writes overwriting each other, which makes it a slightly different kind of problem.
ANSI Isolation Levels
The ANSI standard defines isolation in four levels. Each one blocks one more of the first three anomalies (Dirty Read, Non-Repeatable Read, Phantom Read) than the level below it.
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
| Read Uncommitted | allowed | allowed | allowed |
| Read Committed | prevented | allowed | allowed |
| Repeatable Read | prevented | prevented | allowed |
| Serializable | prevented | prevented | prevented |
Read Uncommitted reads even uncommitted values. It allows all three phenomena, so it’s rarely used in practice.
Read Committed reads only committed values, blocking Dirty Read. It’s the default for Oracle and PostgreSQL. Because each query reads the latest committed state, though, two reads within one transaction can see changes committed in between, leaving Non-Repeatable Read.
Repeatable Read guarantees the same value when a row is read repeatedly within one transaction, blocking Non-Repeatable Read as well. By the standard’s definition, Phantom Read — a new row inserted into a range — still remains.
Serializable blocks every phenomenon. It guarantees the same result as running transactions sequentially, at the highest concurrency cost.
As the level rises, fewer anomalies are allowed and isolation grows stronger, but so does the cost of transactions waiting on each other. Rather than memorizing the yes/no cells, following which phenomenon each level blocks beyond the one below it makes the ordering clear.
MVCC
That’s the standard’s definition. MySQL’s InnoDB storage engine, however, doesn’t follow the grid as written, and MVCC (Multi-Version Concurrency Control) is central to that difference.
Instead of overwriting data, MVCC keeps multiple versions. When a transaction reads, it reads the snapshot for the point in time it should see. Even if another transaction changes and commits a value in between, my snapshot doesn’t see that change.
The benefit is that reads take no locks. A plain SELECT only reads a snapshot, so it doesn’t block, and isn’t blocked by, a write transaction modifying the same row. Reads and writes don’t contend, which keeps concurrency high while still providing consistent reads.
When InnoDB takes the snapshot depends on the isolation level. Under Read Committed, InnoDB takes a new snapshot on each query, so even within one transaction each read sees the latest commit at that moment. Under Repeatable Read, it takes the snapshot once at the transaction’s first read and every later plain read reuses it. That’s why multiple SELECTs in the same transaction see results consistent with each other.
InnoDB Repeatable Read
By the standard, Repeatable Read allows Phantom Read. InnoDB’s Repeatable Read, however, prevents Phantom Read for the most part. Combined with the fact that InnoDB’s default level is Repeatable Read, this is where the standard definition and actual behavior diverge.
How it prevents them splits by the kind of read.
A plain SELECT uses the snapshot read described above. It reuses the snapshot from the transaction’s first read, so even if another transaction inserts a new row and commits afterward, my snapshot doesn’t see it. On this path, Phantom Read disappears on its own.
A locking read like SELECT ... FOR UPDATE or FOR SHARE reads the latest data and takes locks rather than reading a snapshot. Here the snapshot can’t prevent a Phantom Read, so InnoDB uses a different mechanism. It places a next-key lock (a record lock plus a gap lock) over the scanned index range, blocking other transactions from inserting a new row into that range at all. With insertion blocked, no Phantom Read appears.
Putting the two paths together, InnoDB’s Repeatable Read blocks the Phantom Read that the standard allows, on both the plain-read and locking-read sides. The reason to say “for the most part” is that edge cases remain — for instance when snapshot reads and locking reads mix within one transaction. Apart from those cases, it behaves more strictly than the standard definition.
Serializable goes one step further. At this level, InnoDB implicitly promotes a plain SELECT to SELECT ... FOR SHARE when autocommit is off. Every read takes a lock, so transactions serialize against each other more strongly, and the concurrency cost rises accordingly.
Wrap-up
An isolation level is a policy choice. Perfect isolation gives the correctness of sequential execution but removes concurrency; weak isolation allows anomalies in exchange for throughput. The four ANSI levels are the choices between them for which phenomenon to tolerate.
The four standard levels are only a baseline — a real engine can behave more strictly. InnoDB uses MVCC to reduce read-write contention while blocking Phantom Read for the most part under Repeatable Read. So memorizing the grid alone misses the actual behavior; you have to check how your engine implements each level.
Where you place the isolation level is decided by the workload. When correctness comes first, as in payments, you lean toward stronger isolation and explicit locks; when reads dominate, you take concurrency with weaker isolation. How an application covers what the default levels don’t automatically prevent — anomalies like Lost Update — through optimistic and pessimistic locking is the subject of the next post.
References
- What RDB Transaction ACID Actually Guarantees — the starting point for the I (Isolation) trade-off this post makes concrete