At 01:07 PM 12/10/2005 +1000, you wrote:

> > No. It's not clear what you mean by "when User A begins an update
> > process". Do you mean "when User A puts a dataset into Edit mode"? or
> > "when User A posts an UPDATE request to the server"? Do you understand
> > the
> > difference?
>
>Sorry for my inappropriate terminology.
>
>I meant.... to lock the record at the App level, so that when User A puts a
>dataset into Edit mode, the "Dummy Update hack" kicks in so that when
>another user (call it) B tries to place her/his dataset in Edit mode for
>record A, their "Dummy Update Hack" will kick in, causing the DB to throw an
>exception which is pick up by the App and regurgitated as a nice message for
>User B.

...so far, so good...

>Thus, User B is not allowed to modify record A until she/he has
>viewed the updated record, therefore having to wait for the update to be
>posted and committed by user A( or rollback if timeout).

...well, this would probably be a reflection of the case if you refreshed
user B's dataset after the commit, e.g. in response to a message from the
DML Cache. But it's a bit cock-eyed. If user B's transaction is in Read
Committed isolation, it can see commits immediately they happen, even if
user B's buffers have not been refreshed.

>Yes, I understand the difference thanks.

It will help to realise that a pessimistic lock works both ways. If user A
is the first to select Record A for editing, then user A's dummy update
will succeed and other users won't be able to get that lock. If another
transaction has already posted a dummy update, and/or has posted but not
committed some actual changes (or a delete), User A's dummy update will
fail and an exception will be returned.

> >
> > >If User B then
> > >decides to update the same record the App will deny such right, as well
> > as
> > >notify her/him that the record is currently being updated by some other
> > >User, etc.

In fact, if you don't intercept the conflict exception, and reframe the
message, the user will get the database message, in all its beauty and glory.

> >
> > Well, you *could* do this, but there is no "must" about it; and it has
> > serious disadvantages in situations where there are more than a couple of
> > users all potentially hitting the same table simultanoeously.
>
>
>Not sure I follow, are you stating that the entire table would be lock?

No. David's solution involved having a gateway table. Gateway tables are
fine for situations where serialization is critical, but they are a real
bottleneck, too.

>I am only interested in locking Record A, and not the entire table
>containing record A.

You never get table locking in Firebird in Read Committed and Concurrency
isolations. You *can* get some very bizarre set-locking if you try to use
the nefarious FOR UPDATE WITH LOCK in a multi-row set.

> >
> > For two-tier systems, I don't know of a safer, more efficient way to deal
> > with this than to just go with the isolation behaviour and handle the
> > conflicts meticulously.
>
>Exactly what I was trying to achieve, but here is where my original problem
>begins.
>
>I set the IBO Transaction Isolation property to Committed(Read Committed)
>and the IB_Query PessimisticLocking to true.
>
>The result was that when User A and B are viewing a record A, and User A
>places her/his Dataset into Edit mode "for an update" (no other user is
>editing Record A).
>
>I get the following error:
>
>ISC ERROR CODE: 335544569
>ISC ERROR MESSAGE:
>Dynamic SQL ERROR
>SQL error code =-510
>Cursor not updatable.

You would get this with a non-updatable set (joins, group bys, union,
etc.). With this kind of set, having RequestLive achieves nothing. You
need custom SQL for inserts, edits, deletes AND pessimistic locking. Could
you have missed the LockSQL property?

If the set has the wrong KeyLinks (or no KeyLinks) pessimistic locking
won't work, either. For a "plain" select statement, with correct KeyLinks,
IBO's automatic LockSQL is to perform an update that sets the keylinks
fields to their own value. You'll get the same problem if you the
KeyRelation technique to make a non-updatable set updatable and the
KeyLinks are not valid.

>So I am not even able to implement the "Dummy Update Hack" as the very first
>user which tried to place the Dataset in Edit mode gets the error mention
>above.

It looks as though we're looking at a different problem here!

>I have also tried the above scenario with IBO Transaction Isolation property
>to Concurrency(repeatable read) and the IB_Query PessimisticLocking to true.
>However get the same error.

For the same reason.

I won't be answering any more list questions today. I have two important
deadlines to meet today.

Helen