How to design a booking system with high concurrency for customer services?

Question

I'm building a booking system for customer services and I got stuck when handling double booking of an employee (that will do the service) for a certain given time. I have looked into multiple related questions but they all explain concurrency solutions for a room, seat, product etc that can be booked or updated. In our case the customer books a service for a time slot that is made available by having at least one free employee at that given time. Let me explain...

Our database now currently looks something like this (other tables and fields have been omitted for simplicity):

An employee is available for a given time if that time doesn't overlap with a row in Unavailable for that employee. Furthermore, we prevent double bookings by adding a constraint to the Unavailable table that prevents inserting new rows that overlaps in time for the same employee.

How can we allow customers to book the same time slot for a service as long as there are available employees for that time while having high concurrency and avoiding to serialize the whole table for such transactions?

Would it be easier to add a new table representing the time slots and if so how would such design work to achieve optimal concurrency?

Answer 1

The problem

So the problem is that Transaction 1 and transaction 2 will read the same available employees list and then wait for the previous transaction to commit, however transaction 2 will not see transaction 1s booking insert.

Alternative 1

One solution to the problem would be to split up the selects into three different sqls.

Select * from employees FOR UPDATE. This will make the other transaction 2 suspend untill transaction 1 is finished inserting.

Then do a query for unavailable and booked: SELECT employee FROM unavailable WHERE start_time<?2 and end_time>?1) SELECT employee FROM booked WHERE start_time<?2 and end_time>?1

Remove the ids from those two queries from all fetched employees. Then insert a booking for the first employee in the list of employees that is left. Once commited transaction 1 will now see the newly inserted booking and thus the available employee list will not include the employee that Transaction 1 booked.

Transaction 1	Transaction 2
read employee	read employee
read bookings	suspend
read unavailables	...
insert booking	...
commit	...
	read bookings
	read unavailables
	insert booking
	commit

Alternative 2

Another solution would be to add a timeslot table, where intervals for the services are defined, and instead of using for update on select all employees ( Which grabs a write lock for every row in the employees table).

Select the time the booking is refering too in the new timeslot table and lock that specific time row, thus bookings overlapping in time will have to wait for each other whilest bookings that dont overlapp in time can be booked concurrently.