Subject Re: [ib-support] Transaction question
Author Doug Chamberlin
At 11/22/2002 09:03 AM (Friday), Nico Callewaert wrote:
>User A opens a query and has 1 record displayed on his screen, the
>record stays let's say 1 hour on his screen.
>In the meantime user B opens the same record, changes it, posts the
>datset and commits the transaction.
>The data on the screen of user A remains unchanged, because there
>was no refresh done. Then user A changes the same record, posts
>the dataset and commits the transaction.
>I suppose the record that user B changed will be overwritten ?

It depends on whether user A maintains an open transaction while the data
is being displayed. It also depends on the transaction isolation levels in
effect.

If user A wants to lock out user B's change then user A will have to
maintain an open transaction for the whole hour and will have to perform an
update on the record as soon as it is obtained. Commonly, a dummy update is
done to lock the record (i.e. UPDATE TABE1 SET FIELD1=FIELD1 WHERE...).

I think for user A to maintain an open transaction for an hour is just bad
design. Better to design in an application level mechanism for user A to
detect that user B has changed the record before user A saves their
changes. In my databases I have a standard field in all tales named
Revision. It is an integer field which gets incremented by a trigger on
every insert and update. it is easy for the application to query the
current revision of the same record before applying an update and alert the
user that someone else changed the record since they acquired the data.