Thanks for your response! I've couple of supplementary questions, but first a
bit more info about our database needs.

- We have an external file that is pointed to be a field in a table record.

- Tables contain other metadata associated with the external file.

- The application allows the user to open/edit the external file --
analogous to a editing a document in a Word processing program.

- During that time, changes in the metadata (or the external file for that
matter) by other users shouldn't be allowed as it will cause problems.

- Editing is an ongoing option that is incremental and could take hours.

Now for my concerns about the dummy-update approach.

Let's say Open Shape starts with a dummy update (I don't mention "begin
transaction"
below because under ODBC the update starts a transaction, but I don't think
using an explicit SET TRANSACTION would make a difference)...

User1::DummyUpdate
User1::ReadShapeData
.
.
. User2:DummyUpdate - Fails, as it should.
.
User1::COMMIT
<== User3:DummyUpdate - Succeeds.
User1::DummyUpdate fails. .
. .
. .

Now any attempt by User1 to do a COMMIT will fail, unless User3 does a
ROLLBACK I suppose. In other words, User1 opens a shape, does some
mods, and, if the timing is just right, loses access to the shape in the middle
of an editing session.

Additional issues...

- Under ODBC there is just one transaction on a connection; User1
could inadvertently COMMIT this operation, without subsequent
DummyUpdate, while COMMITing a different shape or
updating a completely un-related table. (Does having a connection
per shape sound like a good way around this?)

- Once you have a shape open, you have an active transaction which
may persist minutes, hours, or even longer, depending on user
behaviour.

Is my thinking completely off the wall?

Regards,
Lee

On 27 Jun 2009 at 13:06, Alan McDonald wrote:

> > 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
>
>
>