At 10:17 AM 7/07/2005 +1000, you wrote:

>Morning
>
>Can someone please explain what CommitRetaining is meant to be used for:
>
>a) Iterating through a dataset, and making updates in the same txn context,
>while periodically CommitRetaining, so that the main iterating dataset does
>not close and maintains its cursor position

"Dataset" is a client-side concept. The server doesn't know about datasets
at all. It returns rows to the client on request. It is then the client's
business to "do something" with the rows it has fetched. IBO happens to
store them into a structure we call a dataset.

Because a request doesn't complete until all of the rows requested have
been fetched by the client, the dataset has all kinds of mechanisms going
on to do stuff like: making the returned rows seem as though they come
from a scrollable cursor (ib_bdataset has this capability), keeping a count
of rows received, and so on.

A request (statement) is not the same thing as a transaction. Requests
must happen inside a transaction, but many requests can be submitted
through the same transaction.

A transaction has a "start" and a "finish". Once a transaction is started,
it does not finish until either a COMMIT or a ROLLBACK request is received
by the server and is accepted.

CommitRetaining is a full commit. Once CommitRetaining is successfully
called, the transaction is finished and can't be rolled back. The
difference between CR and a "hard COMMIT" is that 1) the server does not
destroy the cursors and other resources associated with the (now finished)
transaction and 2) that it causes a new transaction to be started
immediately after, using the old resources.

>and "old" snapshot view of it's data.

Only true if the transaction was configured with Snapshot isolation. In
this case, the new transaction's "snapshot" now includes any changes
performed by the transaction in its previous "life".

A ReadCommitted transaction, on the other hand, will see ALL changes that
have occurred in the database since the last time the transaction restarted.

> The TXN is fully committed and must be immediately followed by a
>txn.StartTransaction

It happens automatically if CommitRetaining is called.

>or
>
>b) Iterating through a dataset, making updates in the same txn context,
>while periodically CommitRetaining in order to save the work, but the
>Transaction is not fully finished yet. A rollback will rollback to the most
>recent CommitRetaining.

No. The transaction is "finished" each time CommitRetaining is
called. The question of whether rollback is possible doesn't depend on
whether CommitRetaining was called, rather than Commit. Both are
commits. Rollback can happen if work has been updated in the database
(posted) but has not yet been committed at all.

There is a transaction setting, Autocommit, that can be made on the client
side. If Autocommit is true, then EVERY statement request will be
committed immediately it is posted. Effectively, that means that every DML
statement is executed in its own transaction. Autocommit only works with
CommitRetaining.

>I get the feeling b) is actually describing a SavePoint, and a) is the true
>description of a CommitRetaining.

No. A savepoint is a transaction-level operation that has nothing to do
with whether the server preserves cursor resources from one transaction to
another.

>So,
>
>1) What exactly is "Retained" in a CommitRetaining - obviously not the
>Transaction.

Handles, cursors and various other memory structures on the server and/or
in the interface that, in a hard commit, are destroyed and freed when the
transaction ends. A CommitRetained transaction is a "born-again"
transaction, if you like.

>2) Since the txn is fully committed in a CommitRetaining, where does the
>dataset that is kept open get's it's remaining data from?

Back to the beginning, a dataset at its barest is just a container full of
data. The server doesn't have any knowledge of "datasets". The dataset has
a whole lot of stuff going on at the client, to ensure that the stuff in
the container is consistent with the current state of the database. What
that "stuff" might be is entirely up to the client and its
capabilities. So, for example, an ib_query maintains not one but three
cursors on a dataset, and uses them to open "windows" in the cursor
set. The IBO dataset maintains a number of structures to synch and resynch
datasets with cursors.

The dataset itself takes care of determining whether data currently in its
buffers are still valid or whether it needs to open (a) new cursor(s),
refresh its keys, etc.

fwiw, CommitRetaining is a trick that was added to InterBase by Borland as
a way to make BDE applications act as though they were working with a
desktop database (notably Paradox). From the POV of client/server design,
it is an abomination, as the Harrison and Starkey will be delighted to tell
you. :-)

However, a lot of Delphi developers like it, and IBO supports it for
compatibility. As Jason has pointed out in the past, it does have some
benefits in terms of resource usage on old and under-resourced
hardware. The other side of the coin is that Firebird and IB developers
need to be aware that blind, unconditional use of CommitRetaining will
quite quickly pull your system to a standstill, because CR prevents the
server from cleaning out old record versions and freeing up memory and disk
resources.

Helen