Part 5 of 5 · 5 min read
Exercises
Easy
Easy 1: Name the first step in choosing isolation level (15 minutes)
State the first step in the decision process this page describes for choosing an isolation level.
Done when: identifying the specific anomaly the logic is vulnerable to is correctly named.
Hint
Identifying the specific anomaly the piece of logic is actually vulnerable to.
Easy 2: Name the cost of defaulting to serializable everywhere (15 minutes)
State the cost of applying serializable isolation to every transaction in an application, regardless of actual need.
Done when: paying serializable's waiting or abort-and-retry cost even for transactions that were never vulnerable to the anomalies it prevents is correctly described.
Hint
Paying serializable's real waiting or abort-and-retry cost even on transactions that were never actually vulnerable to the anomalies it guards against.
Easy 3: Name an alternative to raising isolation level (15 minutes)
Name one alternative mechanism to raising a transaction's isolation level for protecting a specific invariant.
Done when: an explicit lock, such as SELECT ... FOR UPDATE, or a database constraint is correctly named.
Hint
An explicit lock, such as SELECT ... FOR UPDATE, or a database-level constraint.
Medium
Medium 1: Diagnose a reported bug's anomaly (25 minutes)
A report states that two concurrent signups both independently checked "is this username still available" and both committed, resulting in two accounts with the same username. Identify the anomaly and the isolation level or mechanism that would prevent it.
Done when: write skew is correctly identified, with a reasonable fix named, such as serializable isolation, serializable snapshot isolation, or a unique constraint on the username column enforced regardless of isolation level.
Hint
Write skew; a reasonable fix is serializable isolation, serializable snapshot isolation, or simply a UNIQUE constraint on the username column, which enforces the invariant directly regardless of isolation level.
Medium 2: Decide if a read-only dashboard needs serializable isolation (25 minutes)
A dashboard only reads data for display and never writes based on a cross-row check. Decide whether it needs serializable isolation, and explain why.
Done when: no is correctly concluded, since the dashboard is never vulnerable to write skew or the phantom case that specifically involves a write decision based on a read, making a lower isolation level sufficient.
Hint
No, since the dashboard never makes a write decision based on a cross-row read, it is not exposed to write skew or the specific phantom case this level covers, so a lower isolation level is sufficient.
Medium 3: Weigh an explicit lock against isolation-level escalation (30 minutes)
A specific feature's invariant can be expressed as a database UNIQUE constraint. Decide whether this is generally preferable to escalating that feature's transaction to serializable isolation, and explain why.
Done when: the constraint is reasonably preferred, with the explanation correctly noting a database-level constraint enforces the invariant directly and cheaply, regardless of isolation level, without needing to pay serializable's broader cost at all.
Hint
The UNIQUE constraint is generally preferable, since it enforces the invariant directly and cheaply at the database level, without needing to pay serializable isolation's broader concurrency or retry cost across the whole transaction.
Hard
Hard 1: Walk through the full decision process for a new feature (30 minutes)
A new feature allows users to redeem a limited-quantity coupon, checking remaining quantity before decrementing it. Walk through the decision process this page describes to choose an appropriate isolation level or mechanism.
Done when: the walkthrough correctly identifies write skew as the risk (two concurrent redemptions each seeing sufficient quantity in their own snapshot), then reasonably proposes either serializable isolation, serializable snapshot isolation, or an explicit lock or atomic decrement-with-check as the fix, reasoning about cost versus risk rather than defaulting immediately to the strictest option.
Hint
The risk is write skew, since two concurrent redemptions could each see sufficient quantity in their own snapshot; a reasonable fix is serializable isolation, serializable snapshot isolation, or an explicit lock or atomic check-and-decrement, chosen by weighing the actual concurrency and retry cost rather than defaulting immediately to the strictest option.
Hard 2: Explain why the same feature might need different isolation levels in different systems (35 minutes)
Explain why a feature with identical logic might reasonably be given serializable isolation in one system but a lower isolation level with an explicit lock in another.
Done when: the explanation correctly notes the right choice depends on factors like expected concurrency, the cost of an occasional abort and retry for that specific application, and whether the invariant can be expressed as a cheaper, targeted constraint, all of which can differ between systems even with identical core logic.
Hint
The right choice depends on expected concurrency, how costly an occasional abort and retry is for that specific application, and whether the invariant can be expressed as a cheaper, targeted constraint, all of which can vary between systems even when the core transaction logic is otherwise identical.
Hard 3: Critique a blanket policy of 'always use serializable' (40 minutes)
A team adopts a blanket policy of running every transaction at serializable isolation, reasoning that it is simply the safest choice. Critique this policy using this level's material, and propose a better alternative process.
Done when: the critique correctly identifies this as isolation-level overreach, paying serializable's real concurrency or abort-and-retry cost across the entire application regardless of actual vulnerability, with a better alternative proposed along the lines of this page's anomaly-first process: identifying each feature's actual risk and matching the isolation level or mechanism to that specific need.
Hint
This is isolation-level overreach, paying serializable's real cost everywhere regardless of whether each specific transaction was ever actually vulnerable to the anomalies it prevents; a better process identifies each feature's actual anomaly risk first and matches the isolation level or mechanism to that specific, demonstrated need.