Solved

oracle, sql pl/sql, ticket over-booking

Posted on 2008-10-23
7
903 Views
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?
0
Comment
Question by:BillyBoJimBob
  • 4
  • 2
7 Comments
 
LVL 9

Expert Comment

by:MarkusId
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).
0
 
LVL 1

Assisted Solution

by:themdx
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.
0
 
LVL 1

Author Comment

by:BillyBoJimBob
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?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:BillyBoJimBob
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?
0
 
LVL 9

Accepted Solution

by:
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;
  ELSE
   exit;   --otherwise exit the loop
  END IF;

END 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
END IF;

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.
0
 
LVL 1

Author Comment

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

Author Closing Comment

by:BillyBoJimBob
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to take different types of Oracle backups using RMAN.
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.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now