Lesson 0005 · Internals & Performance

Transactions & isolation levels

Why two transactions running at once don't corrupt each other — and what "REPEATABLE READ" actually promises, built on a snapshot you can't see.

~10 minRetrieval quiz + hands-on two-session demoCheat sheet: here

Lessons 0001–0004 were about a single query reaching rows quickly. Now we turn to the other half of the mission: what happens when many transactions touch the same rows at the same time. The tool MySQL gives you is the isolation level, and the machine underneath it is MVCC. Understand the snapshot and the whole topic — including the notorious "REPEATABLE READ vs READ COMMITTED" interview question — falls into place.

The three anomalies isolation protects against

Concurrency can corrupt a read in three escalating ways. The isolation level is just a dial for which of these you tolerate:

dirty readYou see another transaction's uncommitted change — which may be rolled back.
non-repeatable readYou read a row twice in one transaction and get different values (someone committed an UPDATE between).
phantom readYou run the same WHERE twice and new rows appear (someone committed an INSERT between).

The one idea: a consistent read is a snapshot

MVCC — the keystone

A plain SELECT in InnoDB is a consistent nonlocking read: it reads a snapshot of the database at a point in time via multi-version concurrency control (MVCC) — not the live rows, and it sets no locks. So readers never block writers and writers never block readers. The isolation level only decides when that snapshot is taken. — MySQL Manual: "InnoDB uses multi-versioning to present to a query a snapshot of the database… A consistent read does not set any locks."

The four levels — weakest to strongest

LevelSnapshot ruleAllows
READ UNCOMMITTEDReads even uncommitted versions.dirty, non-repeatable, phantom
READ COMMITTEDFresh snapshot per statement.non-repeatable, phantom
REPEATABLE READ (default)Snapshot fixed at the first read.phantom (largely prevented in InnoDB)
SERIALIZABLELike RR, but plain SELECT becomes locking.nothing — full isolation

— MySQL Manual: "InnoDB offers all four transaction isolation levels… The default isolation level for InnoDB is REPEATABLE READ."

The distinction that gets asked in interviews

Both REPEATABLE READ and READ COMMITTED use MVCC snapshots. The only difference is snapshot timing:

REPEATABLE READ → ONE snapshot, taken at the first read; every later SELECT in the transaction sees that same frozen view. Read a row twice → identical. (No non-repeatable reads.) READ COMMITTED → A NEW snapshot for EACH statement; every SELECT sees whatever was committed as of that moment. Read a row twice → may differ. (Non-repeatable reads allowed.)

— MySQL Manual: "all consistent reads within the same transaction read the snapshot established by the first such read" (RR) vs "each consistent read… sets and reads its own fresh snapshot" (RC).

Why InnoDB's REPEATABLE READ rarely sees phantoms

In the SQL standard, REPEATABLE READ still permits phantom rows. InnoDB is stronger: because a plain SELECT reads the first-read snapshot, newly inserted rows simply aren't in that snapshot — so they don't appear. (For locking reads like SELECT … FOR UPDATE, InnoDB adds next-key locks to block the inserts too — that's Lesson 0006.)

Check yourself

Answer from memory — effortful recall is what builds retention. Feedback is immediate.

InnoDB's default transaction isolation level is:

InnoDB defaults to REPEATABLE READ — a snapshot fixed at the transaction's first read, so repeated SELECTs are consistent with each other.

A plain SELECT in InnoDB reads from:

Plain SELECT is a consistent nonlocking read: MVCC serves a point-in-time snapshot and sets no locks, so it never blocks writers.

Seeing another transaction's uncommitted change is a:

A dirty read exposes uncommitted data that might be rolled back. Only READ UNCOMMITTED permits it.

Under REPEATABLE READ, the read snapshot is fixed at:

REPEATABLE READ establishes one snapshot at the first consistent read; every later SELECT in the transaction reuses it, so reads are repeatable.

Under READ COMMITTED, each consistent read:

READ COMMITTED takes a new snapshot per statement, so a row can change between two SELECTs — a non-repeatable read, which this level allows.

A consistent (nonlocking) read in InnoDB:

Consistent reads take no locks — that's the point of MVCC. Other sessions can modify the same rows concurrently while your snapshot stays stable.

Hands-on: watch the snapshot freeze

Open two mysql sessions (call them A and B) against the same table — reuse people from earlier lessons.

1 · REPEATABLE READ (the default) — A's view is frozen

-- Session A
START TRANSACTION;
SELECT first_name FROM people WHERE id = 1;   -- e.g. 'Ann' — snapshot taken here

-- Session B (separate connection)
UPDATE people SET first_name = 'CHANGED' WHERE id = 1;   -- autocommits

-- Back in Session A — same transaction
SELECT first_name FROM people WHERE id = 1;   -- STILL 'Ann' — reads the frozen snapshot
COMMIT;
SELECT first_name FROM people WHERE id = 1;   -- now 'CHANGED' — new transaction, new snapshot

2 · READ COMMITTED — A sees the change mid-transaction

-- Session A
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT first_name FROM people WHERE id = 1;   -- 'CHANGED'
-- Session B: UPDATE people SET first_name='AGAIN' WHERE id=1;
SELECT first_name FROM people WHERE id = 1;   -- 'AGAIN' — fresh snapshot each statement
COMMIT;
What you should observe

Under REPEATABLE READ, A's second SELECT ignores B's committed update — the snapshot was frozen at A's first read. Only after A commits does a new snapshot reveal it. Under READ COMMITTED, A's second SELECT sees B's change immediately — a non-repeatable read, by design. That single behavioral difference is the two levels. Reset with SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;.

Primary source — read this next

MySQL 8.0 Reference Manual — Transaction Isolation Levels, then Consistent Nonlocking Reads for the MVCC snapshot mechanism in the authors' own words.

Where this goes next

You now hold the concurrency keystone: plain reads are lock-free snapshots, and the isolation level just times the snapshot. Lesson 0006 turns to the other side — locking: what happens when transactions write the same rows, record vs gap vs next-key locks, and how to read a deadlock from SHOW ENGINE INNODB STATUS.

Cheat sheet → Glossary → ← Lesson 0004 All lessons
I'm your teacher — if the snapshot timing is fuzzy, or you want to see a phantom actually appear (try it under READ COMMITTED with an INSERT), bring me your two-session output and we'll trace exactly which snapshot each SELECT read.