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

Ok

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

"Dummy Update" hack is just what I had in mind after reading the Docs and
chapter 27.

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

Cool

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

Not until the law allows for a keyboard to zap users.

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

True

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

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

Yes, I understand the difference thanks.

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

Not sure I follow, are you stating that the entire table would be lock?
I am only interested in locking Record A, and not the entire table
containing record A.

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

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.

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.

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.

So I am not sure what I am doing wrong.

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

Exactly what is required.

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

Version 2 :-)

Thanks daniel