← All blogs

Product engineering

Prevent double bookings: two customers, one slot

Our test ended with two saved bookings for the same court and hour. Both customers had checked availability, and both checks were correct when they ran. The trouble came after that. If you're building a booking product, this is worth trying before launch. At Rubix, we'd want to see what happens to both customers when there's only room for one.

Rubix Labs5 min read
Controlled SQLite test: customers A and B both read the same slot as available. Without uniqueness, both claims commit. With a unique slot rule, A commits and B gets a constraint conflict; reversing the write order makes B the winner.
Actual results from a synthetic test using two SQLite connections and deliberately ordered statements. Both availability reads finish before either insert. This is a reproducible collision example, not a simultaneous-thread or production load test.

Both availability checks were correct

Our fictional venue has one-hour slots. Customer A wants Court A at 20:00 UTC on November 21. Customer B wants that exact slot too. We gave each customer a separate SQLite connection and made both check availability before either tried to save a claim.

At that point, the database had no claims for the slot. Both reads returned available. A then inserted a claim and committed it. B inserted another and committed that too. With no rule preventing duplicate slot claims, the database saved both. Nobody needed to click twice or have a slow phone.

To reproduce that gap reliably, we deliberately ordered the statements across the two connections: read A, read B, write A, write B. The test used Python 3.11.4 and SQLite 3.42.0, with each statement committing automatically. We didn't run simultaneous threads, a web server or a load test.

Give the database a rule it can enforce

For the second version, we kept the same sequence and made slot_id unique and non-null in the claims table. A's insert succeeded. B's insert failed with SQLITE_CONSTRAINT_UNIQUE, leaving only A's claim. Reversing the write order gave B the slot and A the conflict. The first successful write won.

The slot ID matters here. It identifies a specific court and a specific scheduled hour from a server-owned catalog. Two different courts at the same time have different IDs. So do adjacent hours on the same court. Our fixture also used a foreign key, enabled on both connections, to reject IDs absent from that catalog.

A production server still needs to check who is booking, whether the venue offers that slot and whether booking is allowed. A foreign key doesn't answer those questions. But the final insert should enforce the one-claim rule even if an earlier availability response is already stale.

Give the database a rule it can enforce
Synthetic caseObserved result
Same slot, no uniqueness ruleBoth claims saved
Same slot, unique rule; A writes firstA saved; B rejected
Same slot, unique rule; B writes firstB saved; A rejected
Different courts, same hourBoth claims saved
Same court, adjacent fixed hoursBoth claims saved
Missing slot / invented slotRejected by NOT NULL / foreign key respectively

Please don't replace the first booking

We tried one more variation: INSERT OR REPLACE. It sounds convenient when you're trying to make an error go away. In our test, both writes reported success, but the final table contained only B's claim. A's earlier claim had been removed.

SQLite documents that replacement behavior. It's a terrible fit for a new customer trying to take an occupied slot. Keep the existing claim and return a recognizable slot conflict. An upsert that changes the current owner would need the same scrutiny.

All eight cases matched their expected results. That includes the replacement case, where the expected result was deliberately bad: B displaced A. A test report needs to say what it checked; a green tick beside this case would be easy to misread.

The second customer still deserves a useful screen

Once the claim commits, the application can show a booking confirmation. If another customer took the slot, tell this customer what happened and refresh the alternatives. Keep their venue, date and other useful choices. Making them start the whole search again won't improve the evening.

Don't turn every database error into 'Someone else booked it.' A busy database, a failed connection and a uniqueness conflict mean different things. SQLite can report SQLITE_BUSY when it can't obtain the required access. The application needs a separate failure path for that, and should avoid claiming success before it knows the write committed.

Disabling the button helps with repeated clicks in one browser. An idempotency key helps identify a repeated attempt at the same operation. Neither makes two different customers the same request. The slot still needs its own protection. Our retry guide covers the separate problem of finding out whether an uncertain operation already completed.

Fixed slots make this example small

This rule assumes the catalog itself contains non-overlapping slots for each court. If customers can choose arbitrary start and end times, two different IDs may still overlap. A unique ID won't catch that. You need a rule for overlapping intervals, tested against the database and booking model you actually use.

We also left out payments, temporary holds and cancellation. Those need their own state transitions. A payment failure shouldn't leave an unexplained permanent booking; releasing a slot shouldn't erase its history. None of that was tested here. Before launch, we'd exercise the real booking endpoint with independent sessions and check the saved records as well as both responses.

Our team's TikiTaka work connected sports-facility discovery with bookings and owner management. You can explore the public booking demo to see that kind of journey. It's a reconstruction with sample data, not the original platform or a concurrency test. The database experiment in this article is new and separate.

If you've got a booking product in mind, bring Rubix the awkward rules too. Can an owner block a court while someone is checking out? How long does an unpaid reservation last? We can help work through those decisions and build the customer flow and database checks around them.

Rubix Labs

A few finishing touches.