Isolation levels.
A dial that trades how much overlapping transactions can see of each other for how fast they run.
When many transactions run at once, the database has to decide how much each one is allowed to see of the others' half-finished work. Isolation levels are that decision, as a dial. Turn it up and transactions are well-shielded but slower; turn it down and they go faster but can glimpse each other's mess.
It is like editing a shared document. The strictest setting acts as if everyone takes turns one at a time, so nobody ever sees a half-typed sentence. Looser settings let people work simultaneously for speed, accepting that you might occasionally read something that is about to change or vanish.
- 1
Two people edit the same document at once. The database has to decide how much each sees of the other’s half-typed work.
- Wait, where did that line go?2
Loosest setting: you read a sentence the other person hasn’t saved — then they delete it. That’s a dirty read.
- 3
Turn the dial up to read committed: you only ever see saved work, never anyone’s drafts.
- 4
Higher still — repeatable read: a line you’ve read stays put for the rest of your turn, even if someone edits it.
- That paragraph wasn’t there a second ago.5
But brand-new lines can still appear between two reads of the same section. Those are phantoms.
- 6
Top of the dial — serializable: it acts as if you each took the whole document in turn. Spotless, but everyone waits more.
The anomalies each level rules out
Each step up the dial forbids one more way concurrent transactions can confuse each other. A dirty read sees data another transaction has not committed (and may roll back). A non-repeatable read happens when reading the same row twice gives different answers because someone committed a change in between. A phantom is when a query run twice returns extra rows because new ones were inserted. Serializable forbids all of them by making concurrent transactions behave as if they ran in some single order.
Why not always pick the strictest
Stronger isolation means the database does more locking or version-checking to keep transactions apart, which reduces how many can run at once and can cause transactions to wait or be retried. So the right level depends on the operation: a bank balance transfer wants serializable safety; a feed of recent comments can happily tolerate a slightly stale read for speed. Most databases default to "read committed" as a practical middle ground.