Skip to content
marten.ackermann
DE
← All articles

Race Conditions in Booking Systems: Conflict Detection with Symfony 7

Published · Marten Ackermann

Short answer: You prevent double bookings with a transaction that runs an overlap query before the INSERT. Whether that is race-condition-free depends on the database: SQLite serializes writes on its own, while PostgreSQL and MySQL additionally need locking, such as SELECT FOR UPDATE.

The problem

In a room booking system I built as a work sample with Symfony 7 and Nuxt 4, the most interesting requirement looked harmless: a room must never be double-booked.

Sounds trivial — until two requests arrive at the same time. Both check “is the room free?”, both get “yes”, both book. A classic race condition between check and insert.

The overlap query

Two time ranges overlap exactly when one starts before the other ends — and vice versa:

SELECT * FROM booking
WHERE room_id = :room
  AND cancelled_at IS NULL
  AND start_at < :newEnd
  AND end_at > :newStart

This single condition covers every case: full containment, partial overlap at the start, at the end, and a new booking that completely wraps an existing one. No catalogue of special cases required.

In the Symfony project this logic lives as findOverlapping() in the BookingRepository — with eight unit tests just for the edge cases (adjacent slots, identical times, cancelled bookings).

Why this is enough on SQLite

The demo runs on SQLite in WAL mode. SQLite allows exactly one writer at a time — write transactions are serialized. A transaction that first runs findOverlapping() and then inserts can never be overtaken by a second write transaction. Check-then-insert is race-condition-free here, with no explicit locking.

Why it is not enough on PostgreSQL/MySQL

PostgreSQL and MySQL allow concurrent writers. Two transactions can run the overlap query simultaneously, both see no conflict, both insert. The options:

  1. SELECT FOR UPDATE at room level — the transaction locks the room row; the second request waits. Simple, robust, sufficient for most systems.
  2. Exclusion constraint (PostgreSQL only) — EXCLUDE USING gist on (room_id, tstzrange(start_at, end_at)) moves the guarantee into the database. The strongest solution, but PostgreSQL-specific.
  3. A unique index? Does not work — overlap cannot be expressed as a simple constraint.

A plain unique index is out because an overlap is not an equality. That is the core reason booking conflicts are an architecture topic, not a schema detail.

What I take away

The conflict strategy belongs in the decision log, not in a code comment. The project documents the trade-off explicitly: “SQLite-specific — migrating to PostgreSQL/MySQL requires SELECT FOR UPDATE at room level.” Whoever scales the system later knows immediately where the assumption lives.

The full project — Symfony 7 REST API, Nuxt 4 SPA, 42 PHPUnit tests, Docker setup — is described in the booking system case study.