Lesson 0009 · Interleaved review

Interleaved review

Eight lessons, mixed on purpose — the difficulty of jumping between pillars is what turns fluency into durable, transferable mastery.

~15 minMixed quiz + diagnose-it scenariosMap: course map

This lesson teaches nothing new. It interleaves — the retrieval questions hop between indexing, concurrency, and durability from one item to the next. That hopping is harder than a themed quiz, and the extra effort is the point: it trains you to first recognize which concept applies, the skill a real slow query or incident actually demands. Answer everything from memory; if one stings, follow its link and re-read.

Part 1 — mixed retrieval

A lookup by primary key returns the full row in:

The clustered-index leaf is the row (0001), so a PK lookup lands on the data in one descent — no bookmark lookup.

Under REPEATABLE READ, the read snapshot is fixed at:

RR sets one snapshot at the transaction's first read (0005); READ COMMITTED takes a fresh one per statement.

On the type ladder, the rung between ref and index is:

const → eq_ref → ref → range → index → ALL (0003). Equality (ref) before span (range).

An UPDATE on an unindexed column tends to lock:

Locks sit on the index records the query walks (0006); with no index it scans and locks every examined row — slow (0004) and a deadlock magnet.

At COMMIT (default settings), what must be durable on disk?

Write-ahead logging (0007): only the sequential redo append is fsync'd at commit; dirty data pages flush later.

Index (a, b) cannot perform a seek for a filter on:

Only leftmost prefixes seek (0002): (a) and (a,b) work; b alone skips the leading column.

With async replication, a source failover can lose:

If the source dies before the binlog reaches a replica (0008), a promoted replica is missing that committed change. Semisync bounds this.

Which predicate lets an index on the column seek?

Sargable = bare column vs matching constant (0004). A function wrap or a leading wildcard kills the seek.

A plain SELECT meeting a concurrent UPDATE of the same row will:

Plain SELECT is a lock-free MVCC snapshot read (0005/0006) served from the undo log — readers don't block writers.

Besides ROLLBACK, the undo log also powers:

Undo stores prior row versions (0007), which MVCC snapshot reads reconstruct (0005). Redo does crash recovery; the binlog does replication.

Extra: Using index in EXPLAIN means the query was:

A covering index answers from the index alone (0003), skipping the clustered-index bookmark lookup (0001).

In REPEATABLE READ, next-key locks exist to stop:

A next-key lock = record + gap lock (0006); the gap portion blocks the INSERTs that would create phantoms — the locking-read counterpart to the MVCC snapshot.

Part 2 — diagnose it

Higher-order transfer: read each scenario, commit to an answer out loud, then expand to check. These are the shapes real problems arrive in.

1. 0003 · 0004 Your index on created_at exists, but EXPLAIN shows key: NULL, type: ALL for WHERE YEAR(created_at) = 2026. Why, and what's the fix?
The column is wrapped in a function, so it's non-sargable — the index sorts raw created_at, not YEAR(...) of it (0004). Fix: rewrite as a bare-column range: created_at >= '2026-01-01' AND created_at < '2027-01-01' → now type: range (0003). Or add a functional index on (YEAR(created_at)).
2. 0002 · 0003 A report filters WHERE tenant_id = ? AND status = ? and sorts ORDER BY created_at. Design one index that serves the filter and kills the filesort.
INDEX (tenant_id, status, created_at). The two equality columns form the leftmost prefix for the seek (0002); because created_at follows them in the index, the rows already arrive in created_at order, so no Using filesort (0003). Equality columns first, the ORDER BY column last.
3. 0005 · 0007 A colleague says "we read from a replica, so a plain SELECT there might block our writes on the source." True?
No. A plain SELECT is a lock-free MVCC snapshot read (0005) served from undo (0007) — it takes no locks even locally, and a replica is a separate server anyway. Reads never block writers. (What a replica can show is slightly stale data due to replication lag — a different issue, from 0008.)
4. 0006 · 0004 Two workers each run UPDATE jobs SET state='done' WHERE state='running' and you're getting frequent deadlocks. state has no index. What's the connection, and one fix?
Without an index on state, each UPDATE scans and locks every row it examines (0006 keystone: locks sit on the index records walked; no index → scans the clustered index). Wide, overlapping lock footprints across two workers → deadlock cycles. Fix: index state so each statement locks only matching rows; also keep transactions short and process in a consistent order (0006).
5. 0007 · 0008 You need "we can never lose a committed order, even if a whole server dies." Which two settings/modes do you reach for, and what does each buy?
(a) innodb_flush_log_at_trx_commit = 1 — full local durability: the redo is fsync'd at every commit, so a single-server crash loses nothing (0007). (b) Semisynchronous replication — the commit waits until ≥1 replica has received the binlog events, so if the whole source machine dies, the committed order still exists on a replica to fail over to (0008). Local durability + a guaranteed remote copy.
If any of those were hard

That's the signal telling you where to spend a re-read — not a failure. Open the linked lesson, skim its one keystone callout, and re-attempt the scenario tomorrow. Spacing + a second attempt is worth more than re-reading now.

The whole system, in one breath

A row lives in the clustered index (PK B+tree). Secondary indexes point back by PK, so a non-covering read is a double lookup — unless a covering/composite index (leftmost prefix) answers it index-only. EXPLAIN grades the plan (type ladder + key + rows×filtered + Extra); functions, wildcards, type mismatches, or low selectivity make the optimizer skip the index. Concurrently, plain reads are lock-free MVCC snapshots (isolation level = when the snapshot is taken); writes and locking reads take record/gap/next-key locks on index records, and lock cycles deadlock (retry the victim). A COMMIT is durable because the redo log is written ahead of the data pages (undo powers rollback + those MVCC snapshots). The binary log ships the same committed changes to replicas — async (may lose a failover commit) or semisync (guaranteed received by a replica first).
Keep for review

The course map puts all eight keystones and a spaced-review schedule on one printable page. Revisit it, not the lessons, for maintenance.

Where this goes next

You've reviewed the whole arc. The most valuable next step is real-world transfer: bring a genuine slow EXPLAIN, a LATEST DETECTED DEADLOCK, or a replication-lag graph from your own database and we'll dissect it together — or go deeper on a chosen sub-topic (advanced tuning, partitioning, GTID failover, PITR).

Course map → Glossary → ← Lesson 0008 All lessons
I'm your teacher — tell me which scenario above was hardest and we'll drill that pillar, or bring me a real problem from work and we'll reason it out from first principles together.