I'm working on a ticket reservation system that is having some over-booking issues.
The issue that needs to be dealt with here is at a database level.
When this site's traffic is at its peak it can have literally thousands of users simultaneously attempting to reserve the same tour time for multiple tickets at once.
How do you make sure that you don't over-book the tour?
The tour has a limited number of tickets available (e.g. 300 tickets per tour).
Let's say that the last set of 10 tickets are available, but two or more users secure the set at the same exact same time.
Now I have at least one set of 10 tickets over-booked.
The problem with a database insert is I would need to execute a query to see how many tickets are left to see if I should allow both users in on the tour or only one user. By the time the count query is returned, the count will have changed and be invalid.
The current solution is to blindly insert and on exception run an update. This only furthers the issue with over booking.
QUESTION: Is there a way to verify that a tour is still within its ticket capacity fast enough to still be able to secure a reservation without over-booking?