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