Part 7 of 7 · 3 min read
Exercises
Easy
Easy 1: Name the anomaly (20 minutes)
Name the anomaly in each story: a report shows money missing during a transfer; two buyers each get half of one car's paperwork; two clients both add one to a counter and it rises by one; two doctors both go off call.
Done when: four names with a one-line reason.
Hint
Think of what was seen or lost in each case.
Easy 2: Which level (20 minutes)
Say which of read committed and snapshot isolation prevents: dirty reads, read skew, write skew.
Done when: a three-row table.
Hint
Snapshot isolation fixes read skew but not write skew.
Easy 3: Fix with an atomic update (20 minutes)
Rewrite "read stock, subtract one, write" as one SQL statement that also refuses to go below 0.
Done when: a statement and one sentence on how it fails safely.
Hint
UPDATE ... SET stock = stock - 1 WHERE id = ? AND stock > 0, then check the rows changed.
Medium
Medium 1: Visibility (30 minutes)
Row R1 (balance 500) was created by txid 5. Txid 8 started before txid 9. Txid 9 changed the balance to 450 and committed. Txid 8 reads. Txid 10 starts after 9 committed and reads. Fill in created_by and deleted_by for both rows and say what 8 and 10 see.
Done when: a table and two read results.
Hint
An update creates a new row and marks the old one deleted.
Medium 2: Pick the prevention (30 minutes)
Choose atomic update, FOR UPDATE, automatic detection or compare-and-set for: a view counter, a game move with rules, a wiki edit, a seat reservation counter. Give a reason for each.
Done when: four choices with reasons.
Hint
Use atomic updates whenever the change fits one statement.
Medium 3: Write skew with numbers (30 minutes)
A rule says the total of two linked accounts must stay at least 0. Both hold 70. Two withdrawals of 100 each run concurrently under snapshot isolation. Show both checks pass and the final total. Then show a fix using locks.
Done when: a timeline, a final total and a locking fix.
Hint
Both transactions see a total of 140 in their snapshot.
Hard
Hard 1: Room booking (45 minutes)
Design a booking table that prevents double booking: with a unique constraint on slots, and as an alternative with materialised conflicts. Compare rows, locks and failure behaviour, and give the SQL for the booking of 10:00 to 11:00.
Done when: two designs, SQL and a comparison.
Hint
A unique constraint on (room, slot) rejects the second insert.
Hard 2: Isolation for a shop (45 minutes)
A shop has these transactions: place order (check stock, reduce stock, insert order), cancel order, daily sales report, price change. For each, choose an isolation level or safeguard, and list the anomalies you accept.
Done when: a table of four transactions with level, safeguard and accepted anomalies.
Hint
Reports suit snapshot isolation. Stock changes need an atomic check.
Hard 3: Find the bug (50 minutes)
A system lets users claim a limited coupon. The code reads the number of claims, checks it is under 1,000 and inserts a claim row. It runs under snapshot isolation. Show how 1,010 claims can pass, and give three fixes with their costs.
Done when: a timeline for the overshoot and three fixes with costs.
Hint
The count is a premise that the insert changes, so it is a phantom.