At 12:25 PM 3/05/2006, you wrote:

>Hi
>
>I have a readonly transaction (tiCommitted) where a query (TIBOQuery) is
>run (left open) and then the underlying data is changed (additions and
>deletions) in another transaction. The theory is that if I do a locate
>in the Query and don't find the record I know it is an addition.
>
>However this is not working, the Locate is always successful and it
>feels like the query is refreshing itself with each call to locate
>(records are added between locates).

It *will* refresh itself if it is asked to locate something that is
not in its buffers. The Read Committed transaction can see changes
made by other transactions but, if another transaction performs and
insert or a delete, the new state won't be visible in the buffers of
the Read Committed transaction automagically.

(With the native IBO datasets you can make it happen, though, by
implementing DML Caching...I'm reasonably sure that you *can* access
the DML Caching mechanism through the InternalDataset...)

>The Query always finds the record and therefore doesn't identify additions.

Correct; because the running Read Committed transaction is able to
see the committed work of other transactions without itself being committed.

>Changing the transaction type to 'tiConcurrency' solves the problem.

Well, it solves one problem and creates another, worse one. A
long-running read-only transaction in tiConcurrency isolation is not
desirable, since it stops the OIT advancing. A read-only transaction
in tiCommitted does not do that.

>I
>understand that tiConcurrency gives a snapshot of data at time the
>transaction is started and that tiCommitted will show changes from other
>transactions, however I thought that once a query was active, regardless
>of transaction type the data returned remained constant until the query
>was refreshed. Is that not the case or am I missing something?

In any event, if the sought row isn't found in the buffers, Locate
will cause the dataset to either fetch more rows from the server's
buffer (if there are any eligible rows that are not currently in the
buffer) or to refresh the query. Note that refreshing the query only
closes and opens the dataset. It doesn't commit the uncommitted transaction.

In the case of the Read Committed transaction, this process of
locating a row that was inserted by another transaction is guaranteed
to cause a refresh call (think about it!) When the transaction is
Concurrency, a refresh makes no difference to the contents of the
dataset so, once the current output of the query has been tested, the
locate can *only* return False (the answer you are actually
interested in!) But the cost of this convenience is too great in
terms of garbage accumulation.

This approach has all the hallmarks of using a sledgehammer to crack
a nut. You wrote: "The theory is that if I do a locate in the Query
and don't find the record I know it is an addition." What's the
purpose of this? If your read-write transaction posts and commits an
insert, you'll already know it is an addition...Locate() is a very
heavy way of confirming what you already know. Just create a flag
that you can set and reset - low-calorie and no cost.

Helen