Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1036
  • Last Modified:

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?
0
BillyBoJimBob
Asked:
BillyBoJimBob
  • 4
  • 2
2 Solutions
 
MarkusIdCommented:
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
 
themdxCommented:
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
 
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?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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?
0
 
MarkusIdCommented:
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
 
BillyBoJimBobAuthor Commented:
MarkusId: I am working out the code for this and will get back to you as soon as I can.
0
 
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.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now