> Hello all,
>
> Our CAD application uses a Firebird 2.0 back end to manage its data.
> Previous releases have used the embedded version, and we are now in the
> process of implementing support for shared access through a
> client/server
> configuration.
>
> We're hoping that in general Firebird's MVCC will be sufficient to our
> needs,
> but there is one key use case that doesn't quite fit the mold:
>
> - User opens a shape;
> - User performs a series of discrete editing operations on it, each
> of which leads to an immediate database update;
> - User closes the shape (directly or by exiting the program).
>
> Given the following, it seems appropriate to grant the user exclusive
> read/write access to the shape (i.e., row) from open to close:
>
> - Once the shape is open a failure to save an edit would confuse and
> annoy the user;
> - There is no natural way in which the editing operations could be
> interleaved with others;
> - Immediate update (with a history-based rollback facility) is an
> important feature of our product.
>
> A couple more constraints: Ours is an MDI application (where the above-
> mentioned shapes are the "documents"), and we're currently using ODBC
> rather than the Firebird API.
>
> We've been considering using WITH LOCK and COMMIT RETAINING, with
> a "hard" COMMIT on close, and I've prototyped a table-based DIY locking
> mechanism.
>
> Finally, the question: What is the best approach to this problem?
>
> Thanks in advance for any assistance.
>
> Regards,
> Lee

I wouldn't use commit retaining or WITH LOCK.
The ODBC drive still has access to transaction isolation settings. So you
should test the use of transaction isoltion on an edit. You start by trying
a dummy edit. If you fail then someone else is editing. If you succeed
(tested in exception block) you continue editing/ When finished you know you
can commit so you comit your edits and the other dummy edit in the same
transaction.
You can even test the case of making intermediate commits followed by the
same dummy update. Other people in the mean time who try to start with the
dummy update will fail and you raise a message telling them that someone
else is editing this shape.
Read the Firebird Book for more details.
Alan