At 08:38 AM 24/07/2003 +0200, you wrote:

>Alternatively, if you use Fb 1.5, you could take advantage of its new
>pessimistic locking capability. Issueing a
>
>select * from table where id = :id with lock
>
>should get you all the benefits of pessimistic locking without firing
>the update triggers.

Oh no, oh no.

To begin with, the syntax is
SELECT .... (specification)
...
...
FOR UPDATE WITH LOCK

** and ** if you do a select * and get a scrolling dataset (as e.g. an
IB_Query), then EACH of the rows is locked at the moment it is fetched and
stays locked for the duration of the transaction, whether or not you
actually edit it.

The golden rule about *avoiding* pessimistic locking unless you absolutely
need it not only holds fast, but is amplified a zillion times for this new
syntax. FOR UPDATE WITH LOCK has many restrictions and gotchas and is NOT
a substitute for the "dummy update" trick in any situation, except where
your transaction is targetting one, or at most a few rows explicitly and
non-interactively, for a brief moment of elapsed time. It has no place in
a scrollable dataset or any interactive GUI interface. One of several
crucial reasons is that the lock for a particular row is not tested until
the row is actually fetched. It's therefore possible to fetch part of the
dataset and do some work on something in that packet, only to have the
pessimistic lock fail on a subsequent packet. You then lose all of the
work done so far in that dataset.

I was fixing up a translation of a Russian paper on pessimistic locking
last night, written by a couple of IBX/FIBPlus users. Their "only correct
approach", which they have adopted and recommend for interactive
application design, goes like this:

Select a large dataset in a read-only transaction.

Let the user select a row which she wants to work on.

Start another transaction and, using the keys of the selected row, select
that single row into another dataset.

Inside the same transaction, create an update object for the operation
selected.
Prepare the updatesql statement and call Post.

Commit the transaction.

What hardships some people will go through, to work around the problem that
their data access architecture of choice doesn't support OAT control!

However, because their "only correct design approach" limits them to one
DML operation on one row per transaction, it DOES make it feasible for them
to use FOR UPDATE WITH LOCK as the normal mechanism for pessimistic
locking, should it be required - they can request it on the single-row
select that they do when user selects a row from the read-only grid.

Erk, FOR UPDATE WITH LOCK won't operate on joined sets, either - not a big
deal to them, since their architecture doesn't support updatable joined
sets anyway.

There are other "curlies" with FOR UPDATE WITH LOCK which will keep you
awake at night. If you use it in a tiConcurrency transaction, you actually
get a dataset which, after the first row, contains record versions that
were committed after your transaction started. This might fall slightly
short of "dirty read" but at best it's "contaminated read".

This is a toy with small detachable parts which is not safe in the hands of
children under the age of 99.

Helen