Subject RE: [IBO] Commit retaining
Author Jason Wharton
> Much appreciate the time you have both spent giving me a better
> understanding...


> > 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.
> >
> For control, every database action in our program is done
> inside an explicit
> StartTransaction/Rollback/Commit construct, even if only one
> query is to be
> executed/opened. Perhaps this is slightly overkill, but it
> saves me having
> to think :)

It is important that you use a full Commit then, rather than CommitRetaining
if you want to "not have to think about it".

> > 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.
> If as you suggest, a CR causes a new transaction to be started on the
> server, How come the TIB_Transaction.Started, InTransaction, and
> TransactionIsActive all return false?

You should find that TIB_Transaction.Started is true after a

> I have done a couple of tests:
> Iterate through a dataset (TIB_Cursor), doing updates
> Call CommitRetaining
> Call StartTransaction (!This works - it should have failed
> with "Already
> in a transaction")
> Call (Exception - Unknown cursor)

No, you are confusing what constitutes an application (explicit) transaction
and a physical transaction on the server. In many cases they are the same
but in some cases they need to be treated distinctly.

> And another scenario
> Iterate through a dataset (TIB_Cursor) doing updates
> Call CommitRetaining
> Call Commit - (IBObjects does nothing (stepped through source
> - it just
> fires event in this case))

In IBO I have it keep track of internal conditions and if it is possible to
do a commit (on the server) when calling CommitRetaining I will go ahead and
do the full commit. You have merely bumped into an optimization feature of

> How do you commit the "New" transaction if for whatever
> reason, no further
> updates are performed. When are the Server (cursor etc)
> resources freed?

IBO automatically closes the cursor resources when all the records are
fetched or when the dataset is closed.

> > >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?
> I believe the answer to this one now is "It get's it from the new
> transaction that IB starts since all the cursors are still (now)
> available inside the new transaction"

On the server there isn't really a fully new transaction started. What the
server does is mark the current transaction as committed and advance the
record versioning but it does not actually do everything associated with a
total commit. In most respects it is still the same physical transaction
open on the server. Even the client handle to that transaction remains the

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

Actually the tricks that were added to try and make InterBase emulate PDOX
were not really finished up. It appears CommitRetaining was rather
fundamental to the original design. The only problem is they did not finish
it up correctly.

The abomination was in trying to make the server fully emulate a navigatable
(forwards and backwards, first and last) record sets and its no surprise
that never was completed.

> Our product is full 3 tier, so obviously no data aware
> controls, sounds like
> SavePoint is what I am after in my framework. It is simply
> to allow the
> user to cancel operations that may take hours, and not have
> to start all
> over again should they wish to temporarily hold up processing
> to do other
> things

In your case you should be using a good front-end tool that has good change
logging and resolution systems so that you are building your own
interactions. It's like a cached updates model where the users make all
their changes, which are stored locally in the client, and then they hit the
"make things permanent" button and all the changes are submitted in one
stream of updates which succeed together or fail and nothing is committed.

> ps. One simple unrelated question - What is your opinion on
> database sizes
> > 4GB all in one file? ok on W2k and XP? how about 98? IB6.1.0.6

This is just conservative me speaking but I would keep your files 2GB and

> Thanks
> Josh

Jason Wharton