I am creating a booking system that will allow users to make a reservations for whole days. When a user wants to initially make a reservation, they select the day(s) and then will have 10 minutes to fill out the rest of their information.

How I achieved this was having a booked field and a lockedUntil field so that other reservations could not be made on the same days as long as there was a booked reservation or one with lockedUntil in the future.

Now as I am using mongodb, in order to enforce uniqueness I had been checking for any conflicting reservations before inserting. I just recently realized the race condition if 2 reservations were to come back with no conflicts, and then both get inserted. Obviously this is a big no-no.

My question boils down to, what is the optimal way to enforce uniqueness of dates, with maintainability in mind?

What I had attempted to implement was a unique index on the dates, so that in the case where there is a race condition, it would at least be blocked at the database level. This seemed fine until I realized that I can't handle reservations that aren't yet booked, but are just locked with lockedUntil. Now I could set a TTL index to delete these reservations, but that would result in up to a 60 second window where unique constraints would fail. Also this would prevent us from being able to see reservations that weren't completed which could possibly be valuable.

Perhaps there's a better way to achieve what I want. I had thought of using transactions, but my concern is if it is controlled at the application level, one reservation might slip by and end up with a double booking in the database.

Is there another approach I might not be thinking about? Could there be a better way to control the locking of reservations?

  • what the problem with the unique date index and having your update statement check the other fields?
    – Ewan
    Commented Jan 14, 2020 at 8:52
  • I'm not sure I follow Commented Jan 14, 2020 at 14:07

2 Answers 2


If you put a unique index on reservation-date, that should do it, along with following something along the lines of what follows. As soon as the user starts editing the record and enters a reservation date, you insert a temporary reservation-holder record into MongoDB to prevent others from starting to reserve the same date. If they try to enter a date into the reserve-date field and that date is already reserved, they are not allowed to keep that date in the reserve-date field. Once they enter a date successfully, if they change the date, you have to delete the placeholder, and reinsert a new one into your MongoDB. If ten minute reservation time limit expires, and they haven't completed the reservation, the reservation-holder (record) gets removed from the MongoDB. If they complete the reservation, the reservation-holder record gets "completed" -- transformed into a permanent reservation.

  • That would include setting a TTL index then, no? Commented Feb 18, 2020 at 14:04
  • Yes, that's a perfect use of a TTL index, as long as you can put up with the 60-second plus delays that can occur, and also keep in mind the other restrictions. Commented Feb 18, 2020 at 15:28
  • As I said in my original post, I am aware of this possibility, but was hoping for something different because we might want statistics on uncompleted bookings. Thanks anyway! Commented Feb 18, 2020 at 16:38
  • @MatthewWeeks -- I'm sure that you can use TTL and still get statistics on uncompleted bookings. How about a trigger on deletion? Wouldn't that do it? Commented Feb 18, 2020 at 16:56
  • That would work I suppose, but triggers only work using MongoDB stitch on MongoDB atlas. I'd rather not limit myself to only be able to use these services Commented Feb 18, 2020 at 17:08

You probably want a side table with all the days listed (search for how to create a date dimension on how to create that)

Then you could

create table reservations(dayid int, user varchar(100), lockeduntil datetime)
update reservations
set lockeduntil = SYSDATE(), user = "Miller"
where dateid = 01012021 and lockeduntil IS NULL

As per ACID's atomicity (sp?) guarantee, this will run or it will not. (the contention loser will return success but update no rows)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.