How to Prevent Double Bookings in a Reservation App

Start with the business invariant: one active reservation may occupy a particular room during a given interval. State whether setup and cleanup time also block the room, which statuses consume availability, and whether staff can deliberately override capacity. If an override is allowed, give it an explicit workflow instead of silently weakening the rule.

A room is not the same as a room type. If the business owns three interchangeable rooms, either allocate a specific unit or model shared capacity deliberately. A rule for one unit cannot simply be copied to a class with twenty seats. Capture those resource and capacity rules in writing before the database design is chosen.

At the first instant, Request A reads “available.” Before A writes, Request B reads the same answer. A then inserts its reservation, followed by B. Each individual request may be correct when viewed alone; the interleaving is the problem.

Keep an availability check for a helpful user experience, but do not treat it as permission to write without further protection. The final reservation operation must arbitrate competing requests atomically. Disabling the button only reduces repeat clicks in one browser; it cannot coordinate another customer or another server.

If the product offers predefined, indivisible slots, identify a reservation by resource and slot. Enforce that only one active allocation can exist for that pair. The database must reject a conflicting insert even if both requests observed availability earlier.

Model cancellation carefully. A permanent uniqueness rule over all historical reservations would prevent rebooking a canceled slot. Options include a separate active-allocation table or a partial unique index for the states that consume capacity. PostgreSQL documents partial indexes; the predicate must match the application's real lifecycle.

Catch the specific conflict and return a normal business response such as “That time was just reserved; choose another.” Do not hide unrelated database failures behind the same message. After a conflict, refresh availability and preserve the customer's other entered information.

A unique start time is insufficient when one booking spans 2–3 p.m. and another spans 2:30–3:30 p.m. Their start times differ, but their occupied intervals overlap. PostgreSQL supports range types and exclusion constraints that can express non-overlap for a resource. Its range-constraint documentation includes a reservation example.

One implementation can combine resource equality with overlap of a timestamp range, using appropriate GiST operator support. Define whether the interval includes its endpoints. A half-open interval includes the start and excludes the end, allowing an appointment ending at 3 p.m. to meet another starting at 3 p.m. when no buffer is required.

Validate nonempty intervals, require an end after the start, and include operational buffers in the occupied interval. Test the exact database schema and version you deploy; this design explanation is not a substitute for a migration reviewed against your existing data.

When a rule depends on several reads and writes, one approach is to lock a stable resource row, check active reservations, and write the new reservation within the same transaction. Every competing writer must follow that protocol. Locking only matching reservations is insufficient when there are no matching rows to lock.

Keep the transaction short and use a consistent lock order when reserving several resources. PostgreSQL's locking documentation explains row locks and deadlocks. If using serializable transactions instead, implement bounded retries for serialization failures. Merely wrapping a check and insert in a default-isolation transaction does not automatically prevent overlap.

A database constraint remains a useful final protection when it can represent the invariant. Relying only on an application convention requires confidence that admin tools, imports, and future code all obey the same convention.

Checkout may need a temporary hold so the customer can complete payment. Store its identity, owner, expiry time, and status. Both active holds and confirmed reservations should consume the appropriate capacity. A countdown displayed in the browser is not the authority for expiration; use the server's decision.

Do not assume a uniqueness rule stops counting a row simply because the clock passes its expiry. Move expired holds out of the active state through controlled cleanup or a reservation transaction that safely releases them. A time-dependent index predicate is not a substitute for managing that lifecycle.

Resolve confirmation and expiration under the same concurrency protection. If cleanup and a payment callback both act on the hold, only an allowed transition should win. The application must know whether the resource is still allocated before declaring the customer confirmed.

Do not keep a database transaction open while the customer enters card details or an external payment service responds. Create a short-lived hold first, then perform the payment workflow outside that transaction. On the verified result, atomically confirm the valid allocation according to your rules.

Decide what happens if payment succeeds after the hold has expired and another customer has reserved the resource. Options depend on the payment flow: recheck availability, offer an alternative, or arrange a void or refund with visible exception handling. Never silently promise the original slot just because money moved.

Protect the payment callback against repeated delivery as well. Our Stripe webhook guide explains how event-level deduplication and business-state protection work together.

A sequence of two requests is not a concurrency test. Use two independent database connections or application clients, synchronize their start, and attempt to reserve the same resource and interval. Wait for both to finish, then inspect persisted state rather than checking only HTTP responses.

For a single-capacity resource, expect one successful active reservation and one conflict. Also verify that the losing request did not create an orphaned payment intent, confirmation email, or partial related record. Repeat enough times to exercise different interleavings without claiming a passing sample proves every possible schedule safe.

Try identical intervals, a partially overlapping interval, one interval entirely inside another, and adjacent appointments. For the declared half-open rule, adjacent appointments should be allowed unless a setup buffer makes them overlap. Repeat with different resources to ensure the protection is not broader than intended.

Cancel an active reservation and confirm that the resource becomes available while its history remains accessible. For rescheduling, make the release of the old allocation and acquisition of the new one atomic where your design permits. If the new slot conflicts, the customer should retain the original booking rather than lose both.

Trigger expiry and confirmation concurrently using a controllable test clock or explicit expiry data. Verify one coherent outcome. If the payment succeeds but the hold cannot be confirmed, the exception must enter the resolution workflow instead of disappearing into logs.

Store reservation instants consistently and retain the business's scheduling time zone where needed for display and recurrence. Test daylight-saving transitions, including local times that occur twice or do not occur. A timestamp format alone does not decide how recurring local appointments should behave.

Give repeated submission of the same booking attempt a stable request identity so network retries can return the existing result. Keep this separate from the resource-conflict rule: the same customer may legitimately create a different booking later.

It improves visibility but does not make two writes atomic. A request may be based on information that became stale just before submission. The final reservation operation still needs protection.

Only if its constraints, locks, isolation, and retry behavior implement the intended rule. A default transaction containing an unprotected read followed by an insert can still race with another transaction.

Use a capacity model. For example, atomically reduce remaining capacity only when enough seats remain, and record the allocation in the same transaction. Cancellations and retries must restore or consume capacity once. A non-overlap constraint for a single room is not the right model for every booking product.

If intentional overbooking is a business requirement, define permissions, customer communication, and a recorded reason. Accidental exceptions created by an admin import are not a substitute for that design.

DevConex helps translate scheduling rules into application and database behavior that can be tested. Discuss your reservation workflow before concurrency and payment edge cases become customer-service problems.