The three anomalies
Dirty read. Transaction A reads a row that B has changed but not committed. If B rolls back, A acted on data that never existed. Step 5 at READ UNCOMMITTED shows this: A reads 1500 from a change that is still draft.
Non-repeatable read. A reads a row, B updates it and commits, A reads the same row again and gets a different value. Step 7 at READ COMMITTED shows it: A reads 1000 and then 1500 inside one transaction, with no error and no warning.
Phantom read. A runs a query, B inserts a row matching its rule and commits, A runs the same query and gets an extra row. Step 9. The gap from a non-repeatable read is that no row A saw has changed — a new one has arrived.
The four levels
| Level | Dirty | Non-repeatable | Phantom |
|---|
| READ UNCOMMITTED | allowed | allowed | allowed |
| READ COMMITTED | prevented | allowed | allowed |
| REPEATABLE READ | prevented | prevented | allowed by the standard |
| SERIALIZABLE | prevented | prevented | prevented |
READ UNCOMMITTED is unused. PostgreSQL accepts the syntax and silently gives you READ COMMITTED instead, because its architecture has no way to expose uncommitted data.
READ COMMITTED is the default in PostgreSQL, Oracle and SQL Server. Each *statement* sees a fresh view of committed data. It's a sensible default and the outlier it permits is real: two queries in one transaction can disagree, so a report that reads the same table twice can give inside inconsistent totals.
REPEATABLE READ pins the transaction to a snapshot taken at its first read. All it sees is steady as of that moment, no matter what commits elsewhere. It's MySQL InnoDB's default.
SERIALIZABLE ensures the result is equal to some serial order. Modern setups achieve this without locking all, by tracking which transactions read which rows and aborting one when a genuine conflict is found.
The thing about SERIALIZABLE that surprises people
It doesn't make your code wait. It makes your code fail.
At step 10 under SERIALIZABLE, A may get a serialization failure instead of a good commit. That isn't a malfunction. It's how the ensure is delivered. The engine allowed both transactions to proceed optimistically, found afterwards that no serial order shows what happened, and rejected one.
So any code running at SERIALIZABLE must catch that error and retry the whole transaction. Code that assumes commit succeeds will fail intermittently under load, which is the worst kind of bug to diagnose.
Where the standard stops helping
The standard defines levels by which outliers are *permitted*, not which are blocked. An engine is free to prevent more than needed, and they do.
PostgreSQL's REPEATABLE READ uses a full snapshot and so blocks phantoms, which the standard doesn't need. MySQL's InnoDB blocks them at that level too, by a different mechanism (gap locks). So code that leans on phantoms being blocked at REPEATABLE READ works on both and isn't portable by the standard, and code that leans on phantoms *appearing* is wrong on both.
This is why "we use REPEATABLE READ" is an incomplete statement about a system's action. The engine matters.
Choosing
Most uses should stay on the engine's default and reach for a thing stronger where a exact rule demands it. The pattern that most often needs it's read-then-write: check a balance, then deduct from it. Between the check and the write, another transaction can change the balance, and no alone level below SERIALIZABLE stops that on its own.
The options are clear locking (SELECT ... FOR UPDATE) or letting the DB enforce the rule with a constraint. This Needs no alone thinking at all.
The lost update the levels do not stop
The read-then-write hazard is best seen as one math. Two transactions both withdraw 100 from an account holding 1,000, each reading before either writes:
| step | transaction A | transaction B |
|---|
| 1 | read balance = 1000 | read balance = 1000 |
| 2 | compute 1000 − 100 = 900 | |
| 3 | | compute 1000 − 100 = 900 |
| 4 | write 900 | |
| 5 | | write 900 |
Two withdrawals of 100 leave the balance at 900, not 800 — one withdrawal has vanished. Nothing here is a dirty or non-repeatable read, so READ COMMITTED and even REPEATABLE READ permit it: both transactions read committed data and each is inside steady. Only SERIALIZABLE stops it on its own, by aborting one at commit. Which is why this pattern needs either that level with a retry, an clear SELECT ... FOR UPDATE, or a single UPDATE ... SET balance = balance - 100 that never reads into the use at all. Thinking about alone is the hard way. Letting one statement do the math is the safe one.
READ UNCOMMITTED is often a no-op
One level on the dial often does nothing, and given or else is a portable source of confusion. READ UNCOMMITTED is meant to permit dirty reads — seeing another transaction's uncommitted changes — but a snapshot-based engine has no uncommitted version to show. PostgreSQL accepts the syntax and silently gives you READ COMMITTED instead:
| engine | SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED |
|---|
| PostgreSQL | accepted, runs as READ COMMITTED |
| MySQL InnoDB | honoured; dirty reads are possible |
So the same statement means two different things on two DBs, and a "speed" alone level chosen on one has no effect on the other. This is a given case of the general rule already stated. The standard names the outliers a level *permits*, and an engine may prevent more — and it's why the level a system runs at is a fact about the engine as much as about the SET statement.
Questions people ask about Isolation Levels
What are the four standard isolation levels? READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. Each one blocks more anomalies than the one before.
What is a dirty read? Reading data another transaction has written but not yet committed. If that transaction rolls back, you acted on data that never existed.
What is the difference between a non-repeatable read and a phantom? A non-repeatable read sees an existing row change between two reads. A phantom sees new rows appear that match your filter.
Which isolation level should I use by default? READ COMMITTED is the default in PostgreSQL and many others, and it is fine for most work. Raise it only where a specific anomaly would hurt.
Does SERIALIZABLE mean transactions run one at a time? No. It means the result is the same as if they had, but the database may run them together and abort one with a serialization error. Your code must retry.
Do isolation levels prevent lost updates? Not always. A read-then-write pattern can lose updates even under READ COMMITTED. Use SELECT FOR UPDATE, an atomic UPDATE, or a version column.
Where it goes wrong with Isolation Levels
given a default. They differ between engines, and code written against one silently changes action on the other.
Using SERIALIZABLE without retry logic. Intermittent failures under load.
Long-running read transactions. A snapshot held open forces the engine to retain old row versions. This Bloats storage and slows all.
Thinking about alone instead of using a constraint. A UNIQUE constraint settles "no two of these" even so of what any transaction sees.