Ann, is this pseudo code correct for your gateway pattern?

Begin TX
Update ROOM_TABLE.RESERVER to unique id of reserver
Insert record into RESERVATION_TABLE
Update ROOM_TABLE.RESERVER to null
Commit TX

-----Original Message-----
From: Ann W. Harrison [mailto:aharrison@...]
Sent: Wednesday, January 12, 2005 12:28 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Re: Locking tables


nagypapi wrote:

>
> Let's say my table is full of conference room reservations, each
> record having a starttime timestamp and duration (or endtime

timestamp)

> Now if I let out a room I have to check if the requested time period
> conflicts with any records in the table And only after this check can

> I insert the record

Locking the table is killing a fly with a sledge hammer. You've got a
data range problem - lets find a data range solution.

Were it up to me, I'd have a table of rooms and a table of
reservations. The reservations table would probably have a room number,

a day, a start time and an end time.

I'd have triggers on the reservations table that check for and reject
updates and inserts that overlap room, date, and time period. I'd write

my application so it expects to handle that triggered error on the
insert/update. I'd probably have stored procedures to identify the
conflicting reservation and suggest other rooms that were available at
the same time or other times for the requested room.

If you don't want to handle errors (why?) leave those triggers - they're

your backup protection. Create a gateway for your reservations that you

can block as needed. In this case, you'd put a field in the room table
that says it's in the process of being reserved. Before you even start
to look, update the appropriate room putting your user name in that
field. That operation may fail, in which case you rollback and retry
until the update succeeds. You've locked the gate.

Put a trigger on the reservations table that checks that the room is
being reserved by the current user. That will keep others from changing

that room's reservation until your transaction finishes.

Then check for overlapping reservations and continue as you had been.

Regards,


Ann





Yahoo! Groups Links