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