CSE Roadmap

Core tier · about 2 hours · 5 parts

Part 5 of 5 · 4 min read

Exercises

Easy

Easy 1: Define a dirty read (15 minutes)

State, in one sentence, what a dirty read is.

Done when: reading another transaction's uncommitted, potentially-to-be-rolled-back value is correctly described.

Hint

Reading a value another transaction wrote but has not yet committed, risking it later being rolled back.

Easy 2: Distinguish a non-repeatable read from a phantom read (15 minutes)

State the key difference between a non-repeatable read and a phantom read.

Done when: a non-repeatable read concerning one row's changing value, versus a phantom read concerning a changing set of matching rows, is correctly described.

Hint

A non-repeatable read is about one row's value changing; a phantom read is about the set of rows matching a query changing.

Easy 3: Name what each isolation level prevents (15 minutes)

State which of the three anomalies serializable prevents, compared to read committed.

Done when: serializable preventing all three (dirty reads, non-repeatable reads, and phantom reads), compared to read committed preventing only dirty reads, is correctly stated.

Hint

Serializable prevents dirty reads, non-repeatable reads, and phantom reads; read committed prevents only dirty reads.

Medium

Medium 1: Pick the minimum isolation level for a scenario (25 minutes)

An application only needs to avoid reading uncommitted, possibly-to-be-rolled-back data, with no other requirement. Pick the minimum standard isolation level that satisfies this, and explain why.

Done when: read committed is correctly chosen, since it prevents dirty reads specifically, which is the only requirement stated.

Hint

Read committed, since its specific guarantee, preventing dirty reads, is exactly and only what the scenario requires.

Medium 2: Diagnose an anomaly from a symptom (25 minutes)

A report runs the same WHERE status = 'placed' query twice within one transaction and gets a different row count the second time, even though no single previously-read row's value changed. Identify the anomaly.

Done when: a phantom read is correctly identified, since the matching row set changed rather than any specific previously-read row's value.

Hint

A phantom read, since the set of rows matching the condition changed, not the value of any specific row that was read before.

Medium 3: Decide if repeatable read is sufficient (30 minutes)

An application reads a specific row twice within one transaction and requires that value to stay consistent across both reads, but never re-runs a filtering query more than once. Decide whether repeatable read is sufficient, and explain why.

Done when: yes is correctly concluded, since the application's only requirement is protection against non-repeatable reads, which repeatable read specifically provides, with no phantom-read scenario present.

Hint

Yes, repeatable read specifically guarantees a previously read row's value stays consistent across re-reads within the same transaction, which is exactly what is needed here.

Hard

Hard 1: Explain why read committed still allows non-repeatable reads (30 minutes)

Explain why read committed, despite preventing dirty reads, does not also prevent non-repeatable reads.

Done when: the explanation correctly notes read committed only requires that any value read has already been committed at the moment it is read, but does not prevent a different, already-committed value from being read on a later, separate read within the same transaction.

Hint

Read committed only checks that a value is committed at the moment it is read; it places no restriction preventing a later read within the same transaction from seeing a newer, different committed value.

Hard 2: Explain why phantom reads need special handling beyond row-level protection (35 minutes)

Explain why preventing non-repeatable reads on specific, already-read rows does not automatically prevent phantom reads as well.

Done when: the explanation correctly notes that non-repeatable read protection concerns rows that have already been read once, while a phantom read involves an entirely new row, one that was never previously read at all, appearing or disappearing from a query's result set, which row-level protection alone does not address.

Hint

Protecting previously-read rows says nothing about rows that did not exist in the result at the time of the first read; a phantom read involves a new row entering or leaving the result set entirely, which is a different mechanism than a specific row's value changing.

Hard 3: Choose an isolation level with a stated performance constraint (40 minutes)

An application must prevent phantom reads for one specific, business-critical report, but the overall system has strict concurrency and throughput requirements that make running everything at serializable isolation unacceptable. Propose an approach that satisfies both constraints.

Done when: a reasonable approach is proposed, such as running most of the application at a lower isolation level like read committed or repeatable read for throughput, while running specifically the business-critical report's transaction at serializable isolation, since isolation level can typically be set per transaction rather than system-wide.

Hint

Since isolation level is typically set per transaction, the system can run most work at a lower level for throughput while specifically elevating the business-critical report's own transaction to serializable isolation, satisfying both the correctness need and the broader performance constraint.