Layer 3 · Go deeper · 6 min

Go deeper

Size it, build it, then make the calls an interviewer will push you on.

Build-up · 5 steps

Watch two bookings both succeed

Two sessions run the same check-then-insert code on PostgreSQL at REPEATABLE READ. Only the time range and the name differ.

Step 1 of 5

The bookings table holds nothing for room R1 between 10:00 and 12:00. The rule is simple, no two bookings of a room may overlap.

4 interview moments

The interviewer pushes on the hard parts. Pick an approach and see how it holds up.

Deep dive 1 of 4
Interviewer
“Asha and Ravi book overlapping slots in room R1 at the same moment, on PostgreSQL. How do you stop the double booking?”
Your move. Pick an approach:
Deep dive 2 of 4
Interviewer
“Same rule, but on MySQL, so there’s no EXCLUDE constraint. How do you stop overlapping bookings?”
Your move. Pick an approach:
Deep dive 3 of 4
Interviewer
“Each user may have at most 3 active sessions. Two logins for the same user race. What enforces the limit?”
Your move. Pick an approach:
Deep dive 4 of 4
Interviewer
“Bookings are now sharded by booking id, or moved to a key-value store. How do you keep a slot from being booked twice?”
Your move. Pick an approach:

Build-up · 4 steps

PostgreSQL has nothing to lock when no row matches. InnoDB at REPEATABLE READ locks the empty space itself.

Step 1 of 4

InnoDB locks index records. This primary key holds 80, 95, 130 and 170, and a supremum pseudo-record marks the end.