You are not logged in.
Pages: 1
It looks like firebird 4 solves that issue of commit retaining transaction
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.”
@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!
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!
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.
@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!
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!
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
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'
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!
Unfortunately, both
FDBConnectionParams.ZeosURL.Properties.Add('hard_commit=true')
and
FDBConnectionParams.ZeosURL.Properties.Add('hard_commit=false')
same result
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!
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.
Pages: 1