At 07:23 AM 18/03/2008, you wrote:

>Obviously this is bad. ~120000 difference between OAT and Next
>Transaction.

There's no "hard number" that's bad. It depends on lots of factors like throughput and no. of connected users and so on. But I see that your database has only existed for 3 days...

>I was reading in "The Firebird Book" something about the
>difference between hard and soft committed transactions. What does that
>mean?

Borland invented a concept called COMMIT WITH RETAIN to make client/server programming with its RAD products look more appealing to certain kinds of developer. It causes work to be committed without losing the related buffer and cursor resources. That's nice for developers but pretty horrible for database hygience if it is used indiscriminately. Unfortunately, in Delphi and related products it's not unusual to find that the default behaviour for the Commit method is CommitRetaining. That's your "soft" commit.

A "hard" commit is simply calling COMMIT.

>Database header page information:
> Flags 0
> Checksum 12345
> Generation 199698
> Page size 4096
> ODS version 10.1
> Oldest transaction 57333
> Oldest active 57334
> Oldest snapshot 57333
> Next transaction 199690
> Bumped transaction 1
> Sequence number 0
> Next attachment ID 0
> Implementation ID 16
> Shadow count 0
> Page buffers 0
> Next header page 0
> Database dialect 3
> Creation date Mar 15, 2008 1:00:23
> Attributes force write

So here we have a 3-day-old database that has processed about 200K transactions. At least one has remained running whilst about 140K more transactions have started. That could be just you watching the database from an admin tool. Everything in Firebird is under transaction control, including SELECTS of course.

>I'm using Delphi 7, dbexpress, and the upscene driver to access Firebird
>1.5.3. The database in question gathers information from a webservice
>and there is a reporting program that reports usage statistics on the
>data back through the webservice. So it's only insert and select
>statements from a webservice. Typical code structure looks something
>like this.
>
> sqlDataSet := TSQLDataSet.Create( nil );
> try
> sqlDataSet.SQLConnection :=
>Global_FBConnectionBroker.GetAvailableDatabaseConnection;
> sqlDataSet.CommandText := 'insert blah';
> TransDesc.TransactionID := GetRandomNumber;
> TransDesc.IsolationLevel := xilREPEATABLEREAD;
> sqlDataSet.SQLConnection.StartTransaction( TransDesc );
> try
> sqlDataSet.ExecSQL;
> sqlDataSet.SQLConnection.Commit( TransDesc );
> except on E:Exception do
> begin
> OutputFmtDebugMessage( 'inserting blah caused: %s (%s)',
>[E.Message, kCRLF + sqlDataSet.CommandText, kCRLF + ConvertParamsToText(
>sqlDataSet )] );
> sqlDataSet.SQLConnection.RollBack( TransDesc );
> end;
> end;
> finally
> Global_FBConnectionBroker.ReleaseConnection(
>sqlDataSet.SQLConnection );
> FreeAndNil( sqlDataSet );
> end;

The Upscene driver is built over IBObjects so we can be pretty sure that Martijn has separate implementations for Commit and CommitRetaining; so these little "shock hits" on the database won't be causing the buildup. (Do check this though.)

It's much more likely that it's the transaction that is reporting back to the client that is your long-running one. Now, if that transaction is set to Read Only with ReadCommitted isolation, it won't cause a long-running transaction problem. Any read-write transaction will; and a read-write or read-only transaction in RepeatableRead (tiConcurrency) isolation will. Your web user can wander away but, unless you've got some form of forced timeout on that transaction, it will take upwards of 2 hours for Firebird to detect an idle connection and start cleaning up.

So if you're concerned about a gap buildup and that SELECT doesn't need to be in a read-write transaction, make it read-only, and make it ReadCommitted so that it won't hang up garbage collection.

./heLen