CSE Roadmap

Core tier · about 2 hours · 5 parts

Part 5 of 5 · 4 min read

Exercises

Easy

Easy 1: Name what a write creates under MVCC (15 minutes)

State what a write creates under MVCC, instead of overwriting the row in place.

Done when: a new version of the row is correctly named.

Hint

A new version of the row.

Easy 2: Define snapshot isolation (15 minutes)

State, in one sentence, what snapshot isolation guarantees a transaction.

Done when: a consistent view of the database as of the transaction's own start time is correctly described.

Hint

A consistent view of the database exactly as it existed at the transaction's own start time.

Easy 3: Name why readers and writers don't block (15 minutes)

State why a reader and a writer never block each other under MVCC.

Done when: each working with a separate version of the row, avoiding any shared-lock contention, is correctly described.

Hint

Each works with a separate version of the row, so there is no single shared lock for the two to contend over.

Medium

Medium 1: Decide which version a transaction sees (25 minutes)

A transaction begins, reads a row, then a different transaction commits a new version of that same row, and the first transaction reads the row again. Determine which version the first transaction's second read returns.

Done when: the original version, current at the first transaction's own start time, is correctly identified, not the newly committed version.

Hint

The original version, the one current at the first transaction's own start time, not the newly committed version from the other transaction.

Medium 2: Connect snapshot isolation to a specific anomaly (25 minutes)

Explain why snapshot isolation prevents a non-repeatable read, using the isolation-levels page's definition of that anomaly.

Done when: the explanation correctly notes a transaction under snapshot isolation always reads the same, snapshot-fixed version of a row on every read within that transaction, so the value can never appear to change partway through.

Hint

Snapshot isolation fixes a transaction's view of each row at its start time, so re-reading that row within the same transaction always returns the same, unchanged version, which is exactly the non-repeatable-read protection.

Medium 3: Explain the storage cost of MVCC (30 minutes)

Explain what MVCC must do with an old version of a row once no active transaction's snapshot still needs it.

Done when: the explanation correctly notes the old version can eventually be cleaned up or removed once no transaction's snapshot still requires it, since keeping every version forever would grow storage without bound.

Hint

Once no active transaction's snapshot still needs an old version, it can be cleaned up or removed, since retaining every version indefinitely would let storage grow without bound.

Hard

Hard 1: Explain why MVCC avoids the lock conflict locking would have (30 minutes)

Explain, by contrasting with the shared-and-exclusive-lock model from two pages ago, why a reader and a writer accessing the same row at the same time do not conflict under MVCC the way they would under plain locking.

Done when: the explanation correctly notes that under plain locking, a reader's shared lock and a writer's exclusive lock on the same row would conflict and force one to wait, while under MVCC the reader is reading an existing, separate version untouched by the writer's new version, so there is no shared resource to contend over at all.

Hint

Plain locking would have the reader's shared lock and the writer's exclusive lock conflict directly over the one shared row, forcing a wait; MVCC instead gives the reader an existing, separate version the writer's new version never touches, so there is nothing shared to contend over.

Hard 2: Explain why a long-running transaction has a real cost under MVCC (35 minutes)

Explain why a transaction that stays open for a very long time can impose a cost on an MVCC-based system, even though it never blocks any other transaction directly.

Done when: the explanation correctly notes that as long as that long-running transaction's snapshot still references old versions, those versions cannot be cleaned up, so a very long-running transaction can cause old versions to accumulate and consume storage for an extended period.

Hint

As long as a long-running transaction's snapshot still references old versions, the storage engine cannot clean those versions up, so an unusually long-running transaction can cause old versions to accumulate and consume storage for longer than usual, even without blocking anyone directly.

Hard 3: Predict what snapshot isolation alone does not guarantee (40 minutes)

Based only on what this page has defined, predict whether snapshot isolation alone is sufficient to guarantee full serializability, as defined two pages ago in this level, and explain your reasoning.

Done when: the prediction correctly expresses uncertainty or a reasoned "not necessarily," noting that snapshot isolation guarantees a consistent read view but says nothing yet about whether two concurrent transactions' writes, based on their respective snapshots, could still produce a final result inconsistent with any possible serial ordering, a question the next page addresses directly.

Hint

Not necessarily; snapshot isolation guarantees each transaction reads a consistent view, but it does not by itself address whether two transactions' concurrent writes, each based on their own snapshot, could still produce a combined result that no serial ordering could have produced, which the next page covers directly.