Beyond Code & Karma · Databases
Two transactions touch the same row and each perceives its own version
of reality. The isolation level decides how much one
transaction's actions disturb another's perceived truth — from
anything-goes Read Uncommitted to perfectly serial
Serializable. Explained through anomalies, MVCC snapshots,
and the discipline of not letting illusion leak between observers.
Chapter 01 — The Foundation
A transaction groups several operations so they succeed or fail as one. Isolation is the promise that concurrent transactions don't corrupt each other's view of the data — ideally, each runs as though it were alone. Perfect isolation is expensive, so databases offer a dial: weaker levels allow more anomalies but more concurrency; stronger levels forbid anomalies but block, abort and retry more.
The Cosmic Connection
In Vedic thought, satya (truth) rests on rita — the cosmic order that keeps every observer's reality coherent. Against it stands maya, illusion: seeing something that isn't truly real. Serializable is rita — perfect order where every transaction sees one consistent truth. A dirty read is maya — seeing an uncommitted value that may vanish. Each weaker level lifts another veil, trading truth for speed.
"Hold to rita and every observer shares one truth; tolerate maya, and you trade certainty for speed."
Chapter 02 — The Dial
The SQL standard names four levels. Climbing the ladder removes one class of anomaly at a time — at the cost of more blocking, aborts and retries, and therefore less concurrency.
The floor. A transaction may read another's uncommitted writes. Permits dirty reads, non-repeatable reads and phantoms. Maximum concurrency, minimum truth.
You only ever see committed data — dirty reads gone. But each statement takes a fresh snapshot, so a re-read can still change. The common default (PostgreSQL, Oracle, SQL Server).
One snapshot for the whole transaction — re-read a row and it's unchanged. Non-repeatable reads gone. The standard still permits phantoms; snapshot engines like PostgreSQL prevent most. MySQL/InnoDB's default.
The ceiling — rita. The result must equal some serial order of the transactions. The engine detects dangerous interleavings and aborts one with a serialization failure; your app retries. No anomalies, highest cost.
Chapter 03 — The Hazards
Concurrency anomalies are the ways one transaction's view of the data gets corrupted by another running alongside it. Three are read anomalies, in increasing subtlety; a fourth corrupts a write.
COUNT(*) query twice; another transaction commits an insert/delete in between, so new rows appear or vanish.
Under Read Committed, each statement re-snapshots. T1 reads twice and gets two different answers because T2 committed in between:
-- Two sessions, Read Committed. Watch x change under T1's feet. -- session T1 session T2 BEGIN; SELECT x FROM acct WHERE id = 1; -- T1 reads x = 100 BEGIN; UPDATE acct SET x = 150 WHERE id = 1; COMMIT; -- T2 commits the change SELECT x FROM acct WHERE id = 1; -- T1 re-reads x = 150 → NON-REPEATABLE READ COMMIT;
Only Read Uncommitted permits this. T2 reads a value that T1 later throws away:
-- A dirty read: seeing a value that may never become real. -- session T1 session T2 (READ UNCOMMITTED) BEGIN; UPDATE acct SET x = 150 WHERE id = 1; SELECT x FROM acct WHERE id = 1; -- reads 150, NOT committed ROLLBACK; -- x snaps back to 100; the 150 never existed -- T2 acted on maya — an illusion that dissolved.
Raise the level on just the transaction that needs it. At Repeatable Read both reads share one snapshot; at Serializable a conflicting interleaving aborts instead:
-- Raise the level only on the transaction that needs it. BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- both reads now share ONE snapshot — no non-repeatable read SELECT x FROM acct WHERE id = 1; -- ... other transactions commit in between ... SELECT x FROM acct WHERE id = 1; -- identical value COMMIT; -- At SERIALIZABLE the engine may instead raise: -- ERROR: could not serialize access due to concurrent update -- ...which means: retry the transaction.
What each level permits, per the ANSI SQL standard. "Allowed" means the level does not prevent it.
| Isolation level | Dirty read | Non-repeatable | Phantom |
|---|---|---|---|
| Read Uncommitted | Allowed | Allowed | Allowed |
| Read Committed | Prevented | Allowed | Allowed |
| Repeatable Read | Prevented | Prevented | Allowed* |
| Serializable | Prevented | Prevented | Prevented |
* The standard permits phantoms at Repeatable Read, but PostgreSQL's snapshot-based Repeatable Read prevents most of them — theory and practice diverge here. The visualizer follows the PostgreSQL snapshot model.
Chapter 04 — Live Visualization
A scripted two-transaction interleaving at Read Uncommitted. T2 reads a value T1 never commits — maya — then T1 rolls back and the value dissolves. Press Run, or step through it.
Chapter 05 — Deep Dive
Beneath the levels sit two strategies for managing conflict — locking and versioning — and a lot of nuance senior engineers carry.
Instead of overwriting a row in place, every update writes a new version and leaves the old one behind. Each version carries two transaction-id stamps:
| Stamp | Meaning |
|---|---|
xmin | The transaction that created this version. |
xmax | The transaction that deleted or superseded it (empty while live). |
A statement takes a snapshot — the set of
transactions whose effects it may see. A version is visible only
if its xmin committed before the snapshot and its
xmax hasn't. Readers walk to the newest visible
version, so reads never block writes and writes never block
reads. Repeatable Read takes one snapshot at transaction
start and reuses it — that's why a re-read is stable.
Keeping old versions isn't free. Every update and delete leaves a dead tuple — a version no live snapshot can see — and they pile up in the table and its indexes. PostgreSQL's autovacuum reclaims that space and advances the visibility horizon. If it falls behind you get table bloat, slower scans, and eventually transaction-id wraparound pressure. Long-running transactions are the usual culprit: they hold the horizon back, keeping dead tuples alive.
Snapshot isolation (Repeatable Read) stops the classic anomalies, but not write skew: two transactions read an overlapping set, each checks an invariant that still holds, and each writes a different row — together breaking the invariant. Classic example: "at least one doctor must remain on call." Both doctors read "two on call", both decide it's safe to go off, both commit — now zero. Only Serializable detects the dangerous read/write dependency and aborts one.
PostgreSQL, Oracle and SQL Server (with read-committed snapshot) default to a flavour of Read Committed. MySQL's InnoDB defaults one notch higher to Repeatable Read. None default to Serializable — it's the most expensive, since it must detect and abort non-serializable interleavings and your app must retry. The practical rule: default to Read Committed and raise the level only on the specific transactions whose correctness depends on it.
At Repeatable Read and Serializable, a transaction can fail at commit with a serialization failure ("could not serialize access due to concurrent update"). This is not a bug — it's the engine refusing to let an anomaly through. The correct response is to retry the whole transaction from the beginning, usually with a small backoff. Code that raises the isolation level without a retry loop is incomplete.
Chapter 06 — Dharma & The Database
Many observers, many truths. As each soul perceives reality coloured by its own karma, each transaction reads the world as it was at the moment of its intention — its snapshot. A dirty read is an illusion leaking from one observer into another; isolation is the discipline of not letting your unfinished actions disturb another's perceived truth.
-- Each transaction reads the world as it was at its intention. BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- perfect rita -- act as though you alone exist — the result stays consistent SELECT sum(balance) FROM acct; UPDATE acct SET balance = balance - 10 WHERE id = 1; COMMIT; -- or retry, if another's karma conflicts with yours
Serializable is perfect dharma: every transaction may act as though it alone exists, and the result is still consistent. MVCC keeps the old worlds intact a while, so no observer is forced into another's present — and lets them go, through vacuum, once no one is looking back.
"Act on the truth that was real at your intention; do not be moved by another's unfinished act." — the Gita, interpreted for database engineers 🙏
Chapter 07 — Test Yourself
Three questions. No cheating. Your karma is watching.
The Enlightenment
कर्म करो, फल की चिंता मत करो