oracle, sql pl/sql, ticket over-booking

Posted on 2008-10-23
Last Modified: 2013-12-19
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?
Question by:BillyBoJimBob
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2

Expert Comment

ID: 22794225
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).

Assisted Solution

themdx earned 100 total points
ID: 22794308
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.

Author Comment

ID: 22796582
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?
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 22799027
A trigger for either the insert and/or update might work, but how would you do it and still be able to synchronize transactions?

Accepted Solution

MarkusId earned 400 total points
ID: 22808262
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.

Author Comment

ID: 22851284
MarkusId: I am working out the code for this and will get back to you as soon as I can.

Author Closing Comment

ID: 31509547
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.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question