1Show the Level 1 solution
2**A.**
31. **Lost update** (or a check-then-act race) on the seat counter.
42. **Non-repeatable read.**
53. **Phantom read** (the check-then-insert race).
64. **Deadlock.**
75. **A distributed lock expiring while still held**; fencing tokens would have stopped the stale write.
89**B.**
101. **Atomic update**: `SET seats_left = seats_left - 1 WHERE seats_left > 0`.
112. **Unique constraint** on (event, seat), or a conditional update from free to held.
123. **Optimistic locking** with a version column.
134. **Pessimistic locking** (lock both rows, in a fixed order) inside one transaction, or an atomic conditional update on the sender plus an update on the receiver in the same transaction.
Show the Level 2 solution
1. 1 ÷ 0.05 = **20 bookings a second**, at most, for that event. Lock per seat or use an atomic update.
2. 100 × 0.3 = **30 seats** come back after 10 minutes. With 30-minute holds, they'd sit unsellable for **30 minutes** instead of 10.
3. 3 prepare messages + 3 commit messages = **6** (plus 6 replies).
4. At most 100 can succeed, so about **900** attempts fail in the first round and retry. Optimistic locking is a poor fit here.
1Show the Level 3 solution
2Tested solution (Python 3), the key parts:
34```python
5def atomic_decrement(db, n_requests):
6 sold = 0
7 for _ in range(n_requests):
8 cur = db.execute("UPDATE events SET seats_left = seats_left - 1 "
9 "WHERE id = 1 AND seats_left > 0")
10 sold += cur.rowcount # 0 when sold out
11 db.commit()
12 return sold
131415class VersionedRow:
16 def __init__(self, value):
17 self.value, self.version = value, 1
1819 def compare_and_set(self, expected_version, new_value):
20 if self.version != expected_version:
21 return False # someone changed it since you read it
22 self.value, self.version = new_value, self.version + 1
23 return True
242526def find_deadlock(waits_for):
27 for start in waits_for:
28 seen, node = [], start
29 while node in waits_for:
30 if node in seen:
31 return seen[seen.index(node):]
32 seen.append(node)
33 node = waits_for[node]
34 return None
353637def run_saga(steps):
38 done, log = [], []
39 for do, undo in steps:
40 try:
41 log.append(do())
42 done.append(undo)
43 except Exception as e:
44 log.append(f"failed: {e}")
45 for u in reversed(done): # compensate, newest first
46 log.append(u())
47 return False, log
48 return True, log
49```
5051All checks pass: the lost update leaves 99 instead of 98; the atomic update sells exactly 100 of 130 attempts; the stale optimistic write is rejected; the deadlock cycle is found; Asha's expired hold goes to Ravi; and the failed saga refunds, then releases. The full file is in `code/ch17_transactions.py`.
1Show the Level 4 solution
2**The bug:** check, then insert, with nothing stopping another request in between. It's a **phantom** problem: the check looks for rows that don't exist yet.
341. Asha's request runs the `SELECT`: no booking for seat 14.
52. Ravi's request runs the same `SELECT`: still none, because Asha hasn't inserted yet.
63. Asha's request inserts her booking.
74. Ravi's request inserts his booking. Both return `True`. Seat 14 is sold twice. The tested demo reproduces exactly this.
89**The fix:** make double booking **impossible at the database level** with a unique constraint, and just try the insert:
1011```sql
12ALTER TABLE bookings ADD CONSTRAINT one_booking_per_seat UNIQUE (event_id, seat);
13```
1415```python
16def book(db, event, seat, user):
17 try:
18 db.execute("INSERT INTO bookings (event_id, seat, user) VALUES (?, ?, ?)",
19 (event, seat, user))
20 db.commit()
21 return True
22 except sqlite3.IntegrityError: # someone else got there first
23 db.rollback()
24 return False
25```
2627However the requests interleave, only one insert can succeed. (Alternatives: lock the seat row with `SELECT ... FOR UPDATE` before checking, or use Serializable isolation and retry on serialization failures.)
1Show the Level 5 (sketch) solution
21. **Data model:** `seats(event_id, seat, status, user_id, hold_expires_at, version)` with primary key (event_id, seat). **Hold:** `UPDATE seats SET status='held', user_id=?, hold_expires_at=now()+interval '10 min' WHERE event_id=? AND seat=? AND (status='free' OR (status='held' AND hold_expires_at < now()))`; one row updated means you got it.
32. **Coordination:** an orchestrated **saga**: (1) hold seat; (2) charge ₹499 with an idempotency key (Chapter 24); (3) mark sold and issue the ticket. If payment fails: release the hold. If ticket issuing fails: retry it (it's idempotent); if it keeps failing, refund and release. The confirmation email goes last.
43. **Expiry:** a job every 30 seconds sets expired holds back to free; the hold query above also treats expired holds as free, so it's correct even if the job lags.
54. **Load at 10:00:** a **waiting room** queue that admits users at a controlled rate (Chapter 43), per-user limits on holds, rate limits against bots (Chapter 25), a cached seat map (eventually consistent) for display, with the conditional update as the source of truth.
65. **Isolation and locking:** Read Committed, with **atomic conditional updates** per seat (no hot single row); the payment step uses an idempotency key; no long transactions span the user's payment time, which is what the hold is for.