Double Booking: How an EXCLUDE Constraint Eliminates Race Conditions in Reservation Systems

Introduction

Double booking is one of the most expensive bugs a reservation system can ship. It silently turns satisfied customers into angry ones, erodes trust, and forces support teams to untangle manual refunds and rebookings. The root cause is usually not a careless developer but a classic concurrency bug: a race condition between two simultaneous transactions that both check availability and both decide that a slot is free.

A recent engineering post on Habr describes exactly this scenario. The team behind a booking platform noticed that under high load, the same resource — a room, a table, or a time slot — was being assigned to two different clients. Their investigation led them to a textbook race condition, and their fix was elegant: a PostgreSQL EXCLUDE constraint that makes double booking structurally impossible at the database level. This article examines their findings, explains the underlying concepts, and compares the solution with alternative approaches.

Source

The Race Condition: Why Two 'Free' Checks Both Return True

Imagine a simple booking flow. A client requests a reservation for Tuesday at 14:00. The application runs a SELECT query to check whether any existing booking overlaps that time slot. If the query returns no rows, the application inserts a new booking.

The problem appears when two clients request the same slot at nearly the same instant. Both transactions read the table before either of them writes. Both see no conflicting booking. Both proceed to insert. The application-level check is not atomic, and the gap between the check and the insert creates a window of vulnerability. In database terminology, this is a non-repeatable read or a write skew, depending on isolation level. Under the default READ COMMITTED isolation level, each statement sees a fresh snapshot, but the two INSERT statements do not block each other because they are not writing the same row.

The result is a logical corruption of the data model: the room is booked twice, yet the constraint 'one booking per room per time interval' is silently violated. The developers in the article describe how they only detected the issue after a customer complaint, because the application logs looked harmless — every individual transaction was internally consistent.

Traditional Prevention Strategies and Their Limits

Several techniques are commonly used to prevent double booking. Each has strengths, but also blind spots.

Application-Level Locks

Some projects implement a mutex or a distributed lock around the check-and-insert sequence. For example, Redis SETNX or a PostgreSQL advisory lock can serialize access to a specific room. This works in a single-node deployment, but introduces external dependencies and is easy to get wrong. Lock keys must be perfectly aligned with business objects; a mismatch means the lock protects nothing.

Unique Indexes on Booked Slots

If bookings are for discrete slots, such as hourly intervals, a unique index on (room_id, start_time) prevents duplicates. However, real-world bookings are often continuous ranges. Two bookings, 14:00–15:00 and 14:30–15:30, do not share a start_time but they do overlap. A unique index simply cannot express 'these ranges must not intersect'.

Serialization via Transactions

Using SERIALIZABLE isolation level forces the database to abort transactions that cannot be serialized. This works, but implementing it correctly requires retry logic and is expensive under heavy load. Many teams start with SERIALIZABLE and later discover that aborts are frequent, forcing them to either rewrite application code or fall back to weaker isolation levels.

EXCLUDE Constraints: Declarative Conflict Prevention

PostgreSQL offers a more direct tool: exclusion constraints, declared with EXCLUDE. An exclusion constraint is a rule that says 'no two rows may conflict according to these operators'. It is evaluated not on equality of a single column, but on the relationship between a combination of columns.

For a booking system, the constraint can be written as:

CREATE TABLE reservations (
    id integer PRIMARY KEY,
    room_id integer NOT NULL,
    during tstzrange NOT NULL,
    EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

Here WITH = on room_id means that two rows concern the same room, and WITH && on during means that the time ranges overlap. The constraint therefore rejects any insert or update that would create overlapping bookings for the same room. The database evaluates this constraint atomically, using a GiST index. When two concurrent transactions try to insert overlapping rows, one of them is blocked until the other commits, then it is aborted with an exclusion_violation error.

To make this work for scalar types like integer, the btree_gist extension must be enabled, because GiST indexes do not natively understand equality on integers. The article notes that the developers had to remember to run CREATE EXTENSION btree_gist; before the constraint would function correctly.

What the Article's Authors Discovered

The Habr article describes a practical debugging journey. The developers first suspected an application-level bug — maybe a callback that created bookings twice, or a retry mechanism that duplicated requests. Log inspection showed nothing unusual. Only by reproducing the scenario with two parallel database sessions did they trigger the double booking right in front of them.

Their fix was not to add more application code, but to remove the responsibility from the application entirely. They added the EXCLUDE constraint to the existing reservations table. After deployment, the database itself rejected overlapping inserts. The application was updated to catch the exclusion_violation error and respond to the second client with 'this slot is no longer available'.

An important detail they emphasize: the constraint is only as good as the data it checks. If two different client portals write to the same table through different applications, the constraint protects all of them, regardless of whether those applications were written carefully. This is a decisive advantage over application-level locking.

Comparison of Approaches

Practical Considerations for Production Use

Exclusion constraints are not a silver bullet. Several details matter in production.

Infinite ranges and empty ranges. PostgreSQL ranges support 'infinity' and 'empty' values. An empty range could allow an insertion that appears to overlap nothing. The article recommends validating ranges before insertion and, if necessary, using a CHECK constraint to prohibit empty ranges.

Partial exclusions. If the business allows overlapping bookings for a 'premium' tier or for different resource subtypes, a plain EXCLUDE constraint would be too strict. In that case, a partial exclusion constraint can be created by adding a WHERE clause, for example only excluding overlaps when status = 'confirmed'. Pending bookings can then coexist, and a background job resolves them.

Deferred constraints. By default, EXCLUDE constraints are checked immediately after each statement. For some workflows, like bulk imports or complex transaction sequences, it is convenient to make the constraint DEFERRABLE INITIALLY DEFERRED. This allows overlapping rows to exist within the same transaction as long as they are resolved before the transaction commits. However, deferring reduces the early detection of errors and can lead to larger rollback on failure.

Handling the error in the application. The error code for a violated exclusion constraint is 23P01 (exclusion_violation). Any robust application should map this to a user-friendly message. The developers in the article added a RETURNING clause to their INSERT statement to distinguish success from conflict and then sent an appropriate HTTP 409 response.

Why This Matters for System Architecture

The lesson goes beyond booking systems. Any domain that enforces rules on intersecting ranges or mutually exclusive pairs — resource scheduling, event invitations, IP address allocation, machine-learning feature stores — can benefit from declarative constraints. Adding such constraints at the database layer makes invariants impossible to break, even if a future developer bypasses the service layer or writes a one-off SQL script.

The developers in the article also point out a secondary benefit: debugging becomes easier. When a double booking is impossible, a lot of log-level noise disappears. Support tickets about overlapping reservations become database errors rather than arguments about transaction timing. In their case, the EXCLUDE constraint did not just fix a bug; it changed how they reasoned about correctness. They trusted the database to maintain a hard invariant, allowing the application code to focus on user experience and edge cases.

Conclusion

Double booking is a canonical software failure that illustrates why application-level checks are insufficient under concurrency. The Habr article's authors have provided a clean, practical case study: detect a race, locate the absence of a database-level invariant, and close it with an EXCLUDE constraint. Their experience confirms that PostgreSQL's exclusion constraints are a first-class mechanism for enforcing complex business rules.

For teams building reservation systems on PostgreSQL, the recommendation is straightforward. Model time intervals with tstzrange or daterange, add a GiST-based EXCLUDE constraint on the resource ID and the time range, and handle the resulting exclusion_violation error in the API layer. This approach shifts correctness into the database, removes a whole class of race conditions, and scales far better than distributed locks. The next time a support agent reports a 'mysterious double booking', the answer may already be embedded in the schema.

As the material in the original article demonstrates, sometimes the most effective fix is not more code, but less ambiguity in the data layer. For any developer dealing with overlapping intervals, reading the full case study is well worth the time.