Helen,
(I thought this was a firebird-support thread, not IBO - I am starting
to grow old).

>>select * from table where id = :id with lock
>>
>>should get you all the benefits of pessimistic locking without firing
>>the update triggers.

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

Hmm... I think the "for update" and "with lock" parts have been separated (or
are going to be before the final release) as per Ivan Prenosil's
request and Nickolay Samovatof's acceptance.

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

Where have I said anything different, exactly?

H> FOR UPDATE WITH LOCK has many restrictions and gotchas and is NOT
H> a substitute for the "dummy update" trick in any situation, except where
H> your transaction is targetting one, or at most a few rows explicitly and
H> non-interactively, for a brief moment of elapsed time.

Of course; but I think that both me and the OP understand this, so
your remark may be of use of other readers (it's never enough to
repeat wise things).

H> It has no place in
H> a scrollable dataset or any interactive GUI interface.

Not that I think this is the precise topinc of the thread, but I'd say
that it depends on how the GUI works. Many people (even I at times)
tend to use a (filtered) list, let the user choose *one* of the
records and *then* edit that one record in a different transaction,
possibly with a modal form. I agree that the behaviour is different
from the pessimistic locking feature of IBO, in fact is better as it's
more standard, while IBO's is a work around IB/Fb's deficiencies.

H> One of several
H> crucial reasons is that the lock for a particular row is not tested until
H> the row is actually fetched. It's therefore possible to fetch part of the
H> dataset and do some work on something in that packet, only to have the
H> pessimistic lock fail on a subsequent packet. You then lose all of the
H> work done so far in that dataset.

That is, if you want to lock, you have to fetch. I think that's well
understood too.

H> Select a large dataset in a read-only transaction.

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

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

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

H> Commit the transaction.

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

I'd put is in slightly simpler terms, and would not dare to call it
the only correct approach, but basically that's it.
Not always you are allowed to perform fetches and edits in the same
transaction. In three tier, for example, you can't. And the world is
moving -> threee tier rapidly.

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

I think that was an example. The rows to update can well be several,
from different datasets. A "document", in other terms.

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

I think that IBO is enhanced to leverage the syntax, it will do it for
joins as well (KeyRelation, LockSQL or whatever).

H> There are other "curlies" with FOR UPDATE WITH LOCK which will keep you
H> awake at night.

Currently the only thing that keeps me from sleeping at night is it's
terribly hot round here lately. ;-)

H> If you use it in a tiConcurrency transaction, you actually
H> get a dataset which, after the first row, contains record versions that
H> were committed after your transaction started. This might fall slightly
H> short of "dirty read" but at best it's "contaminated read".

That's actually one of the points of attention; Nickolay explained
this thoroughly in its messages.

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

I *cannot* wait that long! :-)

Ciao
--
Nando mailto:nandod@...