My business operates on timeslots (appointments) assigned to customers. Each timeslot can have exactly one customer, maximum.
Recently I've been having problems with multiple customers trying to get the same timeslot and I'm not sure how to handle it to cause the least annoyance.
I've tried pessimistic locking where I lock all the available timeslots (7 days worth) until the user picks one, however this really isn't acceptable, since it annoys everybody else who is trying to get an appointment and leaves the website open for abuse.
I've also tried optimistic locking where everybody can see all the timeslots, but only the first user to hit the "schedule call" button get it. Everybody else gets a "sorry, you lose" message and has to go back and try to find a different appointment.
Neither has been good. The first one annoys all the users at once, and the second one annoys just a few users but they get really angry and call up and complain that they couldn't get "their" appointment.
I'm hoping there's a better way to handle this. Does anybody have any suggestions?
I went with @jeff-holt suggestion:
When the user picks a time-slot, it's theirs for 5 minutes. If at the end of 5 minutes, they haven't either confirmed the appointment or have abandoned the page, the time-slot is released back into the pool.
This is done with a cron job calling a MySQL stored procedure that examines the "created" timestamps of all the appointments that are not confirmed, and logging and then deleting those older than 5 minutes.
If the user is actually still there, and tries to confirm the appointment after 5 minutes, they're shown a message that says they took too long.
This has been working nicely and there have been no complaints.