Tim wrote:

>>Thinking about it a bit more, I'd use gateway approach - relying purely
>>on triggers leaves the possibility of undetected simultaneous
>>inserts/updates that would not be detected.
>>
>>The update of the gateway records should be in the same transaction as
>>the insert/update of the reservation records.
>>
> What's a gateway record?

Firebird (Interbase) is designed to run without the need to 'lock'
records, as that always causes problems that also then have to be
handled. Because no one can 'lock' another person out, we have
transaction control that prevents two people updating the same record.

If a record has been modified, but not committed, then no other user can
modify that record until the modifications are committed or rolled back.
One way to achieve 'locking' is to write a dummy update to a record (
the gateway record ) and not commit (or roll back ) the dummy update
until you are finished processing on all the records flagged in that
way. So in the case of room booking, you flag the room(s) you are
interested in, make the changes and commit. If the commit fails, then
you can recover the situation by re-reading the records and checking
that a real conflict now exists or simply post the change. It works
nicely for me in my room management system :)

Personally every time I see 'Locking tables' I think 'another mug' -
Firebird's methods of working allow much tidier operations than simply
blocking people doing their work. I have various methods of controlling
who is updating information, even as far as a simple 'user' flag. If the
'last modified by' field is negative, the record is currently 'locked'
by -(user) and other users see that by a highlight and an additional
warning if they try and edit it. They also know who to contact if there
is a problem that can't wait as the user is identified ;)

The statement 'explicit locking seems more appropriate and flexible' is
simply wrong. Locking will never be more appropriate, there will always
be a more appropriate way of doing the same thing ! The problem with
Java (and PHP) interfaces is that some of the inherent flexibility of
Firebird is lost in the name of compatibility. I seem to remember the
same problem with something called BDE ;)

--
Lester Caine
-----------------------------
L.S.Caine Electronic Services