Hello,

I am resoponsible for the development of an legacy app under Delphi. Shortly I migrated this app from BDE to IBO using IBO's TDataset-compatiple components (TIBODatabase & co) with following settings
Isolation = tiCommitted
RecVersion = False
LockWait = False

The application design is somewhat outdatet, dating from the area of desktop databases:

- FormCreate
TableX.Open;
TableX.Locate(Idx); //extremly fast, i know
TableX.Edit;
- Showing Form with dataaware controls.
- BtnOKClick
TableX.Post;
TableX.Close;

In a multi-user environment we experiencing the following problem:
User 1 opens dialog
User 2 opens dialog for the same idx as user 1
User 2 edits, saves (and autocommits)
User 1 edits, saves (and autocommits)
Result: Changes of user 2 are overwritten. Under BDE we got an exception when User 1 tried to post his changes. So that I could handle this. Under IBO this exception is gone (I was not aware of this, using the BDE to IBO - migration-guide).

We are looking for a short-term solution. Redesigning the whole app is not possible in an acceptable timeframe, because the application is huge. I searched in the IBO-newsgroup and read IBO-FAQs and found the following possible solutions:

SOLUTION 1: Use of pessimistic locking (IBO FAQs)
==========
TIboTable.PessimisticLocking := True;
Solves the problem. We get an exception if an other user has already opend the form for this item.

Disadvandtages:
- many unnecessary locks (even if the user opens the edit-dialog only for checking some values)
- adventages of Firebirds optimistic-locking possibilities are not used
- we would have to rework many of our dialogs because we are expecting the "deadlock"-exceptions at the Post-Command not at the Edit-command

SOLUTION 2: Use Concurrency-Isolation (IBO FAQs)
==========
Db.Isolation := tiConcurrency;
(After start of a transaction one sees no changes in data)
Solves the problem. We get an exception when user 1 posts ("update conflicts with concurrent update").

But this has a serious disadvandtage in regard of our application design using TDataset-compatible IBO-Components: With the following construct that has widespread use throughout the program...
ShowList
TableX.Open; //Table with filter (other seems to work(?) - not fully tested)
DoSomething
TableX.Close;
BackToList
...the content of TableX is never refreshed (i think this is because the physical transaction is never commited). So if user 1 edits an item that user 2 has changed after the Edit of user 1, user 2 gets an error message, as we would like to have. But if user 1 now discards his changes and gets back to the list the list ist not updated (seems that no physical commit occures). If user 1 reselects the item to try again, the item is not updated too. If he Edits and chooses OK (Post) he gets the same error message as before, even if he does not change anything (because his buffer data is outdatet). This way he is in an endless loop. (A possible workaround for this is to call TableX.IB_Transaction.Close after every Cancel - but surely this is no good design).

Other disadvandtages:
- if the user does not edit anything, his data gets never updated - (workaround: many manual commits?)
- unforeseeable problems if data is not updated
- more exceptions througout the application
- tiConcurrency is unrecommended for browse- and edit-apps (IBO-FAQ)

POSSIBLE SOLUTION 3: BufferSynchroFlags of bsfBeforeEdit
==========
is no solution for our problem, because this is checked at the time when Edit is called...
"bsBeforeEdit can be used to ensure that the user attempts to edit the
latest copy of the record - just in case someone else has altered the
record making the buffer copy of the record out-of-date."
...we would need this functionality at the time when Post is called.

POSSIBLE SOLUTION 4: DMLCaching (IBO-newsgroup)
==========
..not sure about this. But seems to be quite an afford with many database-modifications and
with other resulting problems: events are necessary -> firewall-problems

POSSIBLE SOLUTION 5: Record-Generation-Id (IBO-FAQ)
==========
From FAQ: "[...] use a trigger to increment a "generation" ID on a
record. That way, if a user changes something and you have the generation ID as
a part of the key, IBO can detect the change and refuse them the ability to
post the record."
Not tested until now: So I would have to add an extra row gen_id for each table together with a trigger that autoincrements the generation-Id with every edit. (generators needed??) Moreover I would have to edit the KeyLinks property of each table to include gen_id? So I would get an exception when trying to post.

At the moment this is our preferred way to go because this seems to have the fewest side-effects.


Now my questions:
1.) It seems to me after reading the newsgroup, that this overwriting-problem is a relatively common problem. Perhaps there exists a simpler solution I did not find?
2.) What solution would you choose?

Many thanks

J. Hennig