Compare isolation levels against the anomalies they permit, with each engine's real default and behaviour, plus ACID, locking and deadlocks explained.
Your database's default is probably not the one you assume
PostgreSQL and SQL Server default to read committed; MySQL InnoDB to repeatable read; Oracle has no read uncommitted at all.
| Level | Dirty read | Non-repeatable | Phantom | Lost update |
|---|---|---|---|---|
| Read uncommitted | possible | possible | possible | possible |
| Read committed | prevented | possible | possible | possible |
| Repeatable read | prevented | prevented | varies | varies |
| Serializable | prevented | prevented | prevented | prevented |
This table is what the SQL standard permits. Real engines are often stricter — the reality line on each level below says where.
A transaction can read changes another has made and not committed — changes that may be rolled back and never have existed.
In practice — PostgreSQL does not implement it: asking for it gives you read committed, because its MVCC design has no way to read uncommitted data. SQL Server does implement it, and `WITH (NOLOCK)` is the same thing applied per query — widely used and widely misunderstood, since it can return a row twice or skip one entirely during a page split.
Cost — Cheapest, and almost never worth it.
Each statement sees only committed data — but a second statement in the same transaction sees a newer snapshot, so the same query can return different answers.
In practice — The one people assume is safe. Reading a balance, deciding, then writing it back is a lost update at this level: another transaction can commit between your read and your write, and your write overwrites theirs with no error. `SELECT … FOR UPDATE` or an atomic `UPDATE … SET balance = balance - 10` is the fix, not a higher level.
Cost — Low. The default nearly everywhere for that reason.
Every read in the transaction sees the same snapshot, so re-reading a row returns what it returned the first time.
In practice — The level where the two major databases genuinely differ. PostgreSQL's implementation also prevents phantom reads, which the standard permits here, and aborts a transaction with a serialisation failure rather than letting a conflicting write through — so your code must be prepared to retry. MySQL's InnoDB prevents phantoms for plain reads through its snapshot, but a locking read (`SELECT … FOR UPDATE`) sees the latest committed data instead, which surprises people.
Cost — Moderate. Transactions can fail and need retrying.
The result is guaranteed to match some order of running the transactions one after another.
In practice — PostgreSQL implements it with predicate locking rather than blocking, so transactions proceed and some are aborted at commit time — meaning the retry loop is not optional, it is the design. MySQL implements it by turning every plain `SELECT` into a locking read, which blocks instead. Same level, opposite failure mode: one gives you errors to retry, the other gives you waits.
Cost — Highest. Expect serialisation failures under contention and write the retry loop.
Transaction Isolation Explainer runs entirely on your device — this is a reference, and no database is contacted.
You’re on the Free plan. Compare plans
The default is not the standard's default, and that is the most consequential fact in this whole area. PostgreSQL and SQL Server default to read committed; MySQL's InnoDB defaults to repeatable read; Oracle does not implement read uncommitted at all. Code written against one database's default behaves differently against another's without a single line changing.
The table shows what the SQL standard permits. The reality line on each level is where real engines diverge from it — PostgreSQL's repeatable read prevents phantom reads, which the standard allows, so reading the standard alone will have you expecting an anomaly that cannot occur there, and reading PostgreSQL alone will leave you surprised by MySQL.
Write skew is the anomaly that survives repeatable read, and it is the reason serializable exists as a separate level rather than a stricter repeatable read. Two transactions each read a consistent snapshot, each make a decision valid against what they read, and together they break an invariant neither could see being broken. The on-call doctor example is the classic one and it is a real class of bug.
Each anomaly is given as a concrete two-transaction sequence rather than a definition, because the definitions are nearly identical to each other and the sequences are not.
Consistency in ACID means your declared constraints, not your business rules — which is the most common misreading of the acronym. A transfer that debits one account and never credits the other is perfectly consistent if no constraint says the totals must match.
Whatever your database defaults to, unless you set one. PostgreSQL and SQL Server default to read committed, MySQL's InnoDB to repeatable read. Your ORM may change it, and connection poolers sometimes do too.
Two transactions read overlapping data, each makes a decision that is valid for what it read, and together they break a rule neither could see breaking. Both on-call doctors marking themselves unavailable at the same moment is the standard example. Repeatable read does not prevent it; serializable does.
Less often than people assume. PostgreSQL implements it optimistically — transactions run without extra locking and some are aborted on conflict, so the cost is retries rather than waiting. It needs retry logic, which is the real barrier.
No — it means your declared constraints hold. Foreign keys, CHECKs, uniqueness. A transfer leaving money missing is consistent if no constraint says otherwise. Business correctness is your transaction's job, not the letter C's.
Retry. Databases detect deadlocks and kill one transaction specifically so the other proceeds, which makes it normal operation rather than a failure. Code that touches multiple tables needs a retry path, and consistent lock ordering makes them rarer.