Dealing with complex uniqueness in MongoDB

Question

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?

• Ewan: what the problem with the unique date index and having your update statement check the other fields?
• Matthew Weeks: I'm not sure I follow

Answer 1

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.

• Matthew Weeks: That would include setting a TTL index then, no?
• MicroservicesOnDDD: 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.
• Matthew Weeks: 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!
• MicroservicesOnDDD: @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?
• Matthew Weeks: 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

Answer 2

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)