Subject Re: [IBO] support for 2 transactions in ib_query
Author Mihai Chezan
Thank you for replying so quickly to my post, but you still haven't
answered to my question: will there be support for 2 transaction in
ib_query (one for the select and the other for insert/update/delete)?
Or you did answer to my question but in a less direct way and the answer
was no?

>IBO has always had multiple transactions - long before FIBPlus was born.
I know IBO has multiple transactions. That was not what I meant.

>It doesn't, anyway, even in the read-write context, if you use
>CommitRetaining. CR is good for this; although one needs to ensure that a
>hard commit occurs regularly (in terms of elapsed time) to avoid building
>up garbage unnecessarily.
I know about CR but like you said "one needs to ensure that a hard commit
occurs regularly"

>It's important to ensure that your read-only transaction has
>ReadCommitted isolation level.
Of course and that's not a problem; just set the isolation level to
tiCommited and ReadOnly to true ;)

>However, it does separate the DML context from the
>user's view. It's an imperfect model, therefore, for an environment with
>many users where it's essential to miminise conflict and keep a real-time
>view, as in a bookings system.
Let me give you an example:
You have a query that returns 500 records. The result of the query is shown
in a grid.
When you open the query a transaction is started (if one is not already
open) and the select query in executed and IBO gets a cursor to the result.
If the grid shows 10 records then ibo will will fetch 10 records from the
cursor. When the user scrolls the grid more fetches are operated. Let's say
the user scrolls to the record 400, then pushes a button that does let's
say an insert (append and post) to our query but it needs to update some
other table too and all this has to be done in an transaction.
One way to do this is to link the grid query and the other query (the
update one) to the same transaction, then start the transaction, do the
insert (append and post to the grid query), run the update query and then
commit. But when you commit the cursor of the grid query will be
invalidated and the default action for commit for ib_query is to close. If
you set it to refresh it will have to reget the 400 records.
Another way is to user a separate query for the insert (instead of using
append and post to the grid query) but then to be able to see the result
you still have to refresh the grid query.

If you would had a transaction for the select cursor and another one for
the insert and the update then when doing the commit on the write
transaction won't affect the cursor transaction and you won't have to do a
refresh to see the result.

I believe that having this feature implemented in IBO will simplify the OAT.
When you have an inser/update/delete you always want to commit. When you
have a select you don't want to commit because you don't want to do a fetch
all but instead you want to keep the cursor open to get only the records
you need to minimize network load.

Mihai Chezan