At 07:05 PM 5/12/2005 +0000, you wrote:

>If I understand things correctly, doing a COMMIT RETAINING does
>nothing to advance the OAT.

It *prevents* the advance of the OAT, since all of the the resources used
by the transaction remain active. But, as long as the resources remain
active, it also prevents the OIT (oldest transaction) from advancing, to
enable the GC to clean up the hard-committed transactions that are between
the OIT and the OAT. When the gap between the OIT and OAT keeps growing,
you know you've got to do something in your application code to address this.

>If this is in fact the case, how does one
>avoid sticking the OAT during long running sequential reads / mass
>updates of a table? If would be nice to be able to COMMIT every
>hundred records or so to advance the OAT while retaining open cursors
>but there doesn't seem to be any way to accomplish this.

No. There is absolutely no way the engine is going to delete record
versions that are in use by any transaction.

Understand, too, that stuff like "sequential reads / mass updates of a
table" is at statement level. At the worst end of the problem is the
typical Delphi/legacy BDE/default IBX model, where absolutely everything
the application does is performed inside a single Autocommit transaction
that is started when the application starts and never gets a hard commit
until the application is shut down. Consider the typical Delphi app, that
opens 50 TTables in the OnCreate event in a single read-write Autocommit
transaction and displays them all day in grids...that's your recipe for
clogging up your database with stuck garbage

>I was wondering if other people had found a way to circumvent this problem.

Sure - don't write applications like that. Firebird isn't a desktop
database so it figures that applications written on the desktop model with
grids everywhere and a gazillion tables held open, regardless of whether or
not they are actually used, is going to be dysfunctional as a client in a
multi-transactional system.

There's a book waiting to be written on this whole subject - it sure isn't
something that a support list thread is going to solve. Since I'm aware
you do use Delphi (or have done, in the past), one strong recommendation is
to avoid processing these mass operations by looping through a dataset on
the client side. Write DML to do this sort of stuff and use an interface,
such as IBO, that provides a bare bones statement class (TIB_DSQL). For a
large proportion of the work, this obviates the need to pull massive sets
over to the client and hold their resources interminably.

./heLen