At 11:33 AM 1/07/2004 +0300, you wrote:

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

No. One IB_Query cannot be in two transactions. That isn't an IBO
limitation: it's a database rule.

What FIBPlus does, it starts a NEW transaction when it performs each DML
operation. With IBO, if you want to follow this model, use a TIB_DSQL in a
separate transaction for your DML operations. If you want to be able to do
it in one shot, by default, without considering the consequences for
contention and integrity, then use FIBPlus.

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

Wrong, IBO has much better control of cursors than other
implementations. What is does, it invalidates *only that row*. The
current state of the dataset buffers is just as it was before the commit
(unless you set it to be something different...) Next time your app needs
to access that row, it knows to re-fetch that row, even if it is currently
in the buffer.

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

Of course. But, again, if you are not interested in what has happened at
other users' locations, you can use an IBO Bookmark to invalidate that
single row in the buffer. Then, IBO will re-fetch that row, just as it
does with the real-time model.

Also, IBO doesn't "Append" rows to datasets (although it can do so, if you
want BDE compatibility). ("Append" is something that happens in file-based
databases like Paradox, not in Firebird). IBO's inserts occur "in place"
(also an artificial concept). So the buffer can be refreshed to get the
latest view of, say, an ordered set, without disturbing the unchanged rows
that were in the buffer.

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

I don't believe that. I know that the Devrace guys made big noises about
this when they implemented it as the default way to do datasets. You'd
have thought they had just invented sliced bread!! It panders to those who
want to transport huge datasets across the wire (a characteristic of bad
design for client/server systems) and it does solve the endemic problem
that FIBPlus and IBX have with stale buffers and their inability to create
a transaction context that extends beyond the limits of the physical
transaction. It's not a magic bullet; it's a workaround for deficiencies
that IBO doesn't have.

With IBO, you already have (and always had) the capability to use this
model of dataset management, which works OK where work is dispersed across
a wide range of the database. But to make it the default behaviour of
datasets is to sacrifice the two main advantages of proper transaction
control: atomicity and a low level of conflict. IBO takes care of these
beautifully. It even makes it reasonable to use CommitRetaining (wisely,
not blindly!!) and to close off unfinished transactions that have been
abandoned or allowed to continue too long.

Helen