Making Booking Retries Safe

We coordinate booking submissions and cancellations by booking identifier. A shared lock orders operations for the same booking, while a transaction groups the related database changes. Together, these controls let us give repeated requests a clear inventory rule.

Booking requests can arrive more than once. A client can retry after a timeout, or a user can repeat a submission while waiting for a response. Those requests can represent one booking intention.

The rule is simple: repeating that intention must not consume another room. Cancellation needs the same care because it reverses the inventory effect.

This article explains that design with synthetic examples. The diagrams describe possible request sequences, and the room counts illustrate expected behavior.

1. Give the booking intention a stable identifier

Idempotency means that repeating an operation does not repeat its effect. For booking submission, the effect is creating a booking and taking rooms from inventory.

We use the booking identifier to recognize repeated submissions. A retry carries the same identifier even when it arrives as a new HTTP request. A request identifier can help trace one attempt, but does not necessarily identify the booking intention across attempts.

The state of the existing booking determines the response.

Submit with booking identifier
  |
  +--> active booking exists
  |      --> return its booking identifier
  |      --> preserve inventory
  |
  +--> inactive booking exists
  |      --> reject this submit
  |      --> preserve inventory
  |
  +--> no booking exists
         --> create booking and deduct rooms together

This design treats an identifier as one booking intention. It does not reactivate a cancelled booking just because a request arrives again.

2. Coordinate the check and the change

Checking for an existing booking is necessary, but concurrent requests also need coordination. The following hypothetical sequence shows why.

Two uncoordinated requests for the same identifier

Request A: check --> absent --> create
Request B: check --> absent --> create

Both checks can finish before either creation completes.

We use a MySQL named lock for the booking identifier. A named lock gives cooperating database sessions exclusive access to work under the same lock name.

Request A: lock --> check --> deduct + create --> commit --> unlock

Request B: wait
             |
             +--> acquire lock --> find existing booking --> return ID
             |
             +--> acquisition timeout --> return error

MySQL associates named locks with a database session. Committing or rolling back a transaction does not release the named lock. MySQL locking functions

That gives the connection a specific responsibility. It must remain the same database session through acquisition, booking work, and release.

We give lock acquisition a bounded wait. If the lock is unavailable when that wait ends, the operation returns an error without starting its booking transaction. The acquisition timeout does not limit the duration of work after the lock is acquired.

3. Give the lock and transaction separate jobs

The lock coordinates operations for one booking identifier. The transaction groups the database changes that belong to one operation.

A transaction commits its changes together or rolls them back when the operation fails. The booking row and its inventory deduction therefore belong inside the same transaction. That assumes the participating tables and database operations support the intended transaction behavior.

The operation follows this order.

1. Reserve one connection from the pool.
2. Acquire the named lock for the booking identifier.
3. Start a transaction on that connection.
4. Check the existing booking state.
5. Return an active booking, reject an inactive booking, or create the booking and deduct rooms.
6. Commit successful work or roll back failed work.
7. Release the named lock if it was acquired.
8. Return the connection to the pool after cleanup.

The cleanup path uses finally blocks. A finally block runs when control leaves its associated try block, including when an operation throws.

Explicit lock release remains part of cleanup even after a successful commit. Connection-pool behavior also needs verification because returning a connection does not necessarily end its database session.

4. Coordinate cancellation through the same boundary

Submission and cancellation affect the same booking and its inventory. They therefore use the same lock-naming rule.

Once cancellation holds the named lock, its transaction locks the matching booking rows and checks their states. Only active booking rows contribute an inventory release. An already-cancelled booking does not release rooms again.

The inventory update uses database arithmetic. It increments the stored room count instead of replacing it with a value previously read into application memory.

For example, the essential update has this shape. The table and column names are synthetic.

UPDATE demo_room_availability
SET available_rooms = available_rooms + 1
WHERE id = 42;

The increment and booking-state change remain inside the cancellation transaction. They must agree when the transaction commits.

The following example assumes one booking reserves one room for one night. These are expected values, not measurements from a customer booking.

Start:              five available rooms

First submit:       5 --> 4
Repeated submit:    4 --> 4   existing booking ID returned
First cancellation: 4 --> 5
Repeated cancel:    5 --> 5   already-cancelled response

The first and repeated cancellations can return different responses. Their inventory effect remains idempotent because the room is returned once.

5. Verify concurrent behavior with a real database

Mocked database calls can check method ordering. They cannot establish how the database coordinates independent connections.

A useful integration check runs concurrent operations against the same database engine and transaction path used by the application. It inspects both the returned responses and the stored effects.

Use these checks for the design.

1. Submit the same booking concurrently from separate connections.
2. Confirm one booking effect and one inventory deduction.
3. Cancel that booking concurrently from separate connections.
4. Confirm one inventory release.
5. Submit again with the cancelled booking's identifier and inspect the rejection.
6. Hold the lock from another connection and verify acquisition timeout behavior.
7. Trigger a transaction error and inspect rollback and lock cleanup.
8. Repeat with the deployment's connection pool and proxy settings.

These are verification steps, not results claimed by the synthetic example. The expected outcome includes stored state, inventory, connection cleanup, and caller responses.

6. State the assumptions behind the design

Every coordinated operation needs a stable booking identifier. All participating writers must follow the same lock-naming rule and reach the same database server. MySQL named locks coordinate within one server; they do not provide a distributed lock across independent servers. MySQL locking functions

Database constraints have a separate role. A unique constraint rejects duplicate values at the database boundary. Its key must reflect the application's booking lifecycle and permitted records. Choosing an application lock does not remove that schema-design decision.

The lock described here coordinates one booking identifier. Two different booking identifiers competing for the last room require separate inventory coordination. Calls to suppliers or payment services also need their own retry and reconciliation rules.

The design starts with an effect we want to preserve. A stable identifier recognizes the intention, the lock orders its operations, and the transaction groups the related writes. Cancellation follows the same boundary because it reverses that effect.