Subject Re: [IBO] Multiple DB Apps on Single FDB file
Author Helen Borrie
At 09:17 AM 7/06/2005 +1000, you wrote:
>I am not sure how IB deals with the following scenario, so I was hopping
>that someone may point me in the right direction.
>I would like multiple instance of the same DB application to gain access to
>a single FBD at the same time so that many users can read the same
>record(s), but only a single user to be able to edit and/or insert new
>record(s) at any point in time, and after the edit/insertion all other users
>be automatically updated with the new/updated record.
>My approach so far has been to set the transaction mode to "Consistency -
>Table Locking" with "No Wait" but unfortunately this is not quite right.
>The only other way I can think of achieving this, is by making the
>transaction "Committed - Read Committed" as well as defining events on the
>FDB which would notify each and every db app that a record(s) has been
>updated/inserted, so that the other instances of the db applications would
>then refresh their dataset.
>Is there is a better more efficient way of achieving this using IBObjects?

Sure is. Pick up the Tech Info sheet on DML Caching.

>i.e. can the transaction component be told to only lock the record(s) which
>is been updated

Records which one transaction successfully posts are locked against updates
by others, anyway. Table locking is totally unnecessary and undesirable
for interactive applications. If Transaction A posts an update,
Transaction B won't be allowed to post another update. The LockWait
setting determines whether Tr. B will wait until Tr. A commits and attempt
to update then, or whether it excepts immediately. Your handling of the
Post operation should deal with this.

If you want to block other transactions from updating a certain record if
Tr. A starts editing the record (but hasn't posted anything yet) you can
use PessimisticLocking. Since this method of pess. locking involves
posting a dummy update as soon as Edit is called, you must take care with
your triggers to ensure that pess. locking cannot cause an update trigger
to fire and give unintended results. Fb 1.5's built-in pess. locking (FOR
UPDATE WITH LOCK) might be useful to use instead, but it does have a lot of
"undesirable issues" when used with datasets.

> as well as having the FDB engine announce the
>update/insertion to every other instance of the application which is
>currently browsing the dataset which contains this particular record(s)?

DMLCaching will take care of the announcing; but, of course, merely
refreshing won't cause an unfinished concurrency transaction to "see" the
outcome of others' commits. The application would need to respond by
committing the concurrency transaction before refreshing. Refresh alone is
OK for ReadCommitted transactions. With concurrency transactions, you'll
need to work out carefully what you want to happen to pending work when the
user gets a DMLCache notification.