Firebird 2.5 multiuser update issues

[JD]

Hi there everyone,

I have an n-tier application in production written in Lazarus 1.6/FPC 3.0 & based on Firebird 2.5 (SuperClassic configuration) and Zeos 7.2RC. Recently, some of my users have noted that changes made by some users are not seen immediately by other users.

The code I used to set up the Zeos connection is as shown below

Code: Select all

  // Zeos method
  //
  with SQLConnection do
  begin
    // Create the dB connection to a Firebird database
    //SQLConnection := TZConnection.Create(nil);

    // This is the default IP if the config file does not specify an alternative path
    HostName                := AHostName;
    // A zero hostname or HTTP Port #80. Zero if hostname is localhost and #80
    // otherwise
    Port                    := APort;
    Database                := TNSNAME;
    Protocol                := 'firebird-2.5';
    User                    := LOGIN_ID;
    Password                := LOGIN_PW;
    AutoCommit              := False;
    TransactIsolationLevel  := tiReadCommitted;

    // UTF-8 connection properties
    with Properties do
    begin
      Add('character_set_client=utf8');
      Add('character_set_connection=utf8');
      Add('character_set_database=utf8');
      Add('character_set_results=utf8');
      Add('character_set_server=utf8');
      Add('character_set_system=utf8');
      Add('collation_connection=utf8_general_ci');
      Add('collation_database=utf8_general_ci');
      Add('collation_server=utf8_general_ci');
      Add('Codepage=utf8');
      Add('isc_tpb_concurrency');       // needed for multiuser environments
      Add('isc_tpb_nowait');            // needed for multiuser environments
      Add('isc_tpb_read_committed');    // Added on 04/12/2015 source: http://tracker.firebirdsql.org/browse/CORE-3108
      Add('isc_tpb_rec_version');       // Added on 04/12/2015 source: http://tracker.firebirdsql.org/browse/CORE-3108
    end;      // with Properties do

    // Connect to the database
    Connect;
  end;

In addition, I commit every transaction after changes are saved to the database. Is there anything I might have missed in the properties that may prevent users from seeing changes made by other users immediately.

I was even wondering if I need to change the Firebird configuration to SuperServer but I'm not so sure about it.

Thanks for your kind assistance.

JD

[marsupilami]

Hello JD,

as far as I know, the behaviour should be the same on Super Server. I wonder wether this might be a case of using commit retaining. Unfortunately I can't check that currently...
Best regards,

Jan

[marsupilami]

Hello JD,

sooo - I checked the Zeos sources. By default Zeos uses soft commits or "commit reatain". You might want to look that up in the Firebird Book from Helen Borrie or in the Firebird 2.5 Language Reference. The soft commit combined with tiReadCommitted might lead to users not seeing the changes of other users.
If you want to try hard commits, you might want to try to set the hard_commit option to 1 or true.

HTH,

Jan

[miab3]

@JD,

With SuperServer can be better, it shared (one) cache space for all connections.

Michal

[JD]

Thanks a lot for your replies Jan & Michal. I'll put each of your suggestions to the test & report on my findings.

JD

[JD]

Hello JD,

sooo - I checked the Zeos sources. By default Zeos uses soft commits or "commit reatain". You might want to look that up in the Firebird Book from Helen Borrie or in the Firebird 2.5 Language Reference. The soft commit combined with tiReadCommitted might lead to users not seeing the changes of other users.
If you want to try hard commits, you might want to try to set the hard_commit option to 1 or true.

HTH,

Jan

Hi there Jan. How do I set the hard_commit option to 1 or true?

JD

[marsupilami]

Hello JD

just add the line hard_commit=1 in the options property in your TZConnection. Programmatically that would look like this:
ZConnection.Options.Add('hard_commit=1');

With best regards,

Jan