CSE Roadmap

Common tier · about 2 hours · 5 parts

Part 5 of 5 · 3 min read

Exercises

Easy

Easy 1: Name the first step (15 minutes)

State what changes first when an UPDATE statement runs: the data file on disk, or the in-memory page in the buffer pool.

Done when: the in-memory page is correctly named as changing first.

Hint

The in-memory page in the buffer pool changes first.

Easy 2: Name the log's role (15 minutes)

State what a write-ahead log entry records.

Done when: the answer correctly names the changed page, the old value, and the new value.

Hint

Which page changed, the old value, and the new value.

Easy 3: Name what makes a page reach disk (15 minutes)

State two reasons a dirty page eventually gets written to the data file on disk.

Done when: both eviction (needing buffer pool space) and periodic background flushing are named.

Hint

Eviction, to free buffer pool space, and a periodic background flush process.

Medium

Medium 1: Order the three steps (25 minutes)

Put the following three events in the correct order for a single write: page flush to disk, in-memory page change, write-ahead log entry.

Done when: the order given is in-memory page change (or log write, nearly simultaneously), log entry written durably, then page flush, with log durability placed before the flush.

Hint

In-memory change and log entry happen essentially together, with the log durable before the later flush.

Medium 2: Explain why the log comes before the flush (25 minutes)

Explain why the write-ahead log entry must be durable before the dirty page is guaranteed to reach disk.

Done when: the explanation correctly notes that the log is the only record of the change if the system crashes before the page is flushed.

Hint

If a crash happens before the flush, the log is the only durable record that the change ever happened.

Medium 3: Predict the state right after a crash (30 minutes)

A crash happens immediately after the in-memory page changes and the log entry is written, but before any flush. Describe what the data file on disk contains at that moment.

Done when: the answer correctly states the data file still holds the old value, while the log file already holds a durable record of the new value.

Hint

The data file still shows the old value; the log file already has the new value recorded durably.

Hard

Hard 1: Connect this page to the buffer pool page (30 minutes)

Explain how the dirty-page concept from the buffer pool page and the write-ahead log from this page work together during a single update.

Done when: the explanation correctly describes the page becoming dirty in the buffer pool while the log entry is written durably, with the dirty flag staying set until a later flush.

Hint

The page becomes dirty in the buffer pool at the same time its change is logged; the dirty flag stays set until a flush later clears it.

Hard 2: Why not write straight to the data file (35 minutes)

Explain why writing straight to the data file on every single UPDATE, instead of going through the buffer pool and log, would be impractical for a busy database.

Done when: the explanation correctly ties direct disk writes on every update to far higher I/O cost compared to batching flushes and relying on a fast sequential log.

Hint

Flushing every single update immediately means constant disk I/O for small changes, whereas batching flushes and relying on a fast sequential log amortizes that cost.

Hard 3: Preview the recovery problem (40 minutes)

A database crashes with several dirty pages never flushed, but their log entries already durable. Describe, in general terms, what information is available to reconstruct those lost changes.

Done when: the answer correctly identifies the durable write-ahead log entries as the source of information Level 8's recovery process would use to reconstruct the unflushed changes.

Hint

The durable write-ahead log entries contain exactly the old and new values needed to redo or verify each lost change, which Level 8 formalizes as the recovery process.