Backend practice series: 1. Requirements and data design · 2. APIs and overbooking · 3. Interview review · 4. Recursion and sorting · 5. SQL exercises · 6. SQL on the E-Learning database
This post continues part 1 (read its sections 2.2 to 2.4 on room_inventory, booking statuses and the DDL first): the API list, preventing overbooking when many people book at once, the Spring Boot plan, and tests that prove there is no overbooking. The hotel system is the instructor’s worked example; the course’s real pet project is E-Learning (endpoints in Designing REST APIs — Exercise 2, schema in SQL exercises on the E-Learning database), and these techniques carry over unchanged.
1. API list
Key idea: nine endpoints under /api/v1, following the shared REST conventions. The two that matter most are search (GET /hotels with query parameters) and booking (POST /bookings with an Idempotency-Key).
Shared conventions (details in Web and REST APIs):
/api/v1prefix, JSON, ISOyyyy-MM-dddates, paginated lists;- errors as Problem Details (RFC 9457): a standard JSON body with
type,title,statusanddetail.
| # | Method | Path | Purpose | Success | Main errors |
|---|---|---|---|---|---|
| 1 | GET | /api/v1/hotels?city=&sleeps=&checkin=&checkout=&rooms=&page=&size=&sort= |
Search hotels with availability | 200 | 400 |
| 2 | GET | /api/v1/hotels/{hotelId} |
Hotel details + room types | 200 | 404 |
| 3 | GET | /api/v1/hotels/{hotelId}/room-types?checkin=&checkout= |
Available room types + nightly prices | 200 | 400, 404 |
| 4 | POST | /api/v1/bookings (header Idempotency-Key) |
Create a booking | 201 | 400, 409, 422 |
| 5 | GET | /api/v1/bookings/{bookingId} |
View my booking | 200 | 403, 404 |
| 6 | GET | /api/v1/users/me/bookings |
Booking history | 200 | 401 |
| 7 | POST | /api/v1/bookings/{bookingId}/confirm |
Confirm (after payment) | 200 | 409 |
| 8 | GET | /api/v1/hotels/{hotelId}/reviews |
List reviews | 200 | 404 |
| 9 | POST | /api/v1/bookings/{bookingId}/review |
Review a stay | 201 | 409 |
Example booking request and responses: 201 Created and a 409 Problem Details
POST /api/v1/bookings
Content-Type: application/json
Authorization: Bearer <token>
Idempotency-Key: 6f1c2e0a-9b7d-4c1e-8f3a-2d5b7c9e1a40
{
"hotelId": 42,
"roomTypeId": 7,
"checkin": "2026-12-01",
"checkout": "2026-12-03",
"quantity": 2,
"couponCodes": ["WINTER10"],
"paymentMethod": "PAY_AT_HOTEL",
"contact": { "name": "Nguyen Van A", "phone": "+84901234567", "email": "a@example.com" }
}
HTTP/1.1 201 Created
Location: /api/v1/bookings/1001
{
"id": 1001,
"status": "PENDING",
"nights": 2,
"quantity": 2,
"originalPrice": 4000000.00,
"fee": 200000.00,
"discountTotal": 400000.00,
"finalPrice": 3800000.00,
"expiresAt": "2026-11-17T10:15:00Z"
}
HTTP/1.1 409 Conflict
Content-Type: application/problem+json
{
"type": "https://api.example.com/problems/room-unavailable",
"title": "Room unavailable",
"status": 409,
"detail": "Room type 7 has fewer than 2 rooms left on 2026-12-02"
}
2. The hardest part: preventing overbooking
Key idea: merge “check a room is free” and “take the room” into one conditional UPDATE over the whole date range. It succeeds if and only if the affected row count equals the number of nights.
Overbooking (selling more rooms than exist) happens when two requests interleave between the read and the write.
UPDATE room_inventory
SET booked_rooms = booked_rooms + :qty
WHERE room_type_id = :roomTypeId
AND stay_date >= :checkin AND stay_date < :checkout
AND total_rooms - booked_rooms >= :qty;
-- Success if and only if the affected row count == number of nights
Why this is safe: in InnoDB, an UPDATE reads the latest committed version of each row and holds a write lock on it until the transaction ends. The second request waits, then re-evaluates the condition against the new data, and updates nothing.
The full flow, including the idempotency key (a key the client sends so the server can recognise a resent request) and coupons:
sequenceDiagram
participant C as Client
participant API as BookingController
participant S as BookingService
participant DB as MySQL
C->>API: POST /bookings (Idempotency-Key)
API->>S: create(command)
S->>DB: SELECT booking WHERE user_id AND idempotency_key
alt already exists
S-->>API: return the existing booking
else new
S->>DB: BEGIN
S->>DB: UPDATE room_inventory ... AND total - booked >= qty
alt rows != nights
S->>DB: ROLLBACK
S-->>API: RoomUnavailableException (409)
else enough rooms
S->>DB: SELECT nightly prices, compute total, claim coupon (UPDATE coupon SET remaining = remaining - 1 WHERE remaining > 0)
S->>DB: INSERT booking (PENDING, expires_at = now + 15 min)
S->>DB: COMMIT
S-->>API: Booking
end
end
API-->>C: 201 Created
Expired PENDING bookings must release their rooms, through a scheduled job (Cron jobs):
- every minute, pick
PENDINGbookings withexpires_at < now(), move them toEXPIREDand give thebooked_roomsback; - the status change is a conditional update too (
WHERE status = 'PENDING'), so several instances running at once can’t release rooms twice.
3. Spring Boot implementation plan
Key idea: package by feature with three layers inside, and build in seven milestones, M0–M6, each with a measurable definition of done.
3.1 Package layout: by feature, layered inside
Key idea: packaging by feature means a booking change only touches the booking folder.
com.example.booking
├── hotel
│ ├── api HotelController, HotelSearchRequest, HotelSummaryResponse
│ ├── application HotelQueryService
│ ├── domain Hotel, Location, RoomType
│ └── infra HotelRepository, HotelSearchRepository (native query)
├── inventory
│ ├── domain RoomInventory, RoomInventoryId
│ └── infra RoomInventoryRepository
├── booking
│ ├── api BookingController, CreateBookingRequest, BookingResponse
│ ├── application BookingService, PricingService, BookingExpiryJob
│ ├── domain Booking, BookingStatus, money rules
│ └── infra BookingRepository
├── coupon ...
├── review ...
└── common GlobalExceptionHandler, ProblemDetail mapping, auditing config
flowchart TB
C[Controller<br/>HTTP, DTO validation, status codes] --> S[Service<br/>use cases, transactions, business rules]
S --> R[Repository<br/>Spring Data JPA, native queries]
R --> DB[(MySQL)]
The dependency rule:
- controller → service → repository, never backwards and never skipping a layer;
- JPA entities never leave the service layer: controllers accept and return DTOs (objects that only carry data across the API);
- lock this in with an ArchUnit test (a library that checks package dependencies while the tests run) instead of relying on code review.
@AnalyzeClasses(packages = "com.example.booking")
class ArchitectureTest {
@ArchTest
static final ArchRule layers = layeredArchitecture().consideringAllDependencies()
.layer("Api").definedBy("..api..")
.layer("Application").definedBy("..application..")
.layer("Infra").definedBy("..infra..")
.whereLayer("Api").mayNotBeAccessedByAnyLayer()
.whereLayer("Application").mayOnlyBeAccessedByLayers("Api")
.whereLayer("Infra").mayOnlyBeAccessedByLayers("Application");
}
3.2 Controller and DTOs
Key idea: the DTO is a record with Bean Validation; the controller reads Idempotency-Key, calls the service and returns 201 with Location. The easiest mistake is which time zone “today” is in.
Code for CreateBookingRequest and BookingController
public record CreateBookingRequest(
@NotNull Long hotelId,
@NotNull Long roomTypeId,
@NotNull @FutureOrPresent LocalDate checkin,
@NotNull LocalDate checkout,
@Min(1) @Max(10) int quantity,
List<@NotBlank String> couponCodes,
@NotNull PaymentMethod paymentMethod,
@NotNull @Valid Contact contact) {
public record Contact(@NotBlank String name,
@NotBlank @Pattern(regexp = "^\\+?[0-9]{8,15}$") String phone,
@NotBlank @Email String email) {}
@AssertTrue(message = "checkout must be after checkin and stay at most 30 nights")
boolean isValidStay() {
if (checkin == null || checkout == null) return true; // let @NotNull report it
long nights = ChronoUnit.DAYS.between(checkin, checkout);
return nights >= 1 && nights <= 30;
}
}
@RestController
@RequestMapping("/api/v1/bookings")
@RequiredArgsConstructor
class BookingController {
private final BookingService bookingService;
@PostMapping
ResponseEntity<BookingResponse> create(@RequestHeader("Idempotency-Key") @Size(max = 64) String idemKey,
@AuthenticationPrincipal AppUser user,
@Valid @RequestBody CreateBookingRequest req) {
BookingResponse body = bookingService.create(user.id(), idemKey, req);
return ResponseEntity.created(URI.create("/api/v1/bookings/" + body.id())).body(body);
}
}
The time zone trap in the DTO above: @FutureOrPresent, like every LocalDate.now(), takes “today” from the JVM’s time zone.
- With the server on UTC, a guest in Vietnam can book a night that has already passed between 00:00 and 07:00 local time; a guest further west gets a valid booking rejected.
- The fix: store a
ZoneIdper hotel and check in the service withLocalDate.now(clock.withZone(hotel.zone()))and an injectedClock, instead of@FutureOrPresent. - Count nights with the half-open convention
[checkin, checkout), consistently across the inventory query, the nights calculation and the price.
The full case study, with a table of real outputs, is in Dates and times in the backend.
3.3 Service: transactions and business rules
Key idea: create runs in one @Transactional: look up the key, reserve, compare rows with nights, price it, save a PENDING booking that expires in 15 minutes. Throwing a RuntimeException rolls back the nights already decremented too.
public interface RoomInventoryRepository extends JpaRepository<RoomInventory, RoomInventoryId> {
@Modifying(flushAutomatically = true, clearAutomatically = true)
@Query("""
update RoomInventory i
set i.bookedRooms = i.bookedRooms + :qty
where i.id.roomTypeId = :roomTypeId
and i.id.stayDate >= :checkin and i.id.stayDate < :checkout
and i.totalRooms - i.bookedRooms >= :qty
""")
int reserve(long roomTypeId, LocalDate checkin, LocalDate checkout, int qty);
}
Code for BookingService.create
@Service
@RequiredArgsConstructor
public class BookingService {
private final BookingRepository bookings;
private final RoomInventoryRepository inventory;
private final PricingService pricing;
private final CouponService coupons;
private final Clock clock; // inject a Clock so time is testable
@Transactional
public BookingResponse create(long userId, String idemKey, CreateBookingRequest req) {
Optional<Booking> existing = bookings.findByUserIdAndIdempotencyKey(userId, idemKey);
if (existing.isPresent()) {
return BookingResponse.from(existing.get());
}
int nights = (int) ChronoUnit.DAYS.between(req.checkin(), req.checkout());
int updated = inventory.reserve(req.roomTypeId(), req.checkin(), req.checkout(), req.quantity());
if (updated != nights) {
// a RuntimeException -> @Transactional rolls everything back, including nights already decremented
throw new RoomUnavailableException(req.roomTypeId(), req.checkin(), req.checkout());
}
List<BigDecimal> nightly = inventory.pricesFor(req.roomTypeId(), req.checkin(), req.checkout());
PriceBreakdown price = pricing.calculate(nightly, req.quantity(), coupons.claim(req.couponCodes()));
Booking booking = Booking.pending(userId, req, nights, price, idemKey,
Instant.now(clock).plus(Duration.ofMinutes(15)));
return BookingResponse.from(bookings.save(booking));
}
}
Things to watch:
@Transactionalonly works through the proxy (the wrapper Spring creates to open and close the transaction). Calling a@Transactionalmethod from inside the same class (this.create(...)) bypasses the transaction.
So the real guard for idempotency is the UNIQUE (user_id, idempotency_key) constraint on the booking table, not the findBy... call at the top of the method.
Making it tighter: claim the key first, the outbox, and evicting the cache after commit
- Claim the key first: flip the order and claim the key with
INSERT ... ON CONFLICT DO NOTHINGbefore reserving inventory, so a retry never reaches the “is there a room left” logic. - Outbox: the confirmation email and the “booking created” event must not be sent inside this transaction. Write them to an outbox table in the same transaction; a separate process reads the outbox and sends them.
- A real bug: Idempotency part 1 analyses exactly this bug in a homestay booking project: a
request_idcolumn without UNIQUE makes a retry report “room already taken” for a booking that actually succeeded. - Evict the cache after commit: once you cache hotel details or room prices (see the interview question on scaling search), deleting the cache entry must happen after commit, not right after
save()inside the@Transactionalmethod. At that point the database hasn’t committed, so a reader that misses reads the old value and puts it back in the cache. Use@TransactionalEventListener(phase = AFTER_COMMIT)or a transaction-aware cache manager; the reasoning and the remaining races are in Cache-aside and keeping the cache consistent with the database.
3.4 The search query and the N+1 problem
Key idea: the list page uses one SQL query that returns DTOs directly and keeps only room types with enough rooms on every night; the details page avoids N+1 with deliberate fetching.
SELECT h.id, h.name, h.rating, l.city, MIN(avail.min_price) AS from_price
FROM hotel h
JOIN location l ON l.hotel_id = h.id
JOIN room_type rt ON rt.hotel_id = h.id AND rt.sleeps >= :sleeps
JOIN (
SELECT room_type_id, MIN(price) AS min_price
FROM room_inventory
WHERE stay_date >= :checkin AND stay_date < :checkout
GROUP BY room_type_id
HAVING COUNT(*) = :nights -- data exists for every night
AND MIN(total_rooms - booked_rooms) >= :rooms -- even the fullest night has enough rooms
) avail ON avail.room_type_id = rt.id
WHERE l.city = :city AND h.status = 'ACTIVE'
GROUP BY h.id, h.name, h.rating, l.city
ORDER BY from_price
LIMIT :size OFFSET :offset;
3.5 Milestones
Key idea: each milestone ends with a checkable criterion; overbooking protection lands in M4 and needs a concurrency test.
| Milestone | Scope | Definition of done |
|---|---|---|
| M0 | Project skeleton: Spring Boot 3, Java 21, Flyway, MySQL via Docker Compose, CI running tests | ./mvnw verify green in CI |
| M1 | Entities + migrations + seed data (1,000 fake hotels) | @DataJpaTest with Testcontainers passes |
| M2 | Hotel details API + shared error handling | BDD Story 2 passes |
| M3 | Search API + indexes + pagination | BDD Story 1 passes, EXPLAIN shows no full scan |
| M4 | Booking API + overbooking protection + idempotency | BDD Story 3 passes, including the concurrency test |
| M5 | Coupons, reviews, booking expiry job | Matching tests pass |
| M6 | Security (JWT), observability (Actuator, Micrometer), load test | Meets the NFRs in section 1.5 of part 1 |
The M0 skeleton (the POM, mvnw, the folder layout, why verify also runs the tests) is covered in the Maven and project layout post.
Before a real deployment, three things remain: check against Twelve-Factor, per-object authorization, and making sure SIGTERM reaches the JVM.
Before a real deployment: Twelve-Factor, authorization, SIGTERM and Linux
Before a real deployment (after M6), check the project against the Twelve-Factor App:
- config and secrets come from environment variables, not
application-prod.yml; - the app shuts down gracefully on SIGTERM (
server.shutdown=graceful, a readiness probe); - Flyway migrations run as a separate Job, not on every replica’s startup.
What M6 doesn’t cover yet:
- A JWT only answers “who is calling”. “Which hotels may this person edit” (roles, permissions, per-object ownership checks) is in Authorization, SSO and OAuth2.
- Graceful shutdown only works if SIGTERM reaches the JVM. Start
javawith the shell form in a Dockerfile and PID 1 issh, which swallows the signal; see Operating systems for backend engineers. - Running the container as a non-root user and rotating logs without filling the disk: see Linux for backend engineers.
4. Testing strategy
Key idea: many unit tests at the bottom, few E2E tests at the top. The most valuable one is a concurrency test on real MySQL via Testcontainers (a library that runs a real database in Docker during tests).
Each kind of Spring Boot test (@WebMvcTest, @DataJpaTest, @SpringBootTest, Testcontainers with @ServiceConnection) is covered in the Spring Boot testing post.
flowchart TB
E2E[E2E / load tests<br/>few, run before release] --> INT
INT[Integration tests<br/>Testcontainers MySQL, @SpringBootTest] --> SLICE
SLICE[Slice tests<br/>@WebMvcTest, @DataJpaTest] --> UNIT
UNIT[Unit tests<br/>PricingService, domain rules: the most numerous and fastest]
| Type | What it tests | Examples |
|---|---|---|
| Unit | Pure logic, no Spring | Pricing: nights with different prices, % coupon, fixed coupon, discount never exceeds the total |
@WebMvcTest |
HTTP mapping, validation, error codes | checkout before checkin → 400 with field errors; missing Idempotency-Key → 400 |
@DataJpaTest + Testcontainers |
Real queries on real MySQL | reserve returns the right row count; search excludes a hotel sold out for one night |
| Integration | The whole flow | Booking → inventory drops; resending the same key → same booking |
| Concurrency | No overbooking | 20 threads racing for 1 room → exactly 1 succeeds |
The concurrency test: 20 threads wait on one CountDownLatch and are released together to race for the last room; assert exactly 1 succeeds and every night has booked_rooms == 1.
Code for BookingConcurrencyIT: 20 threads racing for 1 room
@SpringBootTest
@Testcontainers
class BookingConcurrencyIT {
@Container @ServiceConnection
static MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.4");
@Autowired BookingService bookingService;
@Autowired TestData testData;
@Test
void onlyOneBookingWinsTheLastRoom() throws Exception {
long roomTypeId = testData.roomTypeWithRooms(1, LocalDate.of(2026, 12, 1), 2);
int threads = 20;
ExecutorService pool = Executors.newFixedThreadPool(threads);
CountDownLatch start = new CountDownLatch(1);
List<Future<Boolean>> results = new ArrayList<>();
for (int i = 0; i < threads; i++) {
long userId = testData.newUser();
results.add(pool.submit(() -> {
start.await();
try {
bookingService.create(userId, UUID.randomUUID().toString(),
testData.request(roomTypeId, 1));
return true;
} catch (RoomUnavailableException e) {
return false;
}
}));
}
start.countDown();
long success = 0;
for (Future<Boolean> f : results) if (f.get()) success++;
pool.shutdown();
assertThat(success).isEqualTo(1);
assertThat(testData.bookedRooms(roomTypeId)).allMatch(b -> b == 1);
}
}
Further reading: posts that go deeper into each part of the booking flow.
- Airline booking: holding seats correctly when many people book at once: the same no-double-selling problem, comparing a conditional update,
FOR UPDATE SKIP LOCKED, locking in a fixed order and a job that releases expired holds. - Integrating a VNPay-style payment gateway: the step after a
PENDINGbooking, covering the redirect, signatures and the IPN. - Payments after the integration: late IPNs and reconciliation: an IPN that arrives after the booking has expired, reconciliation and a failure-mode table.
Exercises
Exercise 1: From requirements to three APIs (the main practice task)
Task (paraphrased): For the booking system above, identify the entities and relationships, draw the ERD, and design three APIs: search hotels by location, guests and dates; get a hotel’s details with its room types; and create a booking.
Solution: entities are in section 2.1 and the ERD in section 2.3 of part 1; the APIs are in section 1 of this post (GET /api/v1/hotels with query parameters, GET /api/v1/hotels/{id}, POST /api/v1/bookings). What this adds on top of a minimal answer: per-night inventory, booking statuses, an idempotency key, a conditional update.
Exercise 2 (my own): Extending the requirements with cancellations
Task: Product wants “free cancellation if it’s more than 24 hours before check-in”. Write the BDD scenarios and point out what has to change.
Solution to Exercise 2: cancellation BDD and the changes it drags in
Scenario: Free cancellation
Given a CONFIRMED booking with check-in in 3 days
When the owner calls POST /api/v1/bookings/1001/cancel
Then the status becomes CANCELLED, each night's rooms are released and the coupon use is restored
Scenario: Past the free-cancellation window
Given check-in is in 10 hours
When the owner cancels
Then the response is 422 "cancellation window closed"
Scenario: Cancelling twice
Given the booking is already CANCELLED
When the owner cancels again
Then the response is 200 with the same status (idempotent), and rooms are not released a second time
What changes:
- Add the
CONFIRMED → CANCELLED(andPENDING → CANCELLED) transitions. - The room-release update is conditional on
WHERE status IN ('PENDING','CONFIRMED'), so it only happens once. - “24 hours before check-in” must be computed in the hotel’s time zone, which means adding a
time_zonecolumn to hotel.
A seemingly tiny requirement drags in a schema change: exactly the kind of thing to discover at the spec stage rather than while coding.
Remember
- Explain why check-then-act causes overbooking and why one conditional UPDATE doesn’t.
- Know why the affected row count is compared with the number of nights, and what rolls back the nights already taken.
- Name the real idempotency guard (the unique constraint) and how to handle two concurrent requests with the same key.
- Describe the three layers, the dependency rule, and how ArchUnit enforces it.
- Explain why tests run on real MySQL, and write the 20-threads-for-1-room test.
Interview questions
Junior
Why is the search API a GET with query parameters rather than a POST?
Because search only reads, so it’s safe and idempotent, exactly what GET means; that makes it cacheable and shareable.
Filters go in the query string; resource identifiers (like hotelId) go in the path. POST only makes sense when the filter is too complex or too long for a URL.
Why does the create-booking API return 201 Created with a Location header instead of 200?
201 says a new resource was created, more specific than 200’s plain “success”; Location gives the client its URL.
The Location header carries /api/v1/bookings/1001 so the client can follow it without building paths itself. The body usually includes the created representation, saving an extra GET. When a client resends the same Idempotency-Key, the server can replay the original response (201 with the same body) or answer 200, as long as it’s consistent and documented.
Mid
What is each layer (controller, service, repository) responsible for? Why not return entities from controllers?
The controller handles HTTP, the service handles use cases and transactions, the repository handles data access; returning entities turns your database structure into your API contract.
- Controller: accepts and validates DTOs, calls the service, maps results and errors to status codes.
- Service: use cases, business rules, transaction boundaries.
- Returning entities: rename a column and clients break; lazy-loading errors outside the transaction, JSON cycles on bidirectional relationships, leaked sensitive fields. DTOs decouple the API contract from the data model.
What is an idempotency key and how would you implement it for the booking API?
A unique key the client sends with each booking intent; when the server sees the key again, it returns the original result instead of creating a new booking.
The client generates a UUID and sends it in a header. The server stores the key with the booking under a unique constraint on (user_id, key), so clients can safely retry after a timeout. Two requests with the same key arriving concurrently: the unique constraint rejects the second one, so you catch that error and return the existing booking.
Why test against real MySQL (Testcontainers) instead of H2?
Because the most critical parts of this system (the conditional update, unique constraints, the search query) are exactly where H2 can behave differently from MySQL.
H2 differs from MySQL in syntax, date functions, CHECK enforcement, collations and locking/isolation behaviour. Testcontainers runs the same MySQL version as production, so the tests mean something.
Why might a @Transactional method fail to roll back on an exception?
Usually because the exception is checked, it’s swallowed inside the method, or the call doesn’t go through the proxy.
- Checked exception: by default Spring only rolls back on RuntimeException and Error, so you need
rollbackFor. - The exception is caught and swallowed inside the method.
- The method is called internally via
thisand bypasses the proxy. - The method isn’t public (with proxy-based transactions in older Spring versions).
- The table engine doesn’t support transactions.
Senior
Two people book the last room at the same time. How do you prevent overbooking?
Inventory per room type per night, one conditional UPDATE over the date range, affected rows must equal the number of nights, otherwise roll back and return 409.
- The update:
booked = booked + q WHERE ... AND total - booked >= q. - InnoDB locks each row on update and re-evaluates the condition against the latest version, so the second request fails.
- Add a CHECK constraint
booked <= totalas a safety net. - Alternatives: SELECT FOR UPDATE, optimistic locking with a version column (poor under high contention), or Redis holds at very large scale.
- And you need a concurrency test that proves it.
The service runs as 3 instances. How do you stop the expired-booking job from running twice?
Make the operation itself safe to run twice with a conditional status update, then add a distributed lock if you want.
- Update
WHERE status = 'PENDING' AND expires_at < now()and only release rooms for the rows whose status actually changed, in the same transaction. - Extra layer: a distributed lock such as ShedLock (a lock table in the database) so only one instance runs each cycle.
- Or
SELECT ... FOR UPDATE SKIP LOCKED(skip rows someone else has locked) so instances split the work in batches.
Search traffic grows 100x. How do you scale it?
Measure first, then scale in order of cost: indexes, read replicas, caching, a search engine; bookings still go to the primary database.
- Measure: p95,
EXPLAINplans, cache hit ratio. - The right indexes (city, sleeps, the inventory primary key).
- Read replicas for search: a few seconds of lag is fine because search results are only indicative, and the booking step re-checks availability for real.
- Cache results per parameter set with a short TTL.
- Move search to a search engine (Elasticsearch/OpenSearch) kept in sync via events or CDC.
Useful phrases for interviews
- “Check-then-act is a race condition, so I’d use a conditional update and check the affected row count.”
- “I keep entities inside the service layer and only expose DTOs at the API boundary.”
- “I’d rather enforce the layering with an ArchUnit test than rely on reviewers to catch it.”
- “For this kind of test I use Testcontainers, because H2 behaves differently from MySQL under concurrency.”
- “That requirement looks small, but it actually forces a schema change, so it’s better to catch it at the spec stage.”
- “Search is a safe, idempotent read, so it’s a GET with query parameters and it can be cached.”
- “The expiry job is safe to run twice, because the status transition itself is a conditional update.”
- “If two requests arrive with the same idempotency key, the unique constraint lets exactly one through and the other gets the existing booking.”
Notes based on the Fundamentals of Backend course (Ronin Engineer), rewritten in my own words.