At 05:12 p.m. 27/03/2014, Robert Martin wrote:

>Yes. Lots of areas ignore transactions and rely on auto commit. The POS
>function starts an explicit transaction, which I understand does a hard
>commit on any existing CommitRetaining transaction save points (or
>whatever they are called).

Nope. Starting a new transaction does not commit other transactions. Where did that come from?

>> (b) whether you have checked to ensure none of your transactions is using CommitRetaining
>
>Yes. Most of the code does this. When transactions have not been
>explicitly added, the components connect to the IBODatabase without
>specifying a transaction. they get the default transaction which is set
>to AutoCommit and CommitRetaining (the defaults).
>
>> (c) whether you are using TIBOTables (aaaarrrgh!!)
>
>Yes. Also using tables. A triple whammy (a, b and c) !!. Mainly for
>old code, most new code uses IBOQuery. If I disable CommitRetaining
>what impact will that have on my code, will it help and what code will I
>need to change? Please excuse my ignorance. My understanding is that if
>I disable CommitRetaining I will get a hard commit after every Post in
>the example below.

Yes. If CommitRetaining is disabled, then both an explicit Commit and an Autocommit will complete fully.

> Could I improve it by leaving commitRetaining on and at the end forcing a hard commit?
>
>i.e if my code is
>
>Table1.Edit;
>Table1.FieldByname('d').AsString := '2';
>Table1.Post;
>
>
>Table2.Edit;
>Table2.FieldByName('val').AsString :=
>Table1.FieldByname('someval').AsString;
>Table2.Post

Don't get how you think this describes what you are asking. Did you mean to say "by leaving AutoCommit on"? Post is an IBO method. It is IBO's way of sending the uncommitted record to the database, causing a new record version to be created (or a delete stub, in the case of a delete meithod call). Post causes neither a hard commit nor a CommitRetaining if AutoCommit is off. CommitRetaining, on the other hand, implements a transaction parameter in the API that the database understands.

>am I correct in saying that
>having a long running read only transactions isn't a problem?

Only if its isolation is ReadCommitted and you're working with an Fb server 2.0 or higher. In Snapshot (Concurrency) isolation, even read-only transactions are "interesting" from the GC point of view.

>For an 8KB page size with reasonable RAM resources on the host server, think in the low HUNDREDS of pages for the caches, e.g., 128, not TWENTY THOUSAND! That's not an IBO issue - unless everyone is logging in as SYSDBA and setting page buffers from the application - ouch! Check that this isn't so.
>
>The cache size was just for testing. I have around 3 connections on my
>test DB and 16GB ram.

Seems a bit pointless to test with a daft configuration.

>Im not sure what you meaning by 'That's not an
>IBO issue - unless everyone is logging in as SYSDBA and setting page
>buffers from the application - ouch! Check that this isn't so.'
>
>Our connections are SYSDBA. I thought the buffers was set only in the
>DB and that IBO didn't do anything with it?

Umm. Not right. In fact, IBO, IBX and the VCL allowed ordinary users to mess around with the page buffers and some other vital organs until it was fixed in Firebird 2.1.2 (or thereabouts) so that only SYSDBA can pass these parameters. AFAIR, the fix was backported to 2.0.x. at around 2.0.4. It's well likely that you have inherited some such nasties from your old VCL code so check the Params property of your IBODatabase and remove them. (Check Fb 2.0.7 and 2.1.5 release notes.)

The best way to set the cache size is via firebird.conf DefaultDBCachePages, which will cause all databases on the server to have the same NUMBER OF PAGES in their caches. If you need individual dbs to have different numbers of pages ("buffers") in their cache, it can be set in the database header during a restore, using the -bu switch. This isn't a good idea, either, unless you absolutely must, as it overrides the global setting and is likely to get overlooked if you switch between server models.

>FB. Obviously CommitRetaining is the issue,

It's one of your issues. You won't win much if you ignore the others.

>I just need to better
>understand the effects of this and the alternatives. If I could keep
>autocommit and disable CommitRetaining I suspect that would help.

Transactions really are the blood-an'-guts of working with Firebird. Some reading on the subject of transactions should help. Jason has papers on his tech pages. The IBO help file helps too, :-\

>Really appreciate your help so far Helen :)

This really is *it*. No more.

Helen