In this chapter
We'll uncover what one transaction can and can't see about another one still in progress — meeting dirty reads, non-repeatable reads, phantom reads, and the four standard isolation levels that trade safety for speed.
The Problem in Real Life
Mike asks a question Sarah hasn't had to answer before: while one order is still being processed — not committed yet — can anyone else see it?
Sarah realizes she doesn't actually know. If Mike pulls up the daily revenue report at the exact instant a large correction is mid-transaction, does he see the in-progress number, or only the finished one? The answer depends on a promise the database makes — and not every database makes the same one.
It depends on a promise the database makes — and not every database makes the same one.
Sarah
What One Transaction Can See About Another
"In progress" isn't the same as "invisible"
Whether an uncommitted change can be seen by anyone else at all depends entirely on the isolation level in effect.
A dirty read can act on something that was never true
If the transaction that made the change later rolls back, whoever read it early already acted on a number that turned out to be fiction.
The same query can answer differently within one transaction
A non-repeatable read or a phantom read means asking the exact same question twice, in the same transaction, and getting two different true answers.
Safety and speed pull in opposite directions
The strictest isolation level prevents every one of these problems, and is also the one most likely to make transactions wait on each other.
What Does Isolation Actually Promise?
Isolation — the third letter in ACID, and the one chapter 3 deliberately left alone — is a promise about how much of a transaction still in progress is visible to everyone else. Different databases, and different settings within the same database, make genuinely different promises here.
Three classic problems show up when isolation is too weak. A dirty read happens when one transaction reads a change another transaction hasn't committed yet — a change that might still get rolled back. A non-repeatable read happens when the same row, read twice inside one transaction, returns two different answers because someone else committed a change in between. A phantom read happens when the same WHERE query, run twice inside one transaction, returns a different set of rows because someone else inserted or deleted a matching row in between.
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible |
| Read Committed | Blocked | Possible | Possible |
| Repeatable Read | Blocked | Blocked | Possible |
| Serializable | Blocked | Blocked | Blocked |
Each stricter level blocks one more problem than the level above it, at the cost of more transactions waiting on each other.
A Dirty Read
Transaction A
UPDATE BalanceDue = 0 (not committed yet)
Transaction B
Reads BalanceDue = 0
Transaction A
ROLLBACK — the change never actually happens
Transaction B already acted on a number that was never true
Most databases let an application choose an isolation level for a transaction — each stricter level blocks one more of the problems above, at the cost of more waiting and less throughput.
-- Reference syntax used in databases like PostgreSQL and MySQL ---- SQLite doesn't offer configurable isolation levels the way these do.SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- allows dirty readsSET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- blocks dirty readsSET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- also blocks non-repeatable readsSET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- blocks phantom reads too
SQLite doesn't offer this choice directly. Its own whole-database locking model behaves close to Serializable by default — the strictest and safest of the four — without ever needing to be configured.
Notice the actual trade-off running through the whole table: stricter isolation isn't free. Read Uncommitted lets transactions run past each other with almost no waiting, at the cost of letting other transactions see things that might not even be true yet. Serializable makes the strongest promise of all — a transaction behaves as if it ran completely alone — at the cost of transactions sometimes waiting on each other that a weaker level would have let through.
GreenMart now understands exactly what a database promises, and doesn't promise, about transactions running at the same time. The next chapter turns to a completely different kind of growing pain: a schema starting to strain under its own success.
Key Takeaway
Isolation is a spectrum, not a single guarantee — the stricter the level, the fewer of dirty reads, non-repeatable reads, and phantom reads are possible, but the more transactions end up waiting on each other.
Why This Matters
Choosing an isolation level is a real, consequential decision in any system built on a database that offers the choice — too weak, and reports or balances can reflect data that was never actually true; too strict, and a busy system can slow down waiting on locks that a weaker (but still safe enough) level would never have needed.
GreenMart now understands exactly what a database promises — and doesn't promise — about transactions running at the same time. The next chapter turns to a completely different kind of growing pain: a schema starting to strain under its own success.