oracle, sql pl/sql, ticket over-booking

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?
Who is Participating?
What about a 'before insert' trigger on the bookings table that's updating the tour record table like the following (attention, it's a kind of pseudo-code):

Select the tour_capacity and the tour_total_reserved + 1 (let's call this x now)

WHILE x <= tour_capacity LOOP

  Update the tour-record with tour_total_reserved = x
   where tour_total_reserved = x - 1; -- update only, if the selected booking number is lower than the most recent one.

  IF SQL%ROWCOUNT = 0 THEN -- if the record was not updated, increase the booking number and try again
    x = x + 1;
   exit;   --otherwise exit the loop


IF x > tour_capacity THEN -- the update didn't succeed due to the booking number bigger than the tour capacity
  raise_sql_exception (-20201, 'That tour is already fully booked')  -- you got to catch this in the front-end

This should ensure, that you can't insert in the bookings table as long as the tour-record is not updated. On the other hand, it ensures in the tour record, that only one get's a ticket at one time.
What about a parameter table, where you have a counter and get a number with an immediate commit afterwards before inserting in the tickets table? You have to reorganise the parameter-table on a regular basis, though (in case, somebody fetches a number from the parameter-table but does not insert into the ticket table then for any reasons).
You should use the database trigger BEFORE UPDATE ON table. It verify that a tour is still within its ticket capacity. If the conditions is not satfified, it will raise an application error code, in the front-end can catch this error code and show message for customer.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

BillyBoJimBobAuthor Commented:
MarkusId: In the tour table, each tour record already has a tour_capacity and a tour_total_reserved field.  An update trigger automatically updates the tour_total_reserved field as new reservations are booked to that tour.  That is effectively covering your parameter table suggestion.  

themdx: Using a trigger or a simple query on remaining count would have the same basic problem: being able to restrict total tour reservations to a maximum of the available tour capacity.

1) How do you query the remaining quantity of a cookie jar that has hundreds of hands in it 2) and still be able to verify that there's a cookie or two left for you to get 3) and still be able to get that leftover cookie before someone else snatches it first?

How do you synchronize transactions to allow the first user to the reservation time slot to get them and to disallow any other users from getting the same reservation time slot if the capacity for that specific tour has already been reached?
BillyBoJimBobAuthor Commented:
A trigger for either the insert and/or update might work, but how would you do it and still be able to synchronize transactions?
BillyBoJimBobAuthor Commented:
MarkusId: I am working out the code for this and will get back to you as soon as I can.
BillyBoJimBobAuthor Commented:
I have decided to use a stored procedure rather than a before insert trigger for this solution.  Triggers don't allow a value to be returned so with a stored procedure I will be able to return a newly generated reservation id if there is enough tickets left and a zero if no tickets are left

Using a single stored procedure instead of a series of separately run sql statements will ensure that the reservation transaction is synchronized and the first person to the last set of tickets will get them and everyone else will be rejected.  This will hold true even if 1000 users attempt to get the last 5 tickets at the same time.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.