Firebird transactions

ttomas · 2014-12-04 00:57:46

ab wrote:

In fact, in mORMot ORM we propose the TSQLRestBatch mode which implements the "Unit of Work" pattern, and is faster and using less resource than explicit SQL level transactions.

Do you test speed? For PostgreSQL auto commit work on server side with inserting begin and commit for every statements.
If you send
INSERT 1
INSERT 2
INSERT N
server will convert to
BEGIN
INSERT 1
COMMIT
BEGIN
INSERT 2
COMMIT
BEGIN
INSERT N
COMMIT
If you use explicit SQL level transaction in "Unit of Work"
BEGIN
INSERT 1
INSERT 2
INSERT N
COMMIT
No conversion on server side, also speed will increase

ab wrote:

BTW I suspect the "explicit COMMIT" needed by FB when AutoCommit is TRUE is why ZDBC is slower than FireDAC for single INSERT operations.
In fact, one TSQLRest.Add() operation would need to perform two statements each time, i.e. INSERT + COMMIT, so it is slower than a direct auto-committed INSERT.
Perhaps we may be able to tune it for FB or at least for PostgreSQL, which support an "auto-commit" mode.

ZDBC and FireDAC use same client api fbclient.dll. Auto Commit is software emulated by ZDBC and FireDAC. In firebird API you must at minimum start transaction, execute statement and commit transaction. This is minimum 3 network request/response to Firebird server. Commit is not a statement in firebird api (yes it is network request to server). ZDBC is slower then FireDAC because of bad implementation of api or maybe FireDAC don't commit all data in time (commiting data on disconnect)

miab3 · 2014-12-04 09:37:01

@ab, All

According to my knowledge ZEOS always starts transaction after Connect and Commit/Rollback(at the lowest level).

so
(in AutoCommit mode (Default))

ExecSQL;
ExecSQL;

or

AutoCommit := false;
ExecSQL;
ExecSQL;
Commit/Rollback

StartTransaction - disables (temporarily) AutoCommit mode and starts an additional
transaction then Commit/Rollback refers to the current transaction.
So

StartTransaction;
ExecSQL;
ExecSQL;
Commit/Rollback;

is acceptable.

Michal

Last edited by miab3 (2014-12-05 10:08:13)

ab · 2014-12-08 08:21:38

@ttomas3
Yes, we test speed, and you can run yourself the tests via the sample 15.
I'm not convinced about your statements for FireDAC: ZDBC API of FireBird sounds well written, especially in the latest 7.2 branch we use, which has been optimized.
But what is less optimized is that AFAIK FireDAC is using the isc_tpb_autocommit parameter, whereas ZDBC uses an explicit COMMIT. This may explain the speed difference.

We have committed a patch about transactions, and some enhancements proposed by Michael.
http://synopse.info/fossil/info/927c4b1e66

EgonHugeist · 2014-12-11 21:29:47

@ab

i did check you FireDAC implementation. I can not find the AutoCommit mode you're using for Zeos. But FD supports it too: http://docwiki.appmethod.com/appmethod/ … FireDAC%29

So comparing a non autocommit Component with a ZDBC which definitely commits after each update(because you want it), is like comparing a froot-store with a apple.
To be fair you should either remove the autocommit on Zeos (again you are always in transactional mode until you close the connection) or you're playing a fair game and implement the autocommit for FD too.

Note i did bug out last FB TIL issues. current 7.2 changes the TIL like you want to have it now. I also did test your suggested parameter: It kills the performance in AutoCommit+Batch-mode(it does commit the execute block after each execution but i want to handle the batch idea as !one! update and only one commit should follow. So i rolled back the patch today and let Zeos selve control the auto-commit mode.

Where i agree: Current ZDBC + FB can't create subsequential Transactions. Also you and your users should know if you're changing the TIL on the fly your statement-cache should be flushed before. See same link of FD.

Michael

Last edited by EgonHugeist (2014-12-11 21:41:36)

ttomas · 2014-12-16 09:32:04

Another bad behavior of ZeosDBO is Auto start of transaction. If you use connection pool you will have shifting of transaction logic, transaction is started in previous Unit of work and commited in current

Unit of work1
   // transaction is started in Unit of work 0
   Exec Statement11
   Exec Statement12
   Commit Transaction 1
   Start Transaction 2

Unit of work2
   // transaction is started in Unit of work 1 (Start Transaction 2)
   Exec Statement21
   Exec Statement22
   Commit Transaction 2
   Start Transaction 3

If for some reason Unit of work 1 don't commit or rollback Transaction 1, Commit Transaction 2 in Unit of work 2 will commit Exec Statement11, Exec Statement12 from Unit of work1. I expect:

Unit of work N
   Start Transaction N
   Exec StatementN1
   Exec StatementN2
   Commit Transaction N

This behavior can be confirmed with monitoring (trace) FB server.

ab · 2014-12-16 17:41:47

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

EgonHugeist · 2014-12-16 20:05:25

ttomas wrote:

Another bad behavior of ZeosDBO is Auto start of transaction. If you use connection pool you will have shifting of transaction logic, transaction is started in previous Unit of work and commited in current
...
This behavior can be confirmed with monitoring (trace) FB server.

? About which mode do we talk here? Did you set "hard_commit=true"? Else i can't confirm your findings? Subsequential transactions are NOT supported yet. There is no TA1, 2 or 3! Required savepoints e.g. FD-Docs are not implmented yet.

Note I'm NOT the initial autor of Zeos. Just want to support you guys as good as i can. The behavior is known since years. (Even if i know this is a show-stopper if you are not aware of that!)

Thinking about it .. might be a option for 7.3.. Not sure yet Loads of work is waiting for current 7.3 development..

@AB
are you talking about Daniel's issues? Hope i got it resolved inbetween...? Let's wait for reply.. I wouldn't start from the premisse the issue remains, Arnaud.

Michael

ab · 2014-12-17 08:33:34

Michael, you are doing great and your support is just awesome!

About Daniel's issue, we were not able to reproduce it in a reproducable manner.
Current implementation sounds stable enough, even if FireBird has a somewhat diverse behavior.

We could change our ORM code to explicitly create transactions for all process.
I suspect it may be not a good idea about performance for individual row writing... but it would depend on the client library itself, I guess.

So from my side, I would stick with the current implementation.
Unless some reproducible issue is identified.

ttomas · 2014-12-17 23:53:46

Sorry for delay answer. This is more Zeoslib problem in transaction management.
For confirmation this is trace log for "28 - Simple RESTful ORM Server" from today mORMotNightlyBuild and today Zeoslib svn, only changes in server is:

  SynDBZEOS,
//  SynDBODBC,           // external DB access via ODBC
...
  aProps := TSQLDBZEOSConnectionProperties.Create('zdbc:firebird-2.5://Tomi-PC1:3025/c:\test\test.fdb?username=sysdba;password=masterkey;LibLocation=fbclient.dll','','','');
//  aProps := TODBCConnectionProperties.Create('','Driver=PostgreSQL Unicode'+
//      {$ifdef CPU64}'(x64)'+{$endif}';Database=postgres;'+
//      'Server=localhost;Port=5432;UID=postgres;Pwd=postgresPassword','','');

Firebird trace for start server, 3 times start client, stop server can be downloaded from:
http://halkyon.com/download/Trace1.zip
Look for Transaction ID column.
Ex. In start2 select from person is executed in transaction 12 and in same transaction is executed insert from start3.
Commit retaining on firebird server side is = to commit + start new transaction.
Also notice that transaction 9 and statement 44 is keep open to the end of server process. Do we need this open connection, transaction, statement (cursor)?
With "hard_commit=true" server crash on start no active transaction is started.

EgonHugeist · 2014-12-18 06:56:14

@ttomas

Where can i download this FB-tracing app?

Looking to your results:
I'm starting from the premisse you do NOT use latest \testing-7.2 sources from SVN or latest mORMot?

Current mORMot starts a ReadCommited TIL. As i wrote several times: before latest patches Zeos did Never change the TIL. Using latest 7.2 from SVN Zeos closes the tpb_concurrency (FB default) TA and restarts a new TA with ReadCommited transaction behavior.
Also is it possible to change the HARD_COMMIT on the fly by settinge/dropping this Parameter to/from the ZURL.Properties.

To be clear and on same bandwidth: Please update mORMot and Zeos from SVN, resart your tracing and attach it again.

Michael

ttomas · 2014-12-18 12:24:08

@EgonHugeist
Trace1.zip with Delphi2007, http://synopse.info/files/mORMotNightlyBuild.zip inside is folder mORMot_and_Open_Source_friends_2014-12-12_205233_dec5dae65c and Zeos trunk (my mistake).
Today I use mORMot from git https://github.com/synopse/mORMot.git, last commit 17.12.2014 and Zeos testing-7.2 from svn.
With testing-7.2 hard_commit=true working, no more server crash.

  aProps := TSQLDBZEOSConnectionProperties.Create('zdbc:firebird-2.5://Tomi-PC1:3253/d:\test\test.fdb?username=sysdba;password=masterkey;LibLocation=fbclient.dll;hard_commit=true','','','');

This is new trace log with and without hard_commit.
http://halkyon.com/download/Trace23.zip
With hard_commit=true you can see that transaction is started in prev request and commited in current.
I expect no active transaction on server if nobody is connected. We can keep Connection open, but no active transaction.
For trace I use IBExpert, menu Trace and audit, copy/paste grid to LibreOffice.
Thanks for all

EgonHugeist · 2014-12-18 19:40:34

@ttomas

Thanks for follow my advice. Results are what i expect and i wrote several times.

Of course having no open TA would be nice but for what exactly with FB?. Again: This is a documented behavior. You'll never ever be able to execute a stmt in any kind with FB and there is TA opend: FB/IB-Docs page 317 ... 336+ NO transaction handle -> raise an error. NO SELECT, simply NOTHING. That's FB/IB.

Common Access API by using interfaces, I assume that's why Zeos behaves like it currently does, but i don't know that for sure. From my POV "alltime code". After reading your logs: case closed for 7.2! It might change if minior version increases... Behavior is locked on 7.2.x All TA's started/Leveled/commited or rolled back as I expect it (:

Thank you for the IB-trace idea.

BTW:
@Daniel
what about the Dead-Lock issue? A week is over! Are you happy now?

Michael.

danielkuettner · 2014-12-19 08:23:52

@Michael
Yes I am. No deadlocks more up to now.

vs · 2021-04-18 17:50:50

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!

EMartin · 2021-04-19 11:27:34

Check if you is doing commit retaining instead of hard commit.

vs · 2021-04-19 12:09:24

Unfortunately, both

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

and

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

same result

vs · 2021-04-19 21:30:05

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!

Last edited by vs (2021-04-19 21:43:49)

EgonHugeist · 2021-04-20 03:51:25

Hi vs,
If you have such "old" transactions than you have open cursors somewhere propably. Take care you read all results until no more row is returned. Another cause of many ransactions might be nested transactions with multiple connections. 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.

According the "hard_commit" option: If you are adding that option after creating a transaction then the option is a NOOP.
I can't see a zeos regression. Hope you use minimum version v7.2.10 or v8.0 from trunk.

Hope that helps, Michael

Last edited by EgonHugeist (2021-04-20 03:52:34)

vs · 2021-04-20 12:43:37

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'

vs · 2021-04-26 16:32:08

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

vs · 2021-04-26 16:40:40

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!

Last edited by vs (2021-04-26 17:50:21)

EgonHugeist · 2021-05-06 05:11:52

@vs

that's a missing implementation in the FireDac implemenation of AB. Arnaud's implementation has no AutoCommit mode: http://docwiki.embarcadero.com/RADStudi … _(FireDAC). You can do same with Zeos8 using a non AutoCommit connection. The 'hard_commit' property simply is ignored for Firebird-ISC_TPB_AUTOCOMMIT transactions because of the retaining mode. I don't see a problem in Zeos but using FireDAC according it's documentation.

@AB,
iv'e noticed several issues using your performance FireBird tests without embedded mode(easy to reproduce):
Some resultsets are not release properly after the data was fetched. Some like 'select count(*) from sample_record' or 'select MAX(ID) from sample_record', or TORM.GetOne. The point is Zeos can't know if more rows are in queue, thus the orphan TXN is hold by the opened cursor and is waiting for getting no more rows to close the txn. It would be nice if the ORM classes free the resouces if the result was consumed. i.e. IZResultSet.ResetCursor.

All tabular streamed servers (except MySQL/PosgtreSQL which are caching the result in the client-lib) keep an open cursor too. (Sybase, MSSQL Server) f.e.

ab · 2021-05-06 06:49:44

@Michael,
From what I can see, TSqlDBZeosStatement.ReleaseRows calls fResultSet.ResetCursor.
I made a review, and found some missing ReleaseRows calls in the ORM code.
Should be fixed by https://synopse.info/fossil/info/575871d143 now - and for mORMot 2.

I don't find TOrm.GetOne in the source code. But I guess it is part of the fix.

Thanks a lot for the feedback!

vs · 2021-05-10 22:06:49

@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!

vs · 2021-05-10 22:38:09

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.

ab · 2021-05-11 06:46:25

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

Perhaps the (ab)use of AutoFree() may lead into resource leaks.
Please try to rewrite your sample with explicit in-order freeing for the server side.

vs · 2021-05-11 11:13:20

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!

ab · 2021-05-11 15:21:38

You could continue to use AutoFree() but you have to be warned that it won't work with FPC, nor the very latest of Delphi revisions.

vs · 2021-05-14 16:34:11

@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!

Last edited by vs (2021-05-14 23:10:10)

vs · 2021-05-31 17:21:40

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.”

vs · 2021-08-08 00:01:02

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

ttomas · 2021-09-12 17:50:32

One more ReleaseRow missing
https://synopse.info/forum/viewtopic.php?id=5978

ttomas · 2021-09-14 00:38:36

Inspired by transaction handling of ZeosLib and Firebird by this topic
and goal to have zero started transaction on Firebird server when nobody is connected
I create this mORMot2 database connection for FirebirdSQL.

For low level connection I use MWA Software Firebird Pascal API package (fbintf),
part of IBX for Lazarus.
More details on https://www.mwasoftware.co.uk/fb-pascal-api

- With explicit StartTransaction (Batch) all statements in connection
is executed with this main transaction owned by connection.
- If no explicit StartTransaction is called, all statements create internal transaction
on prepare and COMMIT after execution or Eof or ReleseRows. This is sotware simulated auto commit.
This internal transaction is owned by Statement.
- TSqlDBIbxConnectionProperties.CreateDescendingPK if set to True (Default is False)
will create only one descending PK index using statement
PRIMARY KEY(ID) using desc index PK_TableName
default dFirebird create two indexes on ID, one ascending, second descending
nedded for select max(ID)
see http://www.firebirdfaq.org/faq205

@ab
This feature need some change in mormot.orm.sql.pas at line 1883
For testing in my code i now have this improvisation, I change fEngineName when set this property :-)
dFirebird:
// see http://www.firebirdfaq.org/faq205
if fProperties.Engine='IBX1' then
begin
result := true;
exit;
end
else
Descending := true;

- Batch implemented for insert,update,delete using execute block
- ToDo Firebird4 API interface have new IBatch interface for insert/update
also implemented in fbintf package.
- You must patch FB30Statement.pas and FB25Statement.pas of fbintf, I'm waiting
for response of MWA Software. fbintf raise exception if Execute is executed
with different transaction then Prepare transaction.
Just comment this lines in InternalExecute and InternalOpenCursor and rebuild package.
more details on https://forum.lazarus.freepascal.org/in … 267.0.html

Source code mormot.db.sql.ibx.pas: https://gist.github.com/TTomas/2cb88c1c … a98f17a5b1

Last edited by ttomas (2021-09-14 01:33:25)

ab · 2021-09-14 09:50:14

This is a great input, TTomas!

I have integrated it (with some minor refactoring) as a new mormot.db.sql.ibx.pas beta unit.
https://github.com/synopse/mORMot2/comm … 0720794289

Thanks a lot for sharing.

Do you have any performance or stability numbers, in respect to the Zeos layer?

ttomas · 2021-09-15 00:03:49

Sorry for delay, busy day.
I send pull request with some bugfix and added Zeos and Ibx project files for mvc-blog example.
https://github.com/synopse/mORMot2/pull … 4015d14588
Main goal with mvc-blog sample is OK. Server create 32 thread pull connections after many hits, and zero (0) active transactions on idle, Zeos always keep 32+ started transactions.
Monitored with
select * from [MON$ATTACHMENTS, MON$TRANSACTIONS, MON$STATEMENTS]
This query's are really nice to find forgotten statement/cursor/transaction, usually by missing ReleaseRows in code like:
if step then
DoSomething();
// missing ReleaseRows

I test with code from test.orm.extdb.pas, USEZEOS define part, all pass.
Tested only on Windows with Fpc and Firebird 3, for now. Delphi users welcome to test, fbintf can be installed on D2010>=

This is numbers of 5 tests, Batch add/receive of 1111 rows.
https://gist.github.com/TTomas/b77f951b … 2ae5f29adc
Only interesting is Test1 (BatchAdd of 1111 records), Zeos is really fast, thanks to @EgonHugeist.
Need some testing to tune number of statements in execute block.
I'm planning to implement new Firebird4 IBatch interface also
Test2 is expected to be slower 1111 Retrieve, because Ibx driver for every statement (1111) StartTransaction, Execute and Commit,
Zeos only Execute statement, monitoring this with trace and audit on Firebird server.
Second Ibx test is with ZEOSTRANS define to simulate same behavior.

Real Performance 15-Samples is in ToDo list

Last edited by ttomas (2021-09-15 00:18:00)

ab · 2021-09-15 06:43:13

I have merged your pull request.
Thanks!

And numbers seem good.

ttomas · 2021-09-26 23:43:11

I just pull request https://github.com/synopse/mORMot2/pull/57
Included conversion of sample 15 and this is results:
Running tests using Synopse mORMot framework 2.0.1, compiled with Free Pascal 3.2 64 bit, against SQLite 3.36.0, on Windows 10 64bit (6.2.9200), at 2021-09-26 21:29:48.

Insertion speed (rows/second)
                              Direct      Batch       Trans       Batch Trans
Ibx firebird                  3128        39378       22353       41027
Zeos firebird                 3035        39629       24809       38625

Read speed (rows/second)
                              By one      All Virtual All Direct
Ibx firebird                  12283       122989      179513
Zeos firebird                 30407       162522      209178

Expected result for Read speed-By one (this is read of 5000 records one by one with start/read/commit transaction in Ibx, in this test Zeos never Commit or CommitRetaining transaction)

@EgonHugeist
I test with Zeos 7.2-patches.
8.0-patches have a problem with Batch inserts. Data is corrupted in table after batch inserts, also a lot of records are missing (not 5000 records after Batch). I try with and without MORMOT2 define, same problem.
Tested with Firebird 3.0.7 in embedded mode
i7-4700MQCPU

Same test on i3-8145U
Running tests using Synopse mORMot framework 2.0.1, compiled with Free Pascal 3.2 64 bit, against SQLite 3.36.0, on Manjaro Linux - Linux 5.4.148-1-MANJARO, at 2021-09-25 19:18:03.

Insertion speed (rows/second)
               Direct      Batch       Trans       Batch Trans
Ibx firebird   1549        12895       3170        13066
Zeos firebird  1925        15533       2119        16242

Read speed (rows/second)
               By one      All Virtual All Direct
Ibx firebird   1663        42024       71331
Zeos firebird  1954        72547       96491

Last edited by ttomas (2021-09-27 00:19:42)

ab · 2021-09-27 12:04:28

Code and numbers are nice.

But you have closed the pull request.
Is it on purpose?
I have just checked: Delphi 2010 supports nested type definitions in classes.

So I guess the whole unit could be written as FPC or Delphi 2010+ compatible only - as the IBX library.

ttomas · 2021-09-27 14:22:37

ab wrote:

But you have closed the pull request.
Is it on purpose?

Sorry not much experience with github. I add 1 more commit, move type out of class declaration and yes I close, can I reopen pull request or need to create new? Not on purpose :-)
I create new pull request https://github.com/synopse/mORMot2/pull/58
Please ignore #57

Last edited by ttomas (2021-09-27 14:46:53)

ttomas · 2022-02-21 23:59:11

I create new pull request https://github.com/synopse/mORMot2/pull/75
With Firebird v4 batch Insert and Update use new IBatch interface. Using IBatch interface double insert performance.

Insertion speed (rows/second)
Firebird 4 using execute block for batch Insert
              Direct      Batch       Trans       Batch Trans
Ibx firebird  1745        22317       2946        22017

Firebird 4 using IBatch interface
              Direct      Batch       Trans       Batch Trans
Ibx firebird  1726        46499       6309        46763

ab · 2022-02-22 07:24:35

Great job!

I have merged your pull request.
Nice seeing doubling numbers

So now the IBX driver insertion is ahead of Zeos, if I understand correctly.

ttomas · 2022-02-22 10:08:41

Yes for Fb4 Ibx is 20-30% faster, for Fb3 10-20% slower. Zeos have better optimized parameters setting. I try low level direct buffer setting for integer, currency and utf8 string and performance is same, but remove that code and keep fbintf param setting for safety and type conversion.

ttomas · 2022-02-22 11:23:52

@ab
In test.core.crypt.pas check line 2299 and 2301
Check(st2.Revoke(c3.GetSerial...
I change to
Check(st2.Revoke(c3, ...
to compile test

ab · 2022-02-22 11:38:56

Oups...

I forgot to commit the updated tests...
https://github.com/synopse/mORMot2/commit/9603635b

vs · 2024-01-18 00:22:27

Two small inaccuracies

commit 2.2.6749

[dcc32 Error] mormot.db.sql.ibx.pas(120): E2010 Incompatible types: 'UTF8String' and 'string'
[dcc32 Error] MVCServerFirebirdIbx.dpr(54): E2003 Undeclared identifier: 'CreateDescendingPK'

ttomas · 2024-01-18 10:27:37

I see dcc32 Error, you use Delphi!
mormot.db.sql.ibx.pas is Fpc/Lazarus only, based on IBX for Lazarus interface: https://www.mwasoftware.co.uk/fb-pascal-api
Never tested with Delphi. Try to install fbintf1.4-0 on Delphi. Any help is welcome.

Last edited by ttomas (2024-01-18 10:31:31)

vs · 2024-01-18 14:51:12

fbintf1.4-0 already installed on Delphi.

For Lazarus:
MVCServerFirebirdIbx.dpr(54,17) Error: identifier idents no member "CreateDescendingPK"
MVCServerFirebirdIbx.dpr(95,1) Error: Can't open resource file "D:\Projects\Delphi\Components\Synopse\mORMot2-2.0.stable\ex\mvc-blog\mormot.win.default.manifest.res"

mormot.db.sql.ibx.pas(119):
property FirebirdLibraryPathName: RawUtf8
but
mormot.db.sql.ibx.pas(87):
fFirebirdLibraryPathName: string;

ttomas · 2024-01-18 20:57:47

vs wrote:

MVCServerFirebirdIbx.dpr(54,17) Error: identifier idents no member "CreateDescendingPK"

property CreateDescendingPK was changed to CreateDescendingOnlyPK, ex\mvc-blog need to change

vs wrote:

MVCServerFirebirdIbx.dpr(95,1) Error: Can't open resource file "D:\Projects\Delphi\Components\Synopse\mORMot2-2.0.stable\ex\mvc-blog\mormot.win.default.manifest.res"

I usually copy this file to project folder from mormot2/src. Line 10 can be changed to:

  {$R ..\..\src\mormot.win.default.manifest.res}

mormot.db.sql.ibx.pas(119):
property FirebirdLibraryPathName: RawUtf8
but
mormot.db.sql.ibx.pas(87):
fFirebirdLibraryPathName: string;

Yes need to be changed to string. I will try to create push request.

vs · 2024-01-19 02:05:37

ttomas wrote:

Yes need to be changed to string. I will try to create push request.

Thank you so much.

Fixing these two issues
for Lazarus:

MVCServerFirebirdIbx.dpr(54,17) Error: identifier idents no member "CreateDescendingPK"
MVCServerFirebirdIbx.dpr(95,1) Error: Can't open resource file "D:\Projects\Delphi\Components\Synopse\mORMot2-2.0.stable\ex\mvc-blog\mormot.win.default.manifest.res"

and
for Delphi:

[dcc32 Error] mormot.db.sql.ibx.pas(120): E2010 Incompatible types: 'UTF8String' and 'string'
[dcc32 Error] MVCServerFirebirdIbx.dpr(54): E2003 Undeclared identifier: 'CreateDescendingPK'

MVCServerFirebirdIbx compiling successful for both and working as expected, except FTS

vs · 2024-01-19 12:27:54

Some test results

Running tests using Synopse mORMot framework 2.2.6749, compiled with Free Pascal 3.2.2 64 bit, against SQLite 3.44.2, on Windows 8 64bit (6.2.9200), at 2024-01-19 13:28:17.

Insertion speed								|	Read speed
 			Direct	Batch	Trans	Batch Trans		| 				By one	All Virtual	All Direct
Zeos firebird 25	2265	60781	16408	65852			|	Zeos firebird 25	10884	366770		399840
Zeos firebird 4		2187	55836	6949	63136			|	Zeos firebird 4		6944	373538		412864
Zeos firebird 5		2473	65590	8084	52379			|	Zeos firebird 5		8154	403177		490196
Ibx firebird 25		2366	43955	15503	40030			|	Ibx firebird 25		3850	164076		120290
Ibx firebird 4		1296	37603	3675	49165			|	Ibx firebird 4		1798	106503		99975
Ibx firebird 5		1275	30746	4452	40146			|	Ibx firebird 5		1967	221028		214850

For some reason log shows Windows 8 64bit, but in fact Windows 11 64bit.

mORMot Open Source

#51 2014-12-04 00:57:46

Re: Firebird transactions

#52 2014-12-04 09:37:01

Re: Firebird transactions

#53 2014-12-08 08:21:38

Re: Firebird transactions

#54 2014-12-11 21:29:47

Re: Firebird transactions

#55 2014-12-16 09:32:04

Re: Firebird transactions

#56 2014-12-16 17:41:47

Re: Firebird transactions

#57 2014-12-16 20:05:25

Re: Firebird transactions

#58 2014-12-17 08:33:34

Re: Firebird transactions

#59 2014-12-17 23:53:46

Re: Firebird transactions

#60 2014-12-18 06:56:14

Re: Firebird transactions

#61 2014-12-18 12:24:08

Re: Firebird transactions

#62 2014-12-18 19:40:34

Re: Firebird transactions

#63 2014-12-19 08:23:52

Re: Firebird transactions

#64 2021-04-18 17:50:50

Re: Firebird transactions

#65 2021-04-19 11:27:34

Re: Firebird transactions

#66 2021-04-19 12:09:24

Re: Firebird transactions

#67 2021-04-19 21:30:05

Re: Firebird transactions

#68 2021-04-20 03:51:25

Re: Firebird transactions

#69 2021-04-20 12:43:37

Re: Firebird transactions

#70 2021-04-26 16:32:08

Re: Firebird transactions

#71 2021-04-26 16:40:40

Re: Firebird transactions

#72 2021-05-06 05:11:52

Re: Firebird transactions

#73 2021-05-06 06:49:44

Re: Firebird transactions

#74 2021-05-10 22:06:49

Re: Firebird transactions

#75 2021-05-10 22:38:09

Re: Firebird transactions

#76 2021-05-11 06:46:25

Re: Firebird transactions

#77 2021-05-11 11:13:20

Re: Firebird transactions

#78 2021-05-11 15:21:38

Re: Firebird transactions

#79 2021-05-14 16:34:11

Re: Firebird transactions

#80 2021-05-31 17:21:40

Re: Firebird transactions

#81 2021-08-08 00:01:02

Re: Firebird transactions

#82 2021-09-12 17:50:32

Re: Firebird transactions

#83 2021-09-14 00:38:36

Re: Firebird transactions

#84 2021-09-14 09:50:14

Re: Firebird transactions

#85 2021-09-15 00:03:49

Re: Firebird transactions

#86 2021-09-15 06:43:13

Re: Firebird transactions

#87 2021-09-26 23:43:11

Re: Firebird transactions

#88 2021-09-27 12:04:28

Re: Firebird transactions

#89 2021-09-27 14:22:37

Re: Firebird transactions

#90 2022-02-21 23:59:11