Ivan, Helen,

I finally could solve the problem: there was a bug in SQLAPI.

When preparing the TPB for the standard ReadCommitted level, SQLAPI
was using:

case SA_ReadCommitted:
*tpb++ = isc_tpb_read_committed;
*tpb++ = isc_tpb_no_rec_version;

And this should be:

case SA_ReadCommitted:
*tpb++ = isc_tpb_read_committed;
*tpb++ = isc_tpb_rec_version;

Now everything is working fine!

Thank you very much for your help.

Best regards,
Fabrice Aeschbacher

--- In firebird-support@yahoogroups.com, "Ivan Prenosil"
<Ivan.Prenosil@s...> wrote:

> Sorry, I overlooked you are starting new transaction for each command.
> Are you sure there is no other connection (e.g. isql) ?
> Do you disconnect immediately after running this loop ? (if yes,
> then garbage collection thread has no time do its job while
> the fb server is heavily loaded by your loop, and is stopped
> when the last connection is dropped.)
>
> Ivan
>
>
> ----- Original Message -----
> From: "Ivan Prenosil" <Ivan.Prenosil@s...>
> To: <firebird-support@yahoogroups.com>
> Sent: Monday, June 07, 2004 2:33 PM
> Subject: Re: [firebird-support] record versions allways growing
>
>
> > Can't reproduce using FB1.5, SS, Win32.
> >
> > Are you using FB API, or some components
> > (that perhaps do you a "favour" by autocommitting each command) ?
> >
> > Ivan
> >
> > ----- Original Message -----
> > From: "Fabrice Aeschbacher" <fabrice.aeschbacher@s...>
> > To: <firebird-support@yahoogroups.com>
> > Sent: Friday, June 04, 2004 10:56 AM
> > Subject: [firebird-support] record versions allways growing
> >
> >
> > > Hi,
> > >
> > > (Sorry for re-posting, but I forgot the subject)
> > >
> > > (LI-V6.3.0.4290 Firebird 1.5 Classic / linux)
> > >
> > > I noticed, using gstat -r, that the total versions / max versions
> > > records for
> > > one table is allways growing. So I started some tests, to try to
> > > understand how it works:
> > >
> > > CREATE TABLE A (
> > > ID INTEGER,
> > > V INTEGER
> > > );
> > >
> > > INSERT INTO A ( ID, V ) VALUES ( 1, 0 );
> > > COMMIT;
> > >
> > > Then I wrote a little C program that does the following:
> > >
> > > connect_to_db();
> > > i = 1;
> > > while (true) {
> > > start_transaction();
> > > sqlexec: UPDATE A SET V = :i WHERE ID = 1;
> > > i++;
> > > commit_transaction();
> > > }
> > >
> > > Here is the output of gstat, before the test is started:
> > >
> > > A (172)
> > > Primary pointer page: 548, Index root page: 551
> > > Average record length: 9.00, total records: 1
> > > Average version length: 0.00, total versions: 0, max versions: 0
> > > Data pages: 1, data page slots: 1, average fill: 1%
> > >
> > > And during the test:
> > >
> > > A (172)
> > > Primary pointer page: 548, Index root page: 551
> > > Average record length: 13.00, total records: 1
> > > Average version length: 9.00, total versions: 47528, max versions:
> > > 47528
> > > Data pages: 661, data page slots: 661, average fill: 92%
> > >
> > > As we can see, the record versions never stops growing.
> > > And the UPDATEs are taking more and more time.
> > >
> > > When I stop the test program, the number of versions stops

growing, but

> > > is not reset.
> > >
> > > When I restart the test program, the number of versions is not

reset, and

> > > keeps growing.
> > >
> > > So the questions:
> > > - How can I prevent this?
> > > - What would be the correct way of building an application that

has to

> > > frequently update the same records with different values?
> > >
> > >
> > > One more remark: when I change the test program and make it
> > > disconnect/reconnect
> > > every 1000 transactions, then the number of versions grows up to

1000,

> > > is reset to 0 after the reconnection (in fact, after the first

UPDATE

> > > after the
> > > reconnection), and restarts growing.
> > > Does this mean that I must regularly force a reconnection?
> > >
> > > Best regards,
> > > Fabrice Aeschbacher