At 09:48 AM 12/10/2005 +1000, you wrote:

>Hi Helen,
>
>I have re-read the documentation associated to the Survey demo. I have also
>followed the [Firebird-support] Avoiding pessimistic locking thread. Very
>interesting and helpful.
>
>
>What I am trying to achieve is very similar to what was mention in the
>thread mention above i.e
>
>User A edits record A
>User B and or any other users attempts to edit record A
>
>I would like the application/DB to:
>
>1. Allow any and all user to read any record even if it currently being
>update by some other user.

This is always possible with Concurrency and Read Committed isolation levels.

>2. Stop record A from being edited by user B or any other user (for example
>not allowing the dataset to get into edit mode)

The database engine has no control over what an application allows. One
transaction becomes "aware" of what another is doing only when that
transaction attempts an update, insert or delete, "a DML operation".

That is why achieving what you want to do makes it necessary for the
application to attempt to put a pre-emptive lock a record *before* it
allows the user's attempt to put a dataset into a mode whose expected
outcome is a DML operation - the "dummy update". If this dummy update
fails (because another transaction already has something waiting to commit
for this record) then IBO won't let the dataset go into the requested state.

>3. Notify User B and any other user trying to edit record A that this record
>is currently being edited by some other user.

This is very simple. You can intercept the locking conflict at the
appropriate time and give the user a friendly message.

>4. If User A does not coming within a time period, release the lock on
>record A

You could achieve this by using the timeout properties on
transactions. There is nothing you can do on the server to give clients
the hurry-up.

>5. If User A commits within the allowed time period, notify all other users
>Of the update, i.e automatically update the record they are viewing (which
>is what the Survey Demo does).

Yes, this is where DMLCaching comes in. You have to understand that
DMLCaching activity results from commits. It can't do anything about
uncommitted work.

>Now, from what I have read in your book, the Survey demo doc and the thread
>mention above, this can NOT be achieved automatically using a combination of
>Isolation Rules (please correct me if I am wrong).

You are correct.

>So from what I understand, I must create an "audit trail" which will allow
>me to lock a record when User A begins an update process.

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?

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

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.

>In other words, follow what David Johnson said in the Avoiding pessimistic
>Locking thread on Friday the 7th of October.

From reading David Johnson's postings, I am firmly of the opinion that he
never writes applications designed for multiple users; or, that if he
does, his users must be very tolerant of the bottlenecks he builds into his
systems.

>Would you agree with this?

As a general strategy? Definitely not! As a way to work around an
explicit requirement regardless of the cost to performance, perhaps.

>Or is there a better, more efficient, safer procedure to follow?

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. With IBO, a user's work need not be lost, just
because the first attempt to request the DML results in an exception. A
failed commit doesn't end a transaction: it just keeps everything on the
client side in the same state that is was in before the commit request was
sent. In situations where it is an absolute requirement that the first
user who has the *intention* to update a specific record must win, use
pessimistic locking.

If you want to implement a request-queuing system, with total control of
priority, develop an n-tier system.

Helen