Intuition The ONE core ideaA database is a shared notebook that many people (transactions) scribble in at the same time , and isolation levels are a dial that decides how much of each other's half-finished scribbling they are allowed to see. Turn the dial up for correctness, down for speed — but you cannot understand that dial until you can read every word on it, so this page builds each word from nothing.
Before you touch the parent note, you must be able to read its alphabet. Below is every symbol, term, and picture the parent leans on — built in an order where each one only uses things already defined.
Definition Table, row, columnA table is a grid. Each horizontal line is a row (one real-world thing, e.g. one bank account). Each vertical slot is a column (one property of that thing, e.g. bal = balance). A single cell is one column's value for one row.
Picture the grid literally as squared paper: rows go across, columns go down, and the whole topic is about who is allowed to look at which cell, and when .
Why the topic needs it: every anomaly in the parent ("dirty read", "phantom") is a story about a specific cell or a specific set of rows being read at the wrong moment. Without the grid picture, "row" and "predicate" are just words.
A transaction is a bundle of database steps that must count as one single action — either all of it happens or none of it does. Think of it as a person picking up the notebook, making several scribbles, and then either saying "keep it" (commit) or "undo everything I just did" (rollback) .
A transaction is a fenced-off list of steps with two possible endings: a green "COMMIT" gate (make it permanent and visible to everyone) or a red "ROLLBACK" gate (erase it as if it never happened).
Why the topic needs it: isolation is only interesting because transactions overlap in time. If they never overlapped, there would be nothing to isolate.
The all-or-nothing promise is the A in ACID properties (Atomicity). The I in ACID is literally Isolation — the exact letter this whole chapter zooms into.
Definition Committed, uncommitted, rolled back
Committed data = the transaction pressed the green gate; the change is now official and permanent .Uncommitted (a.k.a. "in-progress") data = a change that has been written but no gate pressed yet ; it might still vanish.Rolled back data = the red gate was pressed; the change is thrown away .
Common mistake "Written to the table" means "committed"Why it feels right: you can see the new value sitting in the row.
Fix: a value can be written but uncommitted . Reading it is exactly the "dirty read" the parent warns about — you read something that may be erased a second later.
Why the topic needs it: the three anomalies differ precisely by which of these three states was read. Dirty read = reading uncommitted . Non-repeatable read = reading committed data that changed. Lock this vocabulary now.
Definition Concurrent / interleavingConcurrent means two transactions are alive at the same time . Interleaving means the database runs a step of one, then a step of the other, then back — like shuffling two decks of cards together instead of stacking them.
Intuition Why interleaving is dangerousIf you run T1 completely, THEN T2 completely (a serial order), the result is obviously "sane". But shuffle their steps and you can produce a result that no clean stack could ever produce. That impossible-under-any-serial-order result is what an anomaly is.
Why the topic needs it: the parent's phrase "no serial order could produce this" is meaningless until you can picture the shuffle vs the two clean stacks.
Definition Serial vs SerializableA serial execution = transactions run one at a time , no shuffling — deck stacked on deck.
Serializable = the shuffled (concurrent) result is equal to some serial order's result. It does not mean they actually ran one at a time; it means you can't tell the difference by looking at the final notebook.
Common mistake "Serializable = literally one at a time"Why it feels right: the word serial .
Fix: engines still run concurrently and just guarantee the outcome matches a serial order. See Serializable Snapshot Isolation (SSI) and Two-Phase Locking (2PL) for the two ways databases pull this off.
Why the topic needs it: "serializable" is the top rung of the parent's staircase and the gold standard the other levels are measured against.
A predicate is a yes/no rule that decides which rows a query cares about — written after WHERE. Example: WHERE age > 30 is the predicate "is this row's age bigger than 30?". Each row either matches (yes) or doesn't (no).
Intuition Membership vs content — the pictureDraw a circle around every row that matches the predicate. That circle is the result set . Two different things can change under you:
a row inside the circle changes its value → content changed → non-repeatable read .
a new row appears inside the circle (or one leaves) → membership changed → phantom read .
Why the topic needs it: the parent's hardest distinction — non-repeatable vs phantom — is exactly content-of-the-circle vs membership-of-the-circle. This figure is the whole difference.
A lock is a "do not touch" tag a transaction hangs on a row (or a range of rows). While the tag is up, others must wait . A read lock blocks writers; a range/predicate lock blocks even inserts into a queried range.
A snapshot is a frozen photo of the whole database at one instant. A transaction that reads from a snapshot sees the world as it was when the photo was taken, no matter what others commit afterwards.
Intuition Two roads to the same guarantee
Locking road: hold "do not touch" tags longer → fewer surprises but more waiting. This is Two-Phase Locking (2PL) .Snapshot road: hand each transaction a photo so it never sees live changes. This is MVCC (Multi-Version Concurrency Control) .
Why the topic needs it: the parent's implementation section ("hold read locks until commit", "one snapshot at transaction start") is unreadable without these two pictures. The higher the isolation level, the longer you hold locks or the more frozen your photo .
Definition Notation cheat-list
T1, T2 ::: two separate transactions running around the same time.SELECT ... FROM ... WHERE ... ::: a read query; SELECT = "give me", WHERE = the predicate.UPDATE ... SET ... ::: change an existing row's value.INSERT / DELETE ::: add / remove whole rows (these change membership → phantoms).COMMIT ::: green gate, make permanent.ROLLBACK ::: red gate, undo.count(*) ::: "how many rows matched" — a number, perfect for spotting a phantom (the count changes).
Why the topic needs it: every worked example in the parent is written in exactly this shorthand.
Committed vs Uncommitted data
Read it bottom-up: rows and transactions feed the ideas of committed data and interleaving; those plus the predicate define what an anomaly even is; locks and snapshots are the machinery; the isolation-level dial sits on top of all of it.
Test yourself — cover the right side and answer out loud.
A table, a row, a column — what is each? A table is a grid; a row is one thing (one account); a column is one property (bal). A cell is one property of one thing.
What is a transaction? A bundle of steps that is all-or-nothing, ending in COMMIT (keep) or ROLLBACK (undo).
Difference between committed, uncommitted, and rolled-back data? Committed = permanent & official; uncommitted = written but no gate pressed yet, may vanish; rolled back = thrown away.
What does "interleaving" mean? Running a step of T1, then a step of T2, then back — shuffling two decks instead of stacking them.
Does "serializable" mean transactions run one at a time? No — it means the concurrent result equals some serial order; they still run concurrently.
What is a predicate? A yes/no rule after WHERE (e.g. age > 30) deciding which rows are in the result set.
Content-change vs membership-change of the result circle — which anomaly is which? Content change (existing row's value) = non-repeatable read; membership change (row appears/disappears) = phantom read.
What is a lock? A snapshot? A lock is a "do-not-touch" tag forcing others to wait; a snapshot is a frozen photo of the DB at one instant.
Which ACID letter is this whole chapter about? I — Isolation.
See also: ACID properties , Deadlocks , Write skew anomaly , and the parent Hinglish version .
