Under ASTA and multi-threading you are responsible to make sure the
transactions are advancing. I don't use the timer for non-default sessions
by default. I saw Geoff show you some code to ensure that.

I think placing a check to close the schema cache transaction would be a
welcomed improvement, although there still may be a possibility of a
hang-up. I could also make the schema cache never leave a transaction open
as well. I think I'll favor the latter.

Even though this is documented I agree this needs to be addressed more
formally.

Regards,
Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com

----- Original Message -----
From: "Mark Irving" <markirving@...>
To: "IBO" <IBOBjects@...>
Sent: Saturday, May 11, 2002 4:54 PM
Subject: [IBO] Database growth


> Hi,
> I'm new to IBO and have a problem (maybe of my own making). First
some background. I'm using an ASTA server and swapping over from IBX to IBO
version Ha. The IBX version works without problems but the advice from
others is to swap to IBO because of threading issues in IBX. My Asta test
client is a little unusual because I'm getting/storing a lot of blobs
(9k-70k per transaction). With the IBX based server the gdb database file
stays at 2M while the test client repeatedly gets and saves the blobs, the
IBO version grows and grows (up to 500M have an hour or so). Now I
understand a little about interbase's record versioning and that this may be
symptomatic of a long transaction. Looking at the database statistics this
is looks like the issue. The IBX version has the oldest transaction, oldest
active transaction and next transaction in the header statistics
incrementing all the time but remaining within 2 or so of each other. The
IBO version has the oldest and active stuck for the duration of the server
process. OK looks like I have a transaction started but not completed. After
cutting and reducing the problem to simpler and simpler transactions I'm now
down to no explicit transactions at all and just the test client logging
in/logging out repeatedly. This still doesn't increment the header
statistics properly and all I have happening is a SELECT, UPDATE call on
login and INSERT call on logout (to my user account tables). Using the
monitor I get the following result for 1 login/logout cycle. You can see my
3 SQLs plus an internal IBO schema SELECT to get computed field names. (of
which there are none) I now at the edge of my understanding but for the
hell of it I look through the monitor output and see COMMITs for my SQL
implicit transaction but not for the schema SELECT. Does this mean this is
the uncommitted transaction for the life of the connection?. The schema
select is cached and runs only after the first select and never again. Ok
for the hell of it I add a exit to the get schema call deep within IBO to
stop that SELECT happening and now my app increments the transaction IDs and
the database growth is stopped!
>
> I'm not saying there is a problem with IBO but can anyone give me some
advice on what I'm doing wrong? At the moment its all implicit transactions
perhaps I should check some settings? Why should avoiding the schema SELECT
fix things?
>
>
> 12/05/2002 9:36:58 AM-->Server connects to
127.0.0.1:c:\Adapa\sagax\data\v212\sagax212.gdb
> 12/05/2002 9:36:58 AM-->Test Server started successfully on port 9000
> 12/05/2002 9:37:05 AM-->/*---
> DATABASE INFO
> DB_HANDLE = 10912572
> ----*/
> 12/05/2002 9:37:05 AM-->/*---
> START TRANSACTION
> DB HANDLE COUNT 1
> TR_HANDLE = 10912392
> ----*/
> 12/05/2002 9:37:05 AM-->/*---
> PREPARE STATEMENT
> TR_HANDLE = 10912392
> STMT_HANDLE = 10912200
>
> SELECT VersionID,Customer,Features FROM DataBaseInfo
>
> PLAN (DATABASEINFO NATURAL)
>
> FIELDS = [ Version 1 SQLd 3 SQLn 30
> DATABASEINFO.VERSIONID = <NIL>
> DATABASEINFO.CUSTOMER = <NIL>
> DATABASEINFO.FEATURES = <NIL> ]
> ----*/
> 12/05/2002 9:37:05 AM-->/*---
> START TRANSACTION
> DB HANDLE COUNT 1
> TR_HANDLE = 10906700
> ----*/
> 12/05/2002 9:37:05 AM-->/*---
> PREPARE STATEMENT
> TR_HANDLE = 10906700
> STMT_HANDLE = 10906772
>
> SELECT R.RDB$FIELD_NAME
> , R.RDB$RELATION_NAME
> FROM RDB$RELATION_FIELDS R, RDB$FIELDS F
> WHERE R.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME
> AND F.RDB$COMPUTED_SOURCE IS NOT NULL
> AND NOT R.RDB$RELATION_NAME STARTING WITH 'RDB$'
> ORDER BY 1 ASC
>
> PLAN SORT (JOIN (F NATURAL,R INDEX (RDB$INDEX_3)))
>
> FIELDS = [ Version 1 SQLd 2 SQLn 30
> RDB$RELATION_FIELDS.RDB$FIELD_NAME = <NIL>
> RDB$RELATION_FIELDS.RDB$RELATION_NAME = <NIL> ]
>
> SECONDS = 0.010
> ----*/
> 12/05/2002 9:37:05 AM-->/*---
> EXECUTE STATEMENT
> TR_HANDLE = 10906700
> STMT_HANDLE = 10906772
> PARAMS = [ ]
> ----*/
> 12/05/2002 9:37:05 AM-->/*---
> EXECUTE STATEMENT
> TR_HANDLE = 10912392
> STMT_HANDLE = 10912200
> PARAMS = [ ]
> ----*/
> 12/05/2002 9:37:05 AM-->/*---
> PREPARE STATEMENT
> TR_HANDLE = 10912392
> STMT_HANDLE = 10912200
>
> SELECT User_Name, PWord, UserRole, Rights, Description, LastUpdate,
ErrorCount FROM AccCntrl c, AccRoles r
> WHERE User_name=? /* name */ and c.UserRoleCode=r.UserRoleCode
>
> PLAN JOIN (C INDEX (RDB$PRIMARY3),R INDEX (RDB$PRIMARY1))
>
> FIELDS = [ Version 1 SQLd 7 SQLn 30
> ACCCNTRL.USER_NAME = <NIL>
> ACCCNTRL.PWORD = <NIL>
> ACCROLES.USERROLE = <NIL>
> ACCROLES.RIGHTS = <NIL>
> ACCCNTRL.DESCRIPTION = <NIL>
> ACCCNTRL.LASTUPDATE = <NIL>
> ACCCNTRL.ERRORCOUNT = <NIL> ]
> ----*/
> 12/05/2002 9:37:05 AM-->/*---
> EXECUTE STATEMENT
> TR_HANDLE = 10912392
> STMT_HANDLE = 10912200
> PARAMS = [ Version 1 SQLd 1 SQLn 1
> [NAME] = 'ADMIN' ]
> ----*/
> 12/05/2002 9:37:05 AM-->/*---
> PREPARE STATEMENT
> TR_HANDLE = 10912392
> STMT_HANDLE = 10912200
>
> UPDATE AccCntrl SET ErrorCount=0,LastUpdate=? /* jdate */
> WHERE User_name=? /* name */
>
> PLAN (ACCCNTRL INDEX (RDB$PRIMARY3))
>
> FIELDS = [ Version 1 SQLd 0 SQLn 30 ]
>
> SECONDS = 0.010
> ----*/
> 12/05/2002 9:37:05 AM-->/*---
> EXECUTE STATEMENT
> TR_HANDLE = 10912392
> STMT_HANDLE = 10912200
> PARAMS = [ Version 1 SQLd 2 SQLn 2
> [JDATE] = '12 May 2002'
> [NAME] = 'ADMIN' ]
>
> SELECT COUNT: 1
> UPDATE COUNT: 1
> ----*/
> 12/05/2002 9:37:05 AM-->/*---
> COMMIT
> TR_HANDLE = 10912392
> ----*/
> 12/05/2002 09:37:06-->/*---
> START TRANSACTION
> DB HANDLE COUNT 1
> TR_HANDLE = 10912392
> ----*/
> 12/05/2002 09:37:06-->/*---
> PREPARE STATEMENT
> TR_HANDLE = 10912392
> STMT_HANDLE = 10912200
>
> INSERT INTO Accounts
(SessionNum,User_name,LogonDate,LogonTime,ConnectHrs,IPAddress,AppName,AppVe
r)
> VALUES (0,? /* Uname */ ,? /* LDate */ ,? /* LTime */ ,? /* CTime */ ,? /*
IP */ ,? /* AppName */ ,? /* AppVer */ )
>
> FIELDS = [ Version 1 SQLd 0 SQLn 30 ]
>
> SECONDS = 0.010
> ----*/
> 12/05/2002 09:37:06-->/*---
> EXECUTE STATEMENT
> TR_HANDLE = 10912392
> STMT_HANDLE = 10912200
> PARAMS = [ Version 1 SQLd 7 SQLn 7
> [UNAME] = 'ADMIN '
> [LDATE] = '12 May 2002'
> [LTIME] = '09:37:05'
> [CTIME] = 0
> [IP] = '192.168.0.2'
> [APPNAME] = 'TESTCLIENT.EXE'
> [APPVER] = '?' ]
>
> INSERT COUNT: 1
> ----*/
> 12/05/2002 09:37:07-->/*---
> PREPARE STATEMENT
> TR_HANDLE = 10912392
> STMT_HANDLE = 10912200
>
> SELECT VersionID,Customer,Features FROM DataBaseInfo
>
> ... etc etc ad finitum