Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


oracle, sql pl/sql, ticket over-booking

Posted on 2008-10-23
Medium Priority
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 400 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?

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


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 1600 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

718 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