Subject Transaction management and AutoCommit
Author Robert Martin
Hi

I have been playing with a test app that uses auto commit. Basically
there are 3 components and IBODatabase and two IBOQuery components. One
of these is an insert Query and the other a select query. I am using
the latest FB 2.5.2 in superclassic mode. My IBODatabase is using all
defaults.

I do a gstat on the DB and confirm that the OAT and Next transaction are
close (i.e. 1 different) then click run. The system inserts 15000
(although the effects are similar when 30000 inserts are used) entries
and I run GStat again. The result is shown below.

Oldest transaction 426761
Oldest active 411761
Oldest snapshot 411761
Next transaction 426762

If I close the app the above remain constant. If I reopen the app and
run the select Query the numbers all 'reset'.

I assumed that connecting triggers garbage collection. So to test this
I re ran and instead of opening the app and running the select I
manually ran a -sweep using gfix.exe. Surprisingly this doesn't 'reset'
the transactions. Any ideas what is going on here?


The results above are the same even when I attempt to 'force' a hard
commit by doing an explicit IBODatabase.IB_Transaction.StartTransaction
and IBODatabase.IB_Transaction.Commit after the insert loop. This is
not what I would have expected.

What am I doing wrong ?

p.s. If I explicitly start and commit transactions around the inserts
the OAT stays close to the Next Transaction. So that seems to be
working fine.

Here is my test code if that helps...

var
Counter : Integer;
begin
StopButton.Enabled := True;
RunButton.Enabled := False;

try
ConnectToDB;

ProgressBar.Position := 0;
ProgressBar.Max := StrToInt(RunEdit.Text); //Normally 15000

fStopNow := False;
Counter := 0;
while (Counter < ProgressBar.Max)
and (fStopNow = False) do begin
// InsertQuery.IB_Transaction.StartTransaction;
InsertQuery.ExecSQL;
// InsertQuery.IB_Transaction.Commit;
ProgressBar.Position := ProgressBar.Position + 1;
Inc(Counter);

Application.ProcessMessages;
end;

//Attempt to explicity force CommitRetaining transaction to
'hard' commit
IBODatabase.IB_Transaction.StartTransaction;
IBODatabase.IB_Transaction.Commit;

finally
ProgressBar.Position := 0;

StopButton.Enabled := False;
RunButton.Enabled := True;

// IBODatabase.Disconnect;
end;



Thanks
Rob