#1 Re: mORMot Framework » Firebird transactions » 2021-08-08 00:01:02

vs

It looks like firebird 4 solves that issue of commit retaining transaction

#2 Re: mORMot Framework » Firebird transactions » 2021-05-31 17:21:40

vs

I think it is a bad idea to set up an AutoCommit for transaction for a Rest Server that has as backend firebird database.
Below is a snippet from The Firebird Book: A Reference for Database Developers Copyright © 2004 by Helen Borrie and IBPhoenix

COMMIT with the RETAIN Option

The optional RETAIN [SNAPSHOT] extension to the COMMIT statement causes the server to retain a “snapshot” of the physical transaction’s context at the time the statement is executed and start a new transaction as a clone of the committed one. If this so-called soft commit is used on a SNAPSHOT or SNAPSHOT TABLE STABILITY transaction, the cloned transaction preserves the same snapshot of the data as the original transaction had when it started.

Although it does commit the work permanently and thus change the state of the database, COMMIT RETAIN (CommitRetaining) does not release resources. In the lifespan of a logical task that comprises many repetitions of a similar operation, cloning the context reduces some of the overhead that would be incurred by clearing resources each time with COMMIT, only to allocate the identical resources all over again when a new transaction is started. In particular, it preserves the cursors on sets selected and currently “open.”

The same TID remains active in the TSB and never appears there as “committed.” For this reason, it is often referred to as soft commit, in contrast with the “hard” commit performed by an unmodified COMMIT statement. Each soft commit is like a savepoint with no return. Any subsequent ROLLBACK reverses only the changes that have been posted since the last soft commit. The benefit of the soft commit is that it makes life easy for programmers, especially those using components that implement “scrolling dataset” behavior. It was introduced to support the data grid user interface favored by many users of the
Borland Delphi development environment. By retaining the transaction context, the application can display a seamless before-to-after transition that reduces the effort the programmer would otherwise need to invest in starting new transactions, opening new cursors, and resynchronizing them with row sets buffered on the client. Data access implementations frequently combine posting a single update, insert or delete statement with an immediate COMMIT RETAIN in a mechanism that is dubbed “Autocommit.” It is common for interface layers that implement Autocommit capability to “dumb out” explicit control of transactions by starting one invisibly in situations where the programmer-written code attempts to pass a statement without first starting a transaction itself.

Explicit transaction control is worth the extra effort, especially if you are using a connectivity product that exploits the flexible options provided by Firebird. In a busy environment, the COMMIT RETAIN option can save time and resources, but it has some serious disadvantages:

•  A snapshot transaction continues to hold the original snapshot in its view, meaning the user does not see the effects of committed changes from other
transactions that were pending at the start of the transaction.

•  As long as the same transaction continues being committed with RETAIN, resource “housekeeping” on the server is inhibited, resulting in excessive growth
of memory resources consumed by the TSB. This growth progressively slows down performance, eventually “freezing” the server and, under adverse operating system conditions, even causing it to crash.

•  No old record versions made obsolete by operations committed by a COMMIT RETAIN transaction can be garbage collected as long as the original transaction
is never “hard committed.”

#3 Re: mORMot Framework » Firebird transactions » 2021-05-14 16:34:11

vs

@AB
Thank you for the clarification about AutoFree().

@Michael,
Several tests have shown that only in such configuration record versions are not created.
Please, see an sample. vConnection.AutoCommit := False; and vConnection.Commit; After each Upadte/Delete request.

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, ZConnection;

var
  vConnection: TZConnection;
  i: integer;

begin
  vConnection := TZConnection.Create(nil);
  try
    vConnection.HostName := 'localhost';
    vConnection.Database := 'd:\Projects\Tests\REC_VERSION_TEST.FDB';
    vConnection.Protocol := 'firebird';
    vConnection.User := 'sysdba';
    vConnection.Password := 'masterkey';
//    vConnection.Properties.Add('hard_commit=true');
//    vConnection.AutoCommit := True;
    vConnection.AutoCommit := False;

    vConnection.Connect;
    i := 0;
    while True do
      begin
        inc(i);
        if vConnection.ExecuteDirect('UPDATE SIMPLERECORD SET CHANGETIME = CURRENT_TIMESTAMP') then
          begin
            vConnection.Commit;
            Writeln('Udate #' + i.ToString);
          end;
        sleep(100);
      end;
    Readln;
  finally
    vConnection.Free;
  end;
end.

When vConnection.AutoCommit := True, value of field *CHANGETIME* is changing.
But, it looks like CommitRetaining not free the transaction environment, so not reset the ID of the oldest active transaction.

@AB
Is it possible at the ORM level to control the transactions behavior?
Thank you!

#4 Re: mORMot Framework » Firebird transactions » 2021-05-11 11:13:20

vs

Please follow the forum rules and don't post huge piece of code or logs.

I'm sorry for that. I will take it into account.
Is 120 lines of code a huge? Can I post a tweaked example here or do I need to save it to an external resource?

Anyway, changing AutoFree () to explicit Create/Free did not solve the problem with record versions.

@AB,
do you recommend not using AutoFree () anymore? (https://synopse.info/forum/viewtopic.ph … 947#p33947)
It was a very useful thing.
Thanks!

#5 Re: mORMot Framework » Firebird transactions » 2021-05-10 22:38:09

vs

After Server restarting

...
        Oldest transaction      119
        Oldest active           120
        Oldest snapshot         120
        Next transaction        122
...
SIMPLERECORD (128)
...
     Average version length: 9.00, total versions: 113, max versions: 113

So, Oldest active           120 was changed
but total versions: 113        remained the same since there was no read from the table SIMPLERECORD
When the client was started second time

Udate #15
Udate #16
Udate #17
Udate #18
Udate #19

PS>

...
        Oldest transaction      120
        Oldest active           121
        Oldest snapshot         121
        Next transaction        142
...
SIMPLERECORD (128)
...
    Average version length: 9.00, total versions: 19, max versions: 19

So, sweep has been performed.
But, the problem is that the server keeps an active transaction for the first connection, which gives rise record versions.

#6 Re: mORMot Framework » Firebird transactions » 2021-05-10 22:06:49

vs

@Michael,
@AB
Thanks alot for your help resolving this issue!
But from my point of view issue still exists.
I'm using mORMot 1.18.6286 (mORMot_and_Open_Source_friends_2021-05-06_064801_575871d143) and zeoslib-code-0-r7537-trunk
Source code of test app

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, SynCommons, system.Classes, SynDB, SynTable, ZDbcIntfs, SynDBZeos, mORMot, mORMotDB, mORMotSQLite3,
  SynSQLite3Static, mORMotHttpServer, mORMotHttpClient;

type
  TSimpleRecord = class(TSQLRecord)
  private
    FChangeTime: TDateTime;
  published
    property ChangeTime: TDateTime read FChangeTime write FChangeTime;
  end;

var
  vCommandLine: TCommandLine;
  vMode: RawUTF8;

const
  cServerPort = '8080';
  cConnectStr = '{"Kind":"TSQLDBZEOSConnectionProperties","ServerName":"firebird://",' +
                '"DatabaseName":"d:\\Projects\\Tests\\REC_VERSION_TEST.FDB","User":"SYSDBA","Password":"HuYxjMYsqbe2"}'; //HuYxjMYsqbe2  = masterkey

procedure RunServer;
var
  vDBConnectionParams: TSQLDBZEOSConnectionProperties;
  vModel: TSQLModel;
  vRestServer: TSQLRestServerDB;
  vHttpServer: TSQLHttpServer;
  vSimpleRecord: TSimpleRecord;
begin
  Writeln('RunServer');
  TAutoFree.One(vDBConnectionParams, TSQLDBZEOSConnectionProperties.CreateFromJSON(cConnectStr) as TSQLDBZEOSConnectionProperties);
//  vDBConnectionParams.ZeosURL.Properties.Add('hard_commit=true');
  vModel := TSQLModel.Create([TSimpleRecord]);
  VirtualTableExternalRegisterAll(vModel, vDBConnectionParams, True);
  TAutoFree.One(vRestServer, TSQLRestServerDB.Create(vModel, ':memory:'));
  vRestServer.Model.Owner := vRestServer;
  vRestServer.AcquireExecutionMode[execORMGet] := amBackgroundORMSharedThread;
  vRestServer.AcquireExecutionMode[execORMWrite] := amBackgroundORMSharedThread;
  vRestServer.CreateMissingTables;
  if vRestServer.TableRowCount(TSimpleRecord) = 0 then
    begin
      TAutoFree.One(vSimpleRecord, TSimpleRecord.Create);
      vSimpleRecord.ChangeTime := Now;
      vRestServer.Add(vSimpleRecord, True);
    end;
  TAutoFree.One(vHttpServer, TSQLHttpServer.Create(cServerPort, [vRestServer], '+', useHttpApiRegisteringURI));
  vHttpServer.AccessControlAllowOrigin := '*';
  Writeln('Server is now running on http://localhost:' + cServerPort + '/root'#13#10#13#10+'Press [Enter] to quit');
  Readln;
end;

procedure RunClient;
var
  i: Integer;
  vModel: TSQLModel;
  vHttpClient: TSQLHttpClient;
  vSimpleRecord: TSimpleRecord;
begin
  Writeln('RunClient');
  vModel := TSQLModel.Create([TSimpleRecord]);
  TAutoFree.One(vHttpClient, TSQLHttpClient.Create('localhost', cServerPort, vModel));
  vHttpClient.Model.Owner := vHttpClient;
  TAutoFree.One(vSimpleRecord, TSimpleRecord.Create);
  vHttpClient.Retrieve(1, vSimpleRecord);
  i := 0;
  while true do
    begin
      inc(i);
      vSimpleRecord.FChangeTime := now;
      if vHttpClient.Update(vSimpleRecord) then
        Writeln('Udate #' + i.ToString);
      Sleep(100);
      if ConsoleKeyPressed(13) then break;
    end;
  Readln;
end;

begin
  TAutoFree.One(vCommandLine, TCommandLine.Create);
  vMode := vCommandLine.AsUTF8('mode', 'S', '');
  case vMode[1] of
    'S', 's': RunServer;
    'C', 'c': RunClient;
  end;
end.

Output of Project1.exe -mode C

....
Udate #96
Udate #97
Udate #98
Udate #99
Udate #100
Udate #101
Udate #102
Udate #103
Udate #104
Udate #105
Udate #106
Udate #107
Udate #108
Udate #109
Udate #110
Udate #111
Udate #112
Udate #113

PS>

Output of gstat -u sysdba -p masterkey -r d:\Projects\Tests\REC_VERSION_TEST.FDB

$ gstat -u sysdba -p masterkey -r d:\Projects\Tests\REC_VERSION_TEST.FDB

Database "d:\Projects\Tests\REC_VERSION_TEST.FDB"
Database header page information:
        Flags                   0
        Checksum                12345
        Generation              120
        Page size               4096
        ODS version             11.2
        Oldest transaction      1
        Oldest active           1
        Oldest snapshot         1
        Next transaction        118
        Bumped transaction      1
        Sequence number         0
        Next attachment ID      1
        Implementation ID       16
        Shadow count            0
        Page buffers            0
        Next header page        0
        Database dialect        3
        Creation date           May 10, 2021 23:51:00
        Attributes              force write

    Variable header data:
        *END*


Database file sequence:
File d:\Projects\Tests\REC_VERSION_TEST.FDB is the only file

Analyzing database pages ...
SIMPLERECORD (128)
    Primary pointer page: 168, Index root page: 169
    Average record length: 17.00, total records: 1
    Average version length: 9.00, total versions: 113, max versions: 113
    Data pages: 1, data page slots: 1, average fill: 73%
    Fill distribution:
         0 - 19% = 0
        20 - 39% = 0
        40 - 59% = 0
        60 - 79% = 1
        80 - 99% = 0

    Index NDXSIMPLERECORDID (1)
        Depth: 1, leaf buckets: 1, nodes: 1
        Average data length: 9.00, total dup: 0, max dup: 0
        Fill distribution:
             0 - 19% = 1
            20 - 39% = 0
            40 - 59% = 0
            60 - 79% = 0
            80 - 99% = 0

    Index RDB$PRIMARY1 (0)
        Depth: 1, leaf buckets: 1, nodes: 1
        Average data length: 9.00, total dup: 0, max dup: 0
        Fill distribution:
             0 - 19% = 1
            20 - 39% = 0
            40 - 59% = 0
            60 - 79% = 0
            80 - 99% = 0

Note for this value, please.

...
        Oldest transaction      1
        Oldest active           1
        Oldest snapshot         1
        Next transaction        118
...
SIMPLERECORD (128)
...
    Average version length: 9.00, total versions: 113, max versions: 113

Client was closed.
Server still running and hold
Oldest active            1
So, I have
total versions: 113, max versions: 113 equal to the number of updates to the record in the SIMPLERECORD table
Maybe I'm missing something?
Thanks!

#7 Re: mORMot Framework » Firebird transactions » 2021-04-26 16:40:40

vs

I found a solution. Switching to the FireDAC library.
If I try this code,

  while true do
    begin
      inc(i);
      vSQLRecordSample.LastLogin := now;
      if FRestServer.Update(vSQLRecordSample) then
        Writeln('Udate #' + i.ToString);
      Sleep(100);
      if ConsoleKeyPressed(13) then break;
    end;

the number of record versions is 1!

#8 Re: mORMot Framework » Firebird transactions » 2021-04-26 16:32:08

vs
ab wrote:

I suppose the automatic transaction used within a BATCH process works as expected, right?

I tried code like this

  while true do
    begin
      inc(i);
      vBatch := TSQLRestBatch.Create(FRestServer, TSQLRecordSample, 5);
      try
        vSQLRecordSample.LastLogin := now;
        vBatch.Update(vSQLRecordSample);
        vBatch.Update(vSQLRecordSample);
        vBatch.Update(vSQLRecordSample);
        vBatch.Update(vSQLRecordSample);
        vBatch.Update(vSQLRecordSample);
        FRestServer.BatchSend(vBatch);
      finally
        vBatch.Free;
      end;
        Writeln('Udate #' + i.ToString);
      Sleep(300);
      if ConsoleKeyPressed(13) then break;
    end;

Result remains the same. Amount of record versions is growing at each FRestServer.BatchSend

#9 Re: mORMot Framework » Firebird transactions » 2021-04-20 12:43:37

vs

Hi Michael!
Thanks for your help.

I can't see a zeos regression. Hope you use minimum version v7.2.10 or v8.0 from trunk.

Yes, I am using version 7.2.10-stable build at 2021-01-12 08:55:31

If you have such "old" transactions than you have open cursors somewhere propably

There is no explicit cursor control in my code.

Take care you read all results until no more row is returned. Another cause of many ransactions might be nested transactions with multiple connections

Only one record is read and only one client connection is established.

My test has a server and one client that executes code like this

    TAutoFree.One(vMySQLRecord, TMySQLRecord.Create);
    FHttpClient.Retrieve(1, vMySQLRecord);
    i := 0;
    while true do
      begin
        inc(i);
        vMySQLRecord.LastCheckTime := now;
        FHttpClient.Update(vMySQLRecord);
        Writeln('Udate #' + i.ToString);
        Sleep(300);
      end;

Actually there are two connections to the database.
First from the main server thread and second from the ThreadSafeConnection, which occurs when the client is selecting data for vMySQLRecord (FHttpClient.Retrieve(1, vMySQLRecord);)

Moreover, if after some time on the server side execute

FDBConnectionParams.ThreadSafeConnection.Disconnect;

For some reason first connections will close and client continues to successfully update vMySQLRecord through the second connection.
And the saddest thing, the ID of the oldest active transaction is equal to the ID of first transaction of the second connection. This is the reason lot of record versions.

Note a second call to IZConnection.StartTransaction creates a savepoint. First call to IZConnection.Commit/Rollback just releases/rollback the savepoint but not the transaction you wanna close.

This is interesting, but the data in the database are updated, therefore commit occurs and savepoint is released.

According the "hard_commit" option: If you are adding that option after creating a transaction then the option is a NOOP.

FDBConnectionParams.ZeosURL.Properties.Add('hard_commit=true')

This code is executed before the call

//SynDBZeos
constructor TSQLDBZEOSConnection.Create(aProperties: TSQLDBConnectionProperties);
var url: TZURL;
begin
  inherited Create(aProperties);
  url := (fProperties as TSQLDBZEOSConnectionProperties).fURL;
  fDatabase := DriverManager.GetConnectionWithParams(url.URL,url.Properties);
  // EG: setup the connection transaction behavior now, not once Opened in Connect
  //fDatabase.SetReadOnly(false); // is default
  // about transactions, see https://synopse.info/forum/viewtopic.php?id=2209
  //fDatabase.SetAutoCommit(true); // is default
  fDatabase.SetTransactionIsolation(tiReadCommitted); // will be swapped to tiSerialiable for SQLite
end;

In this case url.Properties.Commatext = 'codepage=UTF8,hard_commit=true'

#10 Re: mORMot Framework » Firebird transactions » 2021-04-19 21:30:05

vs

A little more details.
When the server is restarted, the first client connection and selection from this table of course started garbage collection (delete record versions).
But for a 24/7 service this is certainly not a good solution.
The question is, how to correctly and safetly close the transaction that occurs when a ThreadSafeConnection is created?
Thanks!

Addon.
Maybe this is more related to Zeos Database Objects transaction handling than ORM.
Please give your advice.
Thanks!

#11 Re: mORMot Framework » Firebird transactions » 2021-04-19 12:09:24

vs

Unfortunately, both

FDBConnectionParams.ZeosURL.Properties.Add('hard_commit=true')

and

FDBConnectionParams.ZeosURL.Properties.Add('hard_commit=false')

same result

#12 Re: mORMot Framework » Firebird transactions » 2021-04-18 17:50:50

vs

Hi.
Faced such a case.
There is a table with 50 records.
Each record is updated every minute from FHttpClient: TSQLHttpClient, 24/7.
As a result, a lot of record version accumulated for this table, since the ID of the oldest active transaction is equal to the ID of the first update transaction for this table at server start.

Maybe someone knows how to handle this?
Thanks!

#13 Re: mORMot Framework » Error on call TSQLHttpClientWinHTTP.UpdateField » 2019-10-21 16:21:43

vs

Looks like it's already fixed.
Please check https://synopse.info/fossil/info/530adf5475e9af5

ab,
Is it by design, that in mORMotDB.TSQLRestStorageExternal.EngineUpdateField/EngineUpdateFieldIncrement no update of TRecordVersion fields?
Thanks.

Board footer

Powered by FluxBB