Firebird transaction handling and performance

[ralle1]

We’ve got the problem that the Firebird Server becomes very slow performance in the course of a day. We’ve found out that the transactions cause this. In the database statistic, I can see that the different between oldest active Transaction and next transaction permanently increases. After reading in several forums, we thought that it’s correct to use the feature “UpdateTransaction”. But the increase is just the same. Please look at my example below. The database sweep interval is set to 10 but the values for “Oldest transaction” / “Oldest active” / “Oldest snapshot” are standing still and the “Next transaction” is running. Nothing else is working on this database.

Here the example:

Code: Select all

procedure TForm7.Button1Click(Sender: TObject);
var
  id : integer;
  IBCQuery : TIBCQuery;
  DefaultTransaction,
  UpdateTransaction : TIBCTransaction;
  IBCCon : TIBCConnection;
begin
  Cancel := false;

  IBCCon := TIBCConnection.Create(nil);
  IBCCon.LoginPrompt := false;
  IBCCon.Database := '10.1.0.18/3050:c:\databases\data1.fdb';
  IBCCon.Username := 'SYSDBA';
  IBCCon.Password := 'masterkey';
  IBCCon.Connected := true;

  DefaultTransaction := TIBCTransaction.Create(Self);
  DefaultTransaction.DefaultConnection := IBCCon;

  UpdateTransaction := TIBCTransaction.Create(Self);
  UpdateTransaction.DefaultConnection := IBCCon;

  IBCQuery := TIBCQuery.Create(Self);
  TRY

    (* Simple Table for Inserts:
        CREATE TABLE TESTTABLE (
            ID INTEGER NOT NULL,
            VAL INTEGER,
            PRIMARY KEY (ID))
    *)

    id := 0;
    IBCQuery.Connection := IBCCon;
    IBCQuery.Transaction := DefaultTransaction;
    IBCQuery.UpdateTransaction := UpdateTransaction;
    IBCQuery.SQL.Text := 'SELECT MAX(ID) FROM TESTTABLE';
    IBCQuery.Open;
    id := IBCQuery.Fields[0].AsInteger;
    IBCQuery.Close;
    IBCQuery.SQL.Text := 'SELECT * FROM TESTTABLE';
    IBCQuery.ReadOnly := false;
    IBCQuery.Open;

    repeat
      inc(id);
      IBCQuery.Insert;
      IBCQuery.FieldByName('ID').AsInteger := id;
      IBCQuery.Post;
      Label1.Caption := IntToStr(id);
      Application.ProcessMessages;
    until (Cancel);

  FINALLY
    IBCQuery.Close;
    IBCQuery.Free;

    DefaultTransaction.Free;
    UpdateTransaction.Free;

    IBCCon.Close;
    IBCCon.Free;
  END;

end;

Can you please help me??
Thanks!

[AndreyZ]

The point is that if the TIBCQuery.Options.PrepareUpdateSQL property is set to True, IBDAC prepares statements that are executed in the context of the writing transaction. This is done to provide the further work with the prepared statement. To avoid the problem, you should set TIBCQuery.Options.PrepareUpdateSQL to False. In this case, IBDAC does not prepare statements of the writing transaction, and executes the Commit statement instead of CommitRetaining.
Please note that we have changed the default value of the TIBCQuery.Options.PrepareUpdateSQL property from True to False in IBDAC 4.2.7 for performance improving.

[ralle1]

Unfortunately this is not quite correct. I am using Delphi XE2 with IBDAC 4.5.9
Sorry for not communicate this information.

And I use the default value. I just checked it.
TIBCQuery.Options.PrepareUpdateSQL is set to False.

[AndreyZ]

I have checked your code using IBDAC 4.5.9, and IBDAC correctly calls the Commit method instead of CommitRetaining. You are right, when using your code, "Oldest transaction", "Oldest active", and "Oldest snapshot" do not change and "Next transaction" changes. It is a correct behaviour, because you have two transactions, one for reading data and one for writing. When you open IBCQuery, IBDAC starts DefaultTransaction to read data from the TESTTABLE table. "Oldest transaction", "Oldest active", and "Oldest snapshot" do not change because DefaultTransaction is active. When you add new records, IBDAC starts UpdateTransaction and commits it for each record, that is why the value of "Next transaction" grows. If you do not want this to happen, you can start UpdateTransaction manually before adding new records and commit all changes afterwards. Here is a code example:

Code: Select all

    IBCQuery.SQL.Text := 'SELECT * FROM TESTTABLE';
    IBCQuery.ReadOnly := false;
    IBCQuery.Open;

    IBCQuery.AutoCommit := False;
    IBCQuery.UpdateTransaction.StartTransaction;
    repeat
      inc(id);
      IBCQuery.Insert;
      IBCQuery.FieldByName('ID').AsInteger := id;
      IBCQuery.Post;
      Label1.Caption := IntToStr(id);
      Application.ProcessMessages;
    until (Cancel);
    IBCQuery.UpdateTransaction.Commit;

[ralle1]

Thank your for investing your time in my problem!

Your explanation seems to be logical. But if I always close the IBCQuery after each Insert, the DefaultTransaction will no longer be active and because of "WHERE ID IS NULL" none of the new records is selected. "Oldest transaction" etc. just the same.

Can you explain this behaviour please?

Here is a code example:

Code: Select all

    IBCQuery.Close;
    IBCQuery.SQL.Text := 'SELECT * FROM TESTTABLE WHERE ID IS NULL';
    IBCQuery.ReadOnly := false;

    repeat
      inc(id);
      IBCQuery.Open;
      IBCQuery.Insert;
      IBCQuery.FieldByName('ID').AsInteger := id;
      IBCQuery.Post;
      IBCQuery.Close;
      Label1.Caption := IntToStr(id);
      Application.ProcessMessages;
    until (Cancel);

[AndreyZ]

This is a correct Firebird behaviour. Firebird returns old values of "Oldest transaction", "Oldest active", and "Oldest snapshot" until a new transaction is started. You can check this using the following code:

Code: Select all

    IBCQuery.Close;
    IBCQuery.SQL.Text := 'SELECT * FROM TESTTABLE WHERE ID IS NULL';
    IBCQuery.ReadOnly := false;

    repeat
      inc(id);
      IBCQuery.Open;
      IBCQuery.Insert;
      IBCQuery.FieldByName('ID').AsInteger := id;
      IBCQuery.Post;
      IBCQuery.Close;
      Label1.Caption := IntToStr(id);
      Application.ProcessMessages;
    until (Cancel);

    DefaultTransaction.Rollback;

    // shows old values
    Memo1.Lines.Add(IntToStr(IBCCon.DatabaseInfo.InfoOldestTransaction));
    Memo1.Lines.Add(IntToStr(IBCCon.DatabaseInfo.InfoOldestActive));
    Memo1.Lines.Add(IntToStr(IBCCon.DatabaseInfo.InfoOldestSnapshot));
    Memo1.Lines.Add(IntToStr(IBCCon.DatabaseInfo.InfoNextTransaction));
    Memo1.Lines.Add('');

    DefaultTransaction.StartTransaction;

    // shows new values
    Memo1.Lines.Add(IntToStr(IBCCon.DatabaseInfo.InfoOldestTransaction));
    Memo1.Lines.Add(IntToStr(IBCCon.DatabaseInfo.InfoOldestActive));
    Memo1.Lines.Add(IntToStr(IBCCon.DatabaseInfo.InfoOldestSnapshot));
    Memo1.Lines.Add(IntToStr(IBCCon.DatabaseInfo.InfoNextTransaction));
    Memo1.Lines.Add('');

We already had similar requests from our users and we discussed it with Firebird developers. They confirmed that it is a correct behaviour.

[ralle1]

OK, thank you for these informations! Can you tell me one more thing: Which transaction will be used by ExecSQL respectivly Execute in this example:

Code: Select all

    
IBCQuery.ReadOnly := true;
IBCQuery.Connection := IBCCon;
IBCQuery.Transaction := DefaultTransaction;
IBCQuery.UpdateTransaction := UpdateTransaction;
IBCQuery.SQL.Text := 'UPDATE TESTTABLE SET VAL=123 WHERE ID=2';
IBCQuery.ExecSQL;

[AndreyZ]

In this case, DefaultTransaction is used.

[ralle1]

OK. What's your opinion, is it possible to do something like this or is there any reason to use the DefaultTransaction:

Code: Select all

IBCQuery.Transaction := UpdateTransaction;
UpdateTransaction.StartTransaction;
IBCQuery.ExecSQL;
UpdateTransaction.Commit;
IBCQuery.Transaction := DefaultTransaction;

[AndreyZ]

Yes, it is possible to use such code. It is up to you to decide what transaction to use in each particular situation.

[ralle1]

OK, thank you very much. While testing I noticed the following behavior:
I have exended the loop in the above example:
I use in each continuous a Stored Procedure, which uses a generator, something like this: „ID = GEN_ID(GENERATORNAME,1)“. Now, the values "Oldest transaction" etc. is increasing too. I don't understand
Can you explain this?

[AndreyZ]

InterBase and Firebird require an active transaction for any operation under data. So, when you execute your stored procedure, a new transaction is started and committed, which leads to increasing of the "Oldest transaction", etc. values.
Please note that IBDAC does not control the "Oldest transaction", "Oldest active", "Oldest snapshot", and "Next transaction" values, they are controlled by Firebird. They can change when any transaction-related operations are performed. If you want to know more about these values and situations when they change their values, please consult with Firebird developers.

[ralle1]

I understand that it’s a firebird issue. But with all of your information you have helped me a lot. Thank you for the very good support. I will try to rebuild everything and to use "Update Transaction" respectively explicitly transactions, so that the DefaultTransaction can be set to IsolationLevel ReadOnlyReadCommitted. So I hope the performance will be consistently good.

Thank you!

[AndreyZ]

I am glad I could help. Feel free to contact us if you have any further questions about IBDAC.