[Firebird-odbc-devel] ODBC driver 2.0.151 / database growth / Oldest active transaction updated late

[Franz B. <fra...@sp...>]

Hi all,
 
Since I use ODBC driver WI_T2.0.0.151 = OdbcFb.dll = v2-0 I encounter the
behaviour described below.
This is different from the previous version I have used, OdbcFb32.dll =
WI_T2.0.0.142 = v2-0-beta
 
I use Delphi 7 or XE (does not matter) and odbcfb.dll, Firebird 2.1.4
Host system is Windows XP or Win7, does not matter.
For test purpose I have also used Firebird 2.5, but this does not change the
situation. 
It rather seems to be a matter of the ODBC driver than the DB (=datbase)
version.
 
The DB is accessed from Delphi via ADO and ODBC using the Delphi ADODB
components.
 
Situation:
I create a test DB, an appropriate ODBC connection to it, and start 2 (test-)
programs acessing the same table concurrently through Delphi ADO Query like
"select * from users"
and display the contents on a Delphi TDbGrid and a TDbNavigator.
the I enter some records, and modify them.
 
The connection string for the TADOConnection is:
Provider=MSDASQL.1;Password=masterkey;Persist Security Info=True;User
ID=SYSDBA;Data Source=TRANSACTION_TEST
 
When I start 2 programs concurrently which access the same table, and modify som
records with the firtst program, the oldest transaction does not increment. 
The consequece (my assumption) seems to be that the database grows rapidly.
 
I have originally observed this in a production environment, where records are
updated very frequently, and read by a second program for evaluation purpose.
There I became aware of the unusual growth.
 
I could reproduce the situation with a small test program. In the production
environment the difference between oldest and next transaction grows to e.g.
100000, but the situation is basically the same as with the test programs.
 
This can be observerved with:
 
> gstat -h
D:\FUJITSO\G\SyLab\BT4300\DB_Firebird_uppercase_II\TRANSACTION_TEST.FDB
 
Database
"D:\FUJITSO\G\SyLab\BT4300\DB_Firebird_uppercase_II\TRANSACTION_TEST.FDB"

Database header page information:
        Flags                   0
        Checksum                12345
        Generation              188
        Page size               4096
        ODS version             11.2
        Oldest transaction      149
        Oldest active           150
        Oldest snapshot         150
        Next transaction        181
        Bumped transaction      1
        Sequence number         0
        Next attachment ID      22
        Implementation ID       16
        Shadow count            0
        Page buffers            0
        Next header page        0
        Database dialect        3
        Creation date           Sep 1, 2011 16:13:59
        Attributes              force write
 
    Variable header data:
        *END*
 
This behaviour differs from the old ODBC driver version 2.0.0.142.
When I use the old driver, the next transaction will increment in the same way,
but the oldest transaction will also do, so that the difference willl normally
be only 3.
 
1. How can I overcome this situation?
2. Is there a possibilty to specify a connection parameter to achieve the old
behaviour?
3. I have not found a way to directly issue ODBC statements over the Delphi
ADODB. This component seems to encapsulate ODBC completely. So it might be
difficult to do this, if this is the solution.
 
Thank you very much 
Best regards 
Franz Batik