CSE Roadmap

Core tier · about 3 hours · 7 parts

Part 7 of 7 · 3 min read

Exercises

Easy

Easy 1: Compute the contention ratio (10 minutes)

An event has 5,000 seats and expects 250,000 customers in the first minute of sale. What is the contention ratio?

Done when: you compute 50 to 1 and explain what that number predicts about lock contention.

Hint

divide customers by seats; 250,000 divided by 5,000 is 50.

Easy 2: Correctness versus throughput (10 minutes)

Explain why the naive UPDATE ... WHERE status = 'available' query from part 3 is correct but still a problem under heavy load.

Done when: you explain that it never double-sells a seat, but many transactions still queue behind the same hot rows, hurting throughput.

Hint

correctness and throughput are two separate questions; the WHERE clause answers correctness, not speed.

Easy 3: State one trade-off (10 minutes)

State, in one sentence, what SKIP LOCKED gives up compared to letting a customer pick one exact seat by number.

Done when: your sentence names that SKIP LOCKED suits "any seat in this tier" rather than a specific chosen seat.

Hint

SKIP LOCKED gives up exact-seat choice in exchange for avoiding queued contention.

Medium

Medium 1: Write the claim query (20 minutes)

Write a SELECT ... FOR UPDATE SKIP LOCKED query that claims one available seat for event 900, in the cheapest price tier, locking only that one row.

Done when: your query filters by event_id and tier, orders deterministically, and uses FOR UPDATE SKIP LOCKED LIMIT 1.

Hint

filter on event_id and tier, order by seat_id for determinism, and use FOR UPDATE SKIP LOCKED LIMIT 1.

Medium 2: Size the hold window (20 minutes)

Argue for a specific hold window length, between 2 and 10 minutes, for this ticket system, weighing customer convenience against seat availability.

Done when: you pick a specific number and justify it using both sides of the trade-off.

Hint

a shorter window frees seats faster but pressures slower customers; a longer window is kinder but locks seats away longer.

Medium 3: Release expired holds (20 minutes)

Write a SQL statement that releases any seat whose hold has expired back to available.

Done when: your UPDATE correctly filters on status = 'held' AND held_until < now() and sets status back to available.

Hint

the release query's WHERE clause needs both the status check and the expiry-time comparison together.

Hard

Hard 1: Extend to exact-seat picking (25 minutes)

A customer wants to pick one exact seat from a map, not just "any seat in this tier." Redesign the claim step for this case, and explain the contention trade-off it now accepts.

Done when: you propose a direct SELECT ... FOR UPDATE on that one specific seat_id, with a short timeout and a clear "seat no longer available" response, and explain this reintroduces real contention on a popular individual seat.

Hint

a direct lock on one chosen seat_id accepts that a popular seat becomes a genuine hot row, unlike the SKIP LOCKED pool-claiming approach.

Hard 2: Walk the five steps on a new question (30 minutes)

Apply the same five-step habit, requirements and numbers, first design, bottleneck, deep dive, trade-offs, to a different question: the database behind a flash-sale inventory system where 50,000 customers compete for 500 units of one product.

Done when: you produce a short answer for each of the five steps, naming the contention ratio, a first schema, the hot-row bottleneck, a concrete locking fix, and at least one trade-off.

Hint

treat each of the five steps as its own short paragraph, in the same order used throughout this page.

Hard 3: Defend the single-region assumption (25 minutes)

An interviewer asks what changes if this ticket system must serve customers across three continents with low latency everywhere. Explain what breaks in this design and name one concrete approach to address it.

Done when: you explain that cross-region writes to the same seat rows reintroduce distributed-transaction coordination costs, and name an approach such as a single authoritative region for each event's seat inventory or a consensus-based distributed SQL system.

Hint

cross-region seat contention reintroduces exactly the 2PC or consensus-based coordination trade-offs covered in the partitioning and distributed transactions level.