Re: Anyone have a trigger solution?

Well,

[Quoted] [Quoted] (a) At least one fix is easy: if you want to check overlaps, do not use the [Quoted] [Quoted] count(*) method, but the package function I posted before (i.e. use a cursor [Quoted] that selects overlaps and halt on the first sucessfull fetch).

[Quoted] [Quoted] (b) Of course this "available dates" table can be replaced with a proper index system on your main table that does exactly the same.

[Quoted] (c) Lost updates can be avoided with a SELECT FOR UPDATE. Unfortunately, [Quoted] this will wait until able to grap the stuff. You could alternatively use a "soft lock", i.e. an indicator field (typically holds the user ID) that you [Quoted] [Quoted] test and eventually update. And not wait of course, but signal the user that [Quoted] someone else has grabbed the obejct of desire. This is fairly [Quoted] straightforward to implement: if empty, you set the indicator (with a commit [Quoted] of course) before you do anything else and you empty it after you commit or [Quoted] rollback your actual work. Of course, if you DO set the indicator, you also [Quoted] SELECT FOR UPDATE, so that applications that are unaware of your softlock can't mess around with your data.

HTH, dirkske.

"Eric Givler" <egivler_at_flash.net> wrote in message news:JSH86.6058$J%.609351_at_news.flash.net...
> (originally posted in comp.databases.oracle.server)
>
> We've an application at work that has a number of problems with
> duplicate/overlapped reservations for the same site. This was written by
 an
> outside vendor, and now we're attempting to clean up their mess. We'll
 call
> this a "double-booking issue".
>
> The design of the application is to allow the public to reserve facilities
> at our state parks (in PA). Each facility,ie. a campsite, cabin, private
> dock, etc, has a date range of availability. We call that a season. A
> season has an open and close date. So, each site has a season. Once the
> seasons are established for all sites, the dba runs a utility called
> populatedates. This places an entry into a table for EVERY SITE, for
 EVERY
> DAY that it is available. The table is very large, and we remove old data
> out of it when it gets over 2 months old.
>
> What makes the table even larger is that some sites allow multiple
> reservations per day: ie. WhiteWater Boating. This has 8 timeslots a day,
> and up to 60 people per slot. So, if the season is from may through
> september, you can 7*60 * # days in season rows in this table! WOW!!
>
> Anyway, each time a reservation is entered in the system, an entry is
> created into another table called: reservations. The reservation has an
> arrival date and a departure date and # of people in the party. The
> corresponding rows in the "availability dates" table have a flag that now
> contains the reservation# to show that they are "booked".
>
> Unfortunately, the brilliant programmers used this method to check whether
 a
> site was open:
>
> select count(*) into Kount
> from available_dates
> where site_id = my_site
> and reservable_date between arrival_date and departure_date
> and reservation_id is null;
>
> if kount = 0 then
> update available_dates
> set reservation_id = my_res_id
> where site_id = my_site
> and reservable_date between arrival_date and departure_date
> and reservation_id is null;
> end if;
>
> -- there's other code that differentiates for the # of people in the party
> based on the site type.
> The problem occurs in that two users can attempt to grab the same site.
 The
> one simply WAITS on the other. When the 1st person commits, the second
 one
> then overwrites his work. OOPS! This isn't good.
>
> The second one overwrites the first because it sees the reservation_id has
> not been populated by the first one yet (it is still null on the read -
 the
> select statement). The update statement that followed just goes into a
 wait
> state until the first session commits. The first session would have had
 to
> COMMIT the data prior the second one doing the update, then the second
> session would see the row was changed,and the update would fail
> (reservation_id would be not null). You can verify this real quick in
> SQL*Plus as the second session will simply wait for the first session to
> COMMIT the data. At that point, it will perform it's update and overwrite
> the original.
>
> I probably didn't spell this out, but the updates to this table are done
 in
> forms, but the table that is updated is not tied to a forms block - it's
> just an update statement. So, we don't get the standard ORacle error
> "Record changed or deleted by another user" or the "Attempting to reserve
> record error".
>
> I was tempted to write some select statements with a for update on the end
> and if I couldn't get the rows that I wanted, then I would think someone
> else is trying to do something with the rows that I want. BUT, there are
> SEVERAL avenues in the system that allow this activity. It's going to
 take
> a while to cleanup the number of screens that are "miscoded".
>
> So....
> 1. Is this "availability dates" table really needed? (It's used
 throughout
> the entire application now which consists of over 100 screens and 45+
> reports, so it will be difficult to remove in the NEAR FUTURE).
>
> 2. Should I attempt to encapsulate all calls that reference this table
 into
> a package and make that the interface to this "cumbersome" table?
>
> 3. Should I also use the "select for update" to initially make sure the
 site
> is available for all days in question?
>
> 4. Should I make use of some form of triggers? (not sure what without
> preventing a mutating table) that I would capture the site#, start, end
 and
> # of people, then after the statement is processed, check for an
 overlapping
> reservation in the system, or check that the # of people in the party
> matches the # of rows "reserved/flagged" in the database?
>
> Any suggestions are appreciated!! This is one of the bigger problems in
> this system, and I'd like to get it fixed ASAP.
> Right now, my approach is #2 and #3.
>
> I was somehow thinking that #1 combined with #4 would also work, possibly
> still using a package though.
>
>
>
Received on Mon Jan 15 2001 - 21:38:20 CET