Firebird transactions

alpinistbg · 2014-11-26 11:47:09

I've made a simple test with sample '01 - In Memory ORM'. Modified a little to use a Firebird DB and added a 3-rd button with:

procedure TForm1.UpdateButtonClick(Sender: TObject);
var Rec: TSQLSampleRecord;
begin
  Rec := TSQLSampleRecord.Create(Database, OldID);
  try
    Rec.Question := StringToUTF8(QuestionMemo.Text);
    if not Database.Update(Rec) then
      ShowMessage('Error updating the data');
  finally
    Rec.Free;
  end;
end;

Here, OldID is the ID remembered from the previous TForm1.FindButtonClick.

When just one instance of the Project01.exe started, everything is fine - the record is updated as expected. When second instance started and record modified, the first instance of the program can't update any more:

---------------------------
Debugger Exception Notification
---------------------------
Project Project01.exe raised exception class EZSQLException with message 'SQL Error: deadlock update conflicts with concurrent update concurrent transaction number is 46139. Error Code: -913. deadlock The SQL: update SampleRecord set TIME1=?,NAME=?,QUESTION=? where ID=?; '. Process stopped. Use Step or Run to continue.

Enclosing the statements with:

  Database.TransactionBegin(TSQLSampleRecord,1);
  ...
  DataBase.Commit(1);

doesn't help as I expected, see http://synopse.info/forum/viewtopic.php … 858#p13858. Any ideas?

Last edited by alpinistbg (2014-11-26 11:50:13)

ab · 2014-11-26 13:53:55

This is by design for insertion and due to some firebird restrictions for updates.

Only one single mORMot server should connect to a single db.

There is no benefit of sharing the same db with several mORMot rest servers at writing.

alpinistbg · 2014-11-26 14:28:53

What restrictions have Firebird on updates?

My point was to show the simplest way to confuse the mORMot/Firebird. That was the quickest way to make two connections to the same database. Probably I can write Client/Server example to do this with just one server (as recommended) and 2+ clients to show the same behavior, but is it needed?

excerpt from: http://synopse.info/forum/viewtopic.php … 221#p12221

ab wrote:

In short:
- mORMot.pas by default, lock all write access to the DB with a critical section;
- SynDBZeos.pas does create one connection to the DB per thread.
Perhaps the multi-thread abilities of Firebird client has some problems to scale at the mORMot pace.

miab3 · 2014-11-26 14:48:45

There is one important limitation.
If you use fbembed.dll (instead fbclient.dll) to localhost , the database is not a multi-user.
(The server is bypassed).
And it is not especially more performance, but easier to simple installations.

I think the same is with Embedded MySQL.

Michal

Last edited by miab3 (2014-11-26 15:02:12)

ab · 2014-11-26 14:49:34

Firebird should be used with a single background thread for all process.
This is the easiest safe setting.

danielkuettner · 2014-11-26 15:45:06

Before the ConnectionTimeout property coming up, I've testet with this option (amBackgroundThread) and this was the problem:
>>After implementing the two options, all updates are invisible! After restart the mORMot server I can the the changes to db.

Wouldn't it be better if every thread had its own Props (db-connection)? There could be a thread-pool of e.g. 10,50 or 100.

Last edited by danielkuettner (2014-11-26 15:45:35)

alpinistbg · 2014-11-26 16:11:13

@miab3,
Not sure what do you mean by 'multi-user', but the embedded engine is at least multi-thread-capable: http://www.firebirdsql.org/en/firebird- … scription/

@ab,
Perhaps here I should give up
IMHO multi-version concurrency of the Firebird has the true potential for a record-breaking performance levels. To confine it in just one thread is akin to removing the main features of Firebird.

miab3 · 2014-11-26 16:22:08

@alpinistbg

Firebird Embedded "server" supports only one localhost connection(to specified database).
http://www.firebirdsql.org/manual/fbmet … edded.html

Michal

Last edited by miab3 (2014-11-26 17:10:45)

alpinistbg · 2014-11-26 16:54:17

@miab3,
Perhaps you wanted to put this link: http://www.firebirdsql.org/manual/ufb-cs-embedded.html, instead of a link related to metadata security

And a litle excerpt from the page:

You can have multiple embedded servers running at the same time, and you can have multiple apps connecting to the same embedded server. Having a regular server already running isn't a problem either. However, an embedded server locks a database file for its own exclusive use after successful connection. This means that you cannot access the same database from multiple embedded server processes simultaneously (or from any other servers, once an embedded server has locked the file).

But I don't think it is worth to discuss the embedded case.

BTW, I absolutely agree with your post, http://synopse.info/forum/viewtopic.php … 878#p13878, i.e. the transaction isolation level used in mORMot is inappropriate.

P.S. Found that changing the tiNone to tiReadCommited in SynDBZEOS.pas resolves completely the problem described at the beginning of the thread:

constructor TSQLDBZEOSConnection.Create(aProperties: TSQLDBConnectionProperties);
begin
  inherited Create(aProperties);
  fDatabase := DriverManager.GetConnectionWithParams(
    (fProperties as TSQLDBZEOSConnectionProperties).fURL.URL,nil);
  fDatabase.SetAutoCommit(true);
  fDatabase.SetTransactionIsolation({tiNone}tiReadCommitted);
end;

Last edited by alpinistbg (2014-11-26 17:52:45)

ab · 2014-11-26 20:37:25

@miab3, yes, for Firebird embedded, you should set:

aExternalDB.ThreadingMode := tmMainConnection;

About expected isolation, sounds pretty fair to me.
I've changed the default transaction isolation status for SynDBZeos to tiReadCommitted as you propose.
See http://synopse.info/fossil/info/9c460b3f91

Any feedback from others?

alpinistbg · 2014-11-26 21:42:38

I can see the same tiNone constant in procedures TSQLDBZEOSConnection.Commit/Rollback; may be they should be changed too. But I am still not sure exactly how to benefit most of the Firebird features.

ab · 2014-11-26 22:25:51

Within a transaction I suspect we could leave the default tiNone value since the transaction is explicit....

EgonHugeist · 2014-11-26 23:09:22

Arnaud,

whoops! Yep the user is right: tiNone for FireBird allows to set a user choosen TIL by adding parameters of:

  TransactionParams: array [0..MAX_TPB_PARAMS]of TZIbParam = (
    (Name:'isc_tpb_version1';         Number: isc_tpb_version1),
    (Name:'isc_tpb_version3';         Number: isc_tpb_version3),
    (Name:'isc_tpb_consistency';      Number: isc_tpb_consistency),
    (Name:'isc_tpb_concurrency';      Number: isc_tpb_concurrency),
    (Name:'isc_tpb_exclusive';        Number: isc_tpb_exclusive),
    (Name:'isc_tpb_shared';           Number: isc_tpb_shared),
    (Name:'isc_tpb_protected';        Number: isc_tpb_protected),
    (Name:'isc_tpb_wait';             Number: isc_tpb_wait),
    (Name:'isc_tpb_nowait';           Number: isc_tpb_nowait),
    (Name:'isc_tpb_read';             Number: isc_tpb_read),
    (Name:'isc_tpb_write';            Number: isc_tpb_write),
    (Name:'isc_tpb_ignore_limbo';     Number: isc_tpb_ignore_limbo),
    (Name:'isc_tpb_read_committed';   Number: isc_tpb_read_committed),
    (Name:'isc_tpb_rec_version';      Number: isc_tpb_rec_version),
    (Name:'isc_tpb_no_rec_version';   Number: isc_tpb_no_rec_version),
    (Name:'isc_tpb_lock_read';        Number: isc_tpb_lock_read),
    (Name:'isc_tpb_lock_write';       Number: isc_tpb_lock_write)
    );

To the connection properties. This was made to be able to handle all possible ISC_TPB modes FB supports.

ab wrote:

Within a transaction I suspect we could leave the default tiNone value since the transaction is explicit. Note: This is a FB single case...

Yep i would confirm that too.

alpinistbg · 2014-11-27 08:21:19

Do we expect TSQLDBZEOSConnection.StartTransaction() to be invoked from TSQLRest.TransactionBegin()? Because it isn't.

  aProps := TSQLDBZEOSConnectionProperties.Create('zdbc:firebird-2.5://127.0.0.1:3050', 
                       'C:\AI\TMP\trymormot.fdb', 'SYSDBA', 'masterkey');
  VirtualTableExternalRegisterAll(aModel, aProps);
  Database := TSQLRestServerDB.Create(aModel, ':memory:', false);
  TSQLRestServerDB(Database).CreateMissingTables;
  ...
  Database.TransactionBegin(TSQLSampleRecord,1);
  ...

Or maybe I am missing something?

ab · 2014-11-27 08:30:21

1. You should better use Batch mode + automatic transaction instead of manual transaction.

2. StartTransaction is called from mORMotDB.pas AFAIR.
See function TSQLRestStorageExternal.TransactionBegin

3. You should ENSURE that the transaction is execute in a single thread from the server side, otherwise you may have conflicts if the write threading setup is not in a background thread.

alpinistbg · 2014-11-27 10:20:24

ab wrote:

1. You should better use Batch mode + automatic transaction instead of manual transaction.

Okay, perhaps I should use the Unit-Of-Work pattern (SAD 11.3.3) but I can't realize from the documentation/sources/examples how to make it work with heterogeneous updates, i.e. updates on different TSQLRecord descendants in one unit-of-work (batch). We don't expect to have just a single entity in our business model, right?

ab · 2014-11-27 11:29:42

No, it is clearly stated in the documentation.
Either you fix a single TSQLRecord class, or you just set nil to BatchStart() parameter, and then you can add any kind of TSQLRecord to the Batch*() commands.

alpinistbg · 2014-11-27 12:17:56

ab wrote:

No, it is clearly stated in the documentation.
Either you fix a single TSQLRecord class, or you just set nil to BatchStart() parameter, and then you can add any kind of TSQLRecord to the Batch*() commands.

I apologize for my oversight for the nil parameter value, I think it would be good to mention that in the SAD with a brief example, because it is somewhat obscured into the TSQLRestBatch.Create() description.

Using the TSQLRestBatch and TSQLRest.BatchSend() goes again to the TSQLRest.TransactionBegin(), which in turn doesn't call TSQLDBZEOSConnection.StartTransaction(). IMHO there is no transaction support on nothing but SQLite3.

ab · 2014-11-27 15:12:34

We have updated the documentation to make this even clearer.
See http://synopse.info/fossil/info/54c80cdf77
Thanks for the feedback.

Note that transactions were previoulys committed whenever the table changed in the Batch*() sequence.
So it is not so optimized: I've fixed that to maintain a list of transactions for the whole connection.
We use the new TSQLDBConnectionProperties.SharedTransaction() method to implement safely a cross-tables transactions, as soon as the same connection is re-used.

At batch level, there was indeed an implementation weakness when using automatic transactions.
If you specify a TSQLRecord class which is used on an external table, it will now call the SynDB transaction process as expected.
I have seen a noticeable performance increase with some backends.
See latest commits.

Feedback is welcome!

mpv · 2014-11-29 12:17:44

Ou! tiNone!!!! Thanks a god I'm currently not use Postgre on production - this is VERY serious bug (in case of direct connection usage. Not on mORMot ORM level).

From my POV transaction isolation level & transaction behavior in general must not be changed inside data access layer at all.
Almost all database set TI to read committed by default and this is developer expect.
In some case DBA can change this behavior (for example in MSSQL I turn on shapshot in some cases (ALTER DATABASE [myDB] SET READ_COMMITTED_SNAPSHOT ON).
Or developer can change it on transaction level by something like 'BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE' or on connection level properties.

My suggestion is to completely remove

fDatabase.SetAutoCommit
fDatabase.SetTransactionIsolation

calls from

TSQLDBZEOSConnection.Create
TSQLDBZEOSConnection.StartTransaction
TSQLDBZEOSConnection.Commit
TSQLDBZEOSConnection.Rollback

This is behavior I expect from database access layer.

Last commit ( fDatabase.SetTransactionIsolation(tiReadCommitted); ) is not a good solution - it broke my DDL statements execution (mORMot CreateMissingTable analog) because in Postgre DDL support transaction.
I create a suggestion ticket (thanks to good mORMot architecture I can override this methods in my descendants in case @AB decide to not apply this suggestion to main branch)

Last edited by mpv (2014-11-29 13:21:29)

ab · 2014-11-29 15:24:32

I need feedback here.
By default, I want the transactions to work in a consistent way, not following the default transaction scheme.
We want the transactions to follow the SQLite3 simple way: auto-commit the modifications, unless an explicit transaction scope (StartTransaction/Commit/Rollback) is performed.

What about if
- we leave fDatabase.SetTransactionIsolation(tiReadCommitted); in TSQLDBZEOSConnection.Create()
- and we remove the fDatabase.SetTransactionIsolation() lines in TSQLDBZEOSConnection.StartTransaction/Commit/Rollback
- and we only set fDatabase.SetAutoCommit(false) in TSQLDBZEOSConnection.StartTransaction and fDatabase.SetAutoCommit(true) in TSQLDBZEOSConnection.Commit/Rollback.

I mean:

constructor TSQLDBZEOSConnection.Create(aProperties: TSQLDBConnectionProperties);
begin
  inherited Create(aProperties);
  fDatabase := DriverManager.GetConnectionWithParams(
    (fProperties as TSQLDBZEOSConnectionProperties).fURL.URL,nil);
  fDatabase.SetAutoCommit(true);
  fDatabase.SetTransactionIsolation(tiReadCommitted);
end;

procedure TSQLDBZEOSConnection.StartTransaction;
begin
  inherited StartTransaction;
  fDatabase.SetAutoCommit(false);
end;

procedure TSQLDBZEOSConnection.Commit;
begin
  inherited Commit;
  fDatabase.Commit;
  fDatabase.SetAutoCommit(true);
end;

procedure TSQLDBZEOSConnection.Rollback;
begin
  inherited Rollback;
  fDatabase.Rollback;
  fDatabase.SetAutoCommit(true);
end;

What do you think?

alpinistbg · 2014-11-29 16:55:30

I can confirm in the ZEOS/Firebird case this is the exact behaviour when AutoCommit := true and TransactionIsolation := tiReadCommited (although I don't know the isolation of SQLite3 - ReadCommited or Serializable). There is also no need of resetting/setting AutoCommit between StartTransaction/Commit - it is not in effect during the explicit transaction.

mpv · 2014-11-29 20:41:19

alpinistbg wrote:

There is also no need of resetting/setting AutoCommit between StartTransaction/Commit - it is not in effect during the explicit transaction.

I have no access to sources till monday, but as far as I remember this is true. May be Egon give some comment?
And from my POV we don't need to set

fDatabase.SetTransactionIsolation(tiReadCommitted);

in constructor. I need to perform investigation..

EgonHugeist · 2014-11-29 23:20:46

@mpv

which info's do you need?

alpinistbg wrote:

no need of resetting/setting AutoCommit between StartTransaction/Commit

Yes this is right for Commit and Rollback. NOTHING would happen. So it's not perfekt but ok.

procedure TSQLDBZEOSConnection.StartTransaction;
begin
  inherited StartTransaction;
  fDatabase.SetAutoCommit(false);
end;

is correct to me. You're expecting a Transaction which YOU can commit or Rollback.

If i would know how everything works in AB's ORM (: In the past he asked me if this code is correct.. If it's a pure retaining model than of course it is correct.

Aranud it might be a good idea to add a property

TSQLDBZEOSConnection = class(TSQLDBConnectionThreadSafe)
...
public
  ....
  TransactIsolationLevel: TZTransactIsolationLevel read f.... write SetTransactionIsolation;
 ....
end;
procdedure SetTransactionIsolation(Level: TZTransactIsolationLevel);
begin
  fDatabase.SetTransactionIsolation(tiReadCommitted);
 
end;

tiNone by default is still OK for me.

Agree the users should have free hand to choose the TIL.

Just study SetTransactionIsolation() in ech ZDbcXXXConnection.pas what the enums do represent.
For PostgreSQL f.e. tiNone is the simple 'BEGIN' call.

But ... I must admit i have to check how you can manipulate in FB?!

Edit: Now you can also customize your TIL's for FB. Patch done R3526 \testing-7.2 will be merged to trunk and \testing-7.3 in a periode of a week.

Last edited by EgonHugeist (2014-11-30 01:13:32)

ab · 2014-11-30 08:37:10

AFAIR you already have the TSQLDBZEOSConnection.Database property which allows to call SetTransactionIsolation() on the ZDBC IZConnection object.
So I guess this is not mandatory to add such a property.

As far as I understood the ZDBC code, you need TransactIsolationLevel <> tiNone to have the transaction to be supported in the units.
Previous SetTransactionIsolation(tiNone) would in fact disable any transaction process.
So I guess the code I wrote above in http://synopse.info/forum/viewtopic.php … 935#p13935 would work as such for all providers?

danielkuettner · 2014-11-30 09:50:04

Hi AB,

I think your code should be changed to:

procedure TSQLDBZEOSConnection.StartTransaction;
begin
  inherited StartTransaction;
  //fDatabase.SetAutoCommit(false); <-- this line is not necessary, because AutoCommit has no relevance when explicit transaction was starting
end;

procedure TSQLDBZEOSConnection.Commit;
begin
  inherited Commit;
  fDatabase.Commit;
  //fDatabase.SetAutoCommit(true); <-- not necessary, because Autocommit wasn't changed
end;

procedure TSQLDBZEOSConnection.Rollback;
begin
  inherited Rollback;
  fDatabase.Rollback;
  //fDatabase.SetAutoCommit(true); <-- not necessary, because Autocommit wasn't changed
end;

If Autocommit will be changed in StartTransaction/Commit/Rollback, normal transactions in other Threads won't be committed until explicit transaction is finished, but other Threads wouldn't know that.

EgonHugeist · 2014-11-30 10:10:10

AB wrote:

As far as I understood the ZDBC code, you need TransactIsolationLevel <> tiNone to have the transaction to be supported in the units.
Previous SetTransactionIsolation(tiNone) would in fact disable any transaction process.
So I guess the code I wrote above in http://synopse.info/forum/viewtopic.php … 935#p13935 would work as such for all providers?

FYI:
After reading all this here i did study the whole Connection interfaces about the Transactions..
NO you are allways in a transaction for ALL providers.
The transaction will be started right after open. For FB as well. But for FB + tiNone the Server starts the defaults. Also are you able to add Connection properties to start your own transaction kinds of FireBird.

May i ask how you handle the AutoCommit mode?
If you do it by your selves by slt:

  YourClass.Commit;

than the Autocommit is not required. And this may cause the DeadLock because if Zeos executes the Stmt a commit will be done if succeed (except for ExecuteQueryPrepared/ExecuteQuery of course). Don't know if FB has problems on calling a second commit?!

So i propose you follow Daniels suggestion.

Last edited by EgonHugeist (2014-11-30 10:11:39)

ab · 2014-11-30 12:23:39

@daniel
1. AFAIK TSQLDBZEOSConnection.StartTransaction does not start any transaction.
We need a BEGIN to be emitted. If I make your modification, no new transaction would take place in ZDBC code, I suspect.
2. There is one connection per thread, so I do not understand your POV about threading issues.

@Egon
We need the auto commit to be available.
I mean, if i execute INSERT without any previous StartTransaction, this INSERT should be committed immediately.
Isn't it what "autocommit" mean?
Then, if I call StartTransaction, then no transaction would be committed until explicitly Commit or Rollback is executed.
This is why

EgonHugeist · 2014-11-30 12:52:48

@AB

ab wrote:

1. AFAIK TSQLDBZEOSConnection.StartTransaction does not start any transaction.

Yes this is right! You are in a single transaction since Connect.
You anly can Commit/Rolback.

ab wrote:

We need the auto commit to be available.
I mean, if i execute INSERT without any previous StartTransaction, this INSERT should be committed immediately.
Isn't it what "autocommit" mean?
Then, if I call StartTransaction, then no transaction would be committed until explicitly Commit or Rollback is executed.
This is why

Yes i agree with the AutoCommit idea. BUT who controls this behavior?
If YOU set Zeos in AutoCommit mode -> Zeos does it by it's selves! Each update will be commited after execution.

What i'm trying to say: If AutoCommit is set for Zeos you don't need a explicit Commit call on your side.
In addition if you are controler about the commits also in AutoCommit mode -> Than use the suggestion of Daniel. There is no need Zeos and Your are doing the commit call, understand me?

Michael

danielkuettner · 2014-11-30 13:16:30

AB wrote:

1. AFAIK TSQLDBZEOSConnection.StartTransaction does not start any transaction.
We need a BEGIN to be emitted. If I make your modification, no new transaction would take place in ZDBC code, I suspect.

That do I not understand. The Transaction counter will increased, Zeos will start the transaction as soon as needed.

AB wrote:

2. There is one connection per thread, so I do not understand your POV about threading issues.

I thought we have amMainThread (e.g. for Fb). Wasn't this your suggestion for Fb? Then only MainConnection will be used by the threads.
That was my POV.

alpinistbg · 2014-11-30 14:53:48

As far as I understand, Arnaud wants the following behaviour:

Group of SQL statements can be enclosed into explicit transaction, i.e. Begin, Stmt1,...,StmtN, Commit/Rollback;
Outside such explicit transactions, each SQL statement must be enclosed in its own implicit transaction, i.e. to be committed immediately;

Although the SQLite3, MSSQL and PostgreSQL works exactly that way, the Firebird works differently. It does not insert implicit Commits until somebody tell him to do so (isn't it for that AutoCommit?).

danielkuettner wrote:

I thought we have amMainThread (e.g. for Fb). Wasn't this your suggestion for Fb? Then only MainConnection will be used by the threads.

IMHO, the amMainThread is to be used for the embedded FB. No reason to use the full FB server in such a crippled way. Furthermore, I don't think thats even necessary, because the embedded FB is claimed to be thread-safe.

Last edited by alpinistbg (2014-11-30 15:02:58)

danielkuettner · 2014-11-30 15:15:19

@alpinistbg
You are right (btw I've meant tmMainConnection but wrote amMainThread):

dFirebird: begin
    if (fURL.HostName='') and // Firebird embedded
       (fURL.Database<>'') then begin
      ThreadingMode := tmMainConnection; // force SINGLE connection
      if not FileExists(fURL.Database) then // create local DB file if needed
        fURL.Properties.Add('createNewDatabase='+UTF8ToString(
          SQLCreateDatabase(StringToUTF8(fURL.Database))));
    end;

But Michael suggest such a single connection for Fb because it has no performance loss and it's better for our deadlock problem.

And in SynDBZeos is written:

    // - note that when run from mORMot's ORM, this class will by default
    // create one connection per thread, which makes some clients (e.g.
    // PostgreSQL) unstable and consuming a lot of resources - you should better
    // maintain one single connection, by setting after Create:
    // ! aExternalDBProperties.ThreadingMode := tmMainConnection;
    // or by adding 'syndb_singleconnection=true' as URI property

However, if I use tmMainConnection and set Autocommit =true then I guess setting Autocommit to false in StartTransaction would give uncommitted implicit transactions.

Last edited by danielkuettner (2014-11-30 15:15:41)

EgonHugeist · 2014-11-30 15:55:48

danielkuettner wrote:

But Michael suggest such a single connection for Fb because it has no performance loss and it's better for our deadlock problem.

Right, i've been trying to say that.

But don't forgett my previous post for all other Drivers.
AutoCommit for Zeos is connection bound. All subsequential bound update statments automatically commit on succcess if Conenction is in AutoCommit mode. So i'm starting from the premisse i'm thinking to much in Zeos-Code-Lines..

alpinistbg wrote:

As far as I understand, Arnaud wants the following behaviour:
Group of SQL statements can be enclosed into explicit transaction, i.e. Begin, Stmt1,...,StmtN, Commit/Rollback;
Outside such explicit transactions, each SQL statement must be enclosed in its own implicit transaction, i.e. to be committed immediately;
Although the SQLite3, MSSQL and PostgreSQL works exactly that way, the Firebird works differently. It does not insert implicit Commits until somebody tell him to do so (isn't it for that AutoCommit?).

Right. But this you can do by hand and the server does encapsulates subsequentual stmt's to it's transaction.

Actually i've no idea how to do that with FB quickly and without breaking current beahior.
It definitally is possible do do that! You can create X trans_handle's (FireBird/Interbase API) but which stmt is owned by which handle needs to be controlled by Zeos/mORMot.
A problem we can't simply resolve with current generic implementations.

In addition you should know:

Zeos documention wrote:

After confirming the chanes made in a transaction by COMMIT or resetting them by ROLLLBACK the
transaction normally is going to be ended and an existing resultset of a query or stored procedure will be
discarded. These COMMITs and ROLLBACKs are called "hard" commit or "hard" rollback. By using the
ZEOS library this will become a little bit different. ZEOS keeps the resultset alive. This is achieved by closing
transaction with "soft" commits or "soft" rollbacks. All this is done by the TZConnection object. This method is
called retaining. The COMMIT and ROLLBACK commands are executed with the addition RETAINING.
Retaining causes the closing of the current transaction and immediately opening a new transaction with all
the data and resources (especially the resultset) of the "old" transaction.
Retaining becomes a problem if it is uses for huge tables. It constrains the internal cleanup mechanism of
firebird (garbage collection). This leads (because of the versioning and the multigenerational architecture of
Firebird) to a lot of old records that have to be kept but will not be needed anymore. This influences the
server's performanced in a negative way. A so called sweep would discard these old versions and improve
the performance. This sweep will only be executed by sending a "hard" COMMIT or ROLLBACK. The ZEOS
Library only executes these "hard" commands when ending the database connection (closing connection). It
is not possible to send them while a database connection is active. So the database connection should be
deactivated and immediately activated occasionally to achieve this performance improvement.

My last patch of yesterday improves this a bit: Change TIL from Current to a different and a "hard_commit" follows and FB cleans up data. But you should know cached-stmts are lost than or trying to do that may raise an Exception!

Michael

ab · 2014-11-30 18:15:50

So... what you should we do?

danielkuettner · 2014-11-30 19:18:43

I try a suggestion:

For sqlite I've found this:

https://www.sqlite.org/c3ref/get_autocommit.html

That sounds for me like auto commit should not set explicit in StartTransaction/Commit/Rollback. But it could depend on Isolation level.

For normal statements (without explicit transaction starting) a

if auto_commit is undefined then
commit

after the post could be a solution.

The property "auto_commit" should be set by Zeos depending on Database. In case of sqlite there will call a function sqlite3_get_autocommit(sqlite3*), in other cases no such function call is necessary.

martin.suer · 2014-11-30 22:31:07

the solution from the firebird point of view (how transactions work in firebird) would be to use different transactions for reading and writing.

for reading: transactions with til = read-read-commited for reading operations, those don't cause harm if retaining is used
for writing: transactions with til = snapshot, those mustn't be retained

I don't know if and how that can be achieved with zeos

alpinistbg · 2014-11-30 23:15:11

I'm aware of soft and hard commits in FB, I wasn't in fact aware how Zeos work with the different back-ends. Using soft commits is a drawback. But we know that in general case the developer will allocate several transaction objects and will decide (wisely perhaps ) which one to use and for what.
We should consider the fact that mORMot wants to use the RDBMS back-end only for a simple table storage ... but, at the other hand - it wants to stay highly multi-concurrent ... I agree, it won't be a trivial task to map the FB transaction model to the SQLite3 one. I guess only Arnaud has the insight where to sprinkle explicit transactions at the ORM level.

martin.suer wrote:

for reading: transactions with til = read-read-commited for reading operations, those don't cause harm if retaining is used

It will hold the lowest interesting transaction number and will stop the sweeper. It may be a problem in update-intensive applications.

danielkuettner · 2014-12-01 06:45:17

Good morning AB,

as you know, I don't know so much about mORMot in deep and therefore some ideas from me are not the best. Up to now I thought, you use these transactions also for your batches.

But in my tests at morning I see, that you doesn't.

Therefore the solution is very easy and clear for me:

procedure TSQLDBZEOSConnection.StartTransaction;
begin
  inherited StartTransaction;
end;

procedure TSQLDBZEOSConnection.Commit;
begin
  inherited Commit;
  fDatabase.Commit;
end;

procedure TSQLDBZEOSConnection.Rollback;
begin
  inherited Rollback;
  fDatabase.Rollback;
end;

All the rest of problems are not problems of the framework. If developers starting transactions, they have to know what happens under their (external) DB with initial settings (TIL an so on).

alpinistbg · 2014-12-01 09:27:31

IMHO, the danielkuettner proposal is workable one. The developer can just briefly disconnect from time to time in order to achieve a hard commit.

Or may be it can be improved a little bit by implementing a simple tracking of TSQLDBZEOSStatement creation/destruction and issuing a hard commit (if Zeos permits that) when no active back-end datasets exists. Those commits are with minimal penalty in FB. After all, the mORMot policy is for client to consume the back-end datasets ASAP, so it might be a quick workaround.

EgonHugeist · 2014-12-01 10:42:07

Agree.

Note you kan kill the sweeper_cache issue by having a second "UPDATE"-connection.

Adding 'hard_commit=True' like

Your_SQLDBZEOSConnectionProperties.ZeosURL.Properties.Add('hard_commit=true');

The TIL of FB is you choise too:

(YourSQLDBConnection as TSQLDBZEOSConnection).Database.SetTransactionIsolation(YourTIL{Level: TZTransactIsolationLevel});

Zeos ZDbcIntfs.pas wrote wrote:

{** Defines a transaction isolation level. }
TZTransactIsolationLevel = (tiNone, tiReadUncommitted, tiReadCommitted,
tiRepeatableRead, tiSerializable);

As i wrote in \testing-7.2 SVN the patch is available to do this.
tiNone allows you to define you custom tdb's by adding them to the URL.Properties too. See FireBird/Interbase doc's how to do that.

Michael

Last edited by EgonHugeist (2014-12-01 10:42:59)

mpv · 2014-12-01 14:27:16

In my branch I change implementation in the same way Daniel propose. Everything if work as I expect now.

The core of problem in this discussion is auto-commit. In my projects I avoid this feature last 10 year, because different DBMS have different behaviors. From my POV this is the same evil as select * ..., natural primary keys e.t.c. But this is only my POV. So I always manually start transaction/Commit/Rollback.

danielkuettner wrote:

Hi AB,

I think your code should be changed to:

procedure TSQLDBZEOSConnection.StartTransaction;
begin
  inherited StartTransaction;
  //fDatabase.SetAutoCommit(false); <-- this line is not necessary, because AutoCommit has no relevance when explicit transaction was starting
end;

procedure TSQLDBZEOSConnection.Commit;
begin
  inherited Commit;
  fDatabase.Commit;
  //fDatabase.SetAutoCommit(true); <-- not necessary, because Autocommit wasn't changed
end;

procedure TSQLDBZEOSConnection.Rollback;
begin
  inherited Rollback;
  fDatabase.Rollback;
  //fDatabase.SetAutoCommit(true); <-- not necessary, because Autocommit wasn't changed
end;

If Autocommit will be changed in StartTransaction/Commit/Rollback, normal transactions in other Threads won't be committed until explicit transaction is finished, but other Threads wouldn't know that.

Last edited by mpv (2014-12-01 14:28:56)

ab · 2014-12-03 07:03:11

mpv wrote:

IThe core of problem in this discussion is auto-commit. In my projects I avoid this feature last 10 year, because different DBMS have different behaviors. From my POV this is the same evil as select * ..., natural primary keys e.t.c. But this is only my POV. So I always manually start transaction/Commit/Rollback.

Does makes sense at SQL level.
But for a REST application, which is stateless, such a behavior is IMHO a no-go.
For mORMot, transactions are better handled as part of the "unit of work" pattern, implemented via BATCH sequences and automatic transaction creation.
But for a REST API, we expect individual Create/Update/Delete operations to be performed without any transaction.

This is what "auto-commit" is about, in what SQLite3 was implementing.
I thought that the ZDBC "auto-commit" property did follow this somewhat standard behavior.
I thought that ZDBC was able to enable the "auto-commit" mode of the RDBMS client, e.g. what we use in SynDBOracle by setting OCI_COMMIT_ON_SUCCESS outside of a transaction.
For instance, ZDBC does the same with Oracle.

To sum up, sounds like if it works in all DB but FireBird, right?
With FB, our SynDBZeos unit should create its own transactions around every single REST individual Create/Update/Delete operations.

So two questions:
1. Is 'hard_commit=true' needed in this case?
2. Is there any other DB supported by ZDBC which wouldn't implement the expected "auto-commit" feature?

alpinistbg · 2014-12-03 09:42:31

@ab
First, allow me to disagree a bit - the atomicity is a logical-level property, not at SQL or UOW level

ab wrote:

1. Is 'hard_commit=true' needed in this case?

The problem is twofold. Since the FB exploits COW semantics, and the transactions are nothing more than ever-increasing version numbers, using soft commits will have the following implications:

A hanging connection will stop the garbage collector and the file storage will grow quickly; That is because the 'interesting' version number stays the same on soft commit;
Recent connection will always tend to win update conflicts in regard to the oldest one;

At the other hand, using hard commits will:

Invalidate all active datasets, since the 'interesting' version/transaction number will change on each hard commit;
Resolve update conflicts in their natural timely order as they should be;

But using that COW semantics, the FB entirely separates readers from writers and does not use any locks. It makes demanding operations as backups (and perhaps replication) to execute entirely in background.

Sorry for that I'm adding a little bit of frustration on this, but IMHO the issue will not pass until the handling of FB transactions in SynDBZeos got somewhat aligned with the mORMot model.

ab wrote:

2. Is there any other DB supported by ZDBC which wouldn't implement the expected "auto-commit" feature?

Somebody with experience with PostgreSQL must give opinion on that, but AFAIK it uses similar MVCC model.

Regards,
Yuliyan

ab · 2014-12-03 10:05:23

Atomicity is a REST property, since it is stateless.

About FB, perhaps we may better let ZDBC handle this at DB provider level, so that its AutoCommit option would mean the same on all supported providers.
AFAIK in the current state, the ZDBC documentation about transaction is consistent.
I mean "AutoCommit" is implemented the same e.g. for PostgreSQL and FB.

About PostgreSQL, MVVC is used on implementation level, but not at API level.
PostgreSQL API does not rely on implementation details: you can customize every PG object, even change the whole storage engine, add new types, and so on, if needed.
PostgreSQL is IMHO the most standard DB (it passes all the SQL standard tests much better than MS SQL or Oracle, BTW).

PostgreSQL documentation wrote:

In the default mode, statements are committed only when EXEC SQL COMMIT is issued. The embedded SQL interface also supports autocommit of transactions (similar to libpq behavior) via the -t command-line option to ecpg (see ecpg) or via the EXEC SQL SET AUTOCOMMIT TO ON statement. In autocommit mode, each command is automatically committed unless it is inside an explicit transaction block. This mode can be explicitly turned off using EXEC SQL SET AUTOCOMMIT TO OFF.

See http://www.postgresql.org/docs/9.3/stat … mands.html
In fact, TZPostgreSQLPreparedStatement.ExecutePrepared will call COMMIT as expected, when AutoCommit is ON.

AFAIK TZInterbase6PreparedStatement.ExecutePrepared does also call TZInterbase6Connection.Commit.
So it should be not mandatory to add any "manual" transactions at SynDBZeos level for FB, when the ORM set AutoCommit to ON, right?

alpinistbg · 2014-12-03 11:06:59

Yes, as I wrote in http://synopse.info/forum/viewtopic.php … 959#p13959, Daniel's proposal is a workable one. At least when used with short-lived connections.
My humble proposal was to augment a little the connection introducing a TSQLDBZEOSStatement Create/Destroy counter and to issue a hard commit when the counter decrements from one to zero. Unfortunately, there is no way to do this in the current Zeos implementation (all commits are either soft ones or hard ones). But may be it won't be better than just simply reconnecting.

ab wrote:

About PostgreSQL, MVVC is used on implementation level, but not at API level.

I didn't got that!

ab wrote:

PostgreSQL is IMHO the most standard DB (it passes all the SQL standard tests much better than MS SQL or Oracle, BTW).

Not surprised at all.

ab · 2014-12-03 11:11:20

Problem with our current implementation is that the SynDB connection could be pretty long-living...
Connections are re-created only if some kind of huge timeout did achieve between two requests.
If you make a request every 10 seconds, for instance, the connection will still be healthy, and re-used.
I would rather not change this behavior, just due to FB limitations/expectations.

I can not understand very well your proposal about reference counted hard-commit.
I guess that in most cases, the hard commit could most always take place.

alpinistbg · 2014-12-03 11:22:47

ab wrote:

I guess that in most cases, the hard commit could most always take place.

I'm a mORMot newbie! The hard commits invalidates the active cursors, if you're sure that it won't invalidate anything useful, then go for 'hard_commit=true' - it is even better!

The ref-counting was proposed as a stupid way to find when such a hard commit won't affect other statements (with possibly active cursors).

ttomas · 2014-12-03 14:03:34

Firebird transaction management is a future not limitation.
Every execute statement is executed in transaction.
Transaction<>DB Connection
Firebird can use nested transactions, PostgrSQL, MSSql, Oracle can not, to simulate you must to use another connection.
Example (pseudo code):

Db1 fb1.fdb
Trans1 Start Transaction
Exec1(Db1, Trans1, 'some statement');
Trans2 Start Transaction
Exec2(Db1, Trans2, 'some other');
Trans2 Commit
Exec3(Db1, Trans1, 'some other statement');
Trans1 Commit

Trans1 and Trans2 can use different isolation level

Transactions can be multi database (Double Commit)
Example:

Db1 fb1.fdb
Db2 fb2.fdb
TransMulti.Add(Db1)
TransMulti.Add(Db2)
TransMulti start transaction
Exec1(Db1, TransMulti, 'some statement in db1')
Exec2(Db2, TransMulti, 'some statement in db2')
TransMulti Commit  // Double Commit in both db1 and db2

Very useful future for replications, datapump, etc
I don't know if this future can be used in mORMot, maybe in user made services.
For RESTful services you can use (resuse) same Fb connection (connect/disconnect can cost time), but every Rest action must start transaction and commit or rollback in the end.

Last edited by ttomas (2014-12-03 14:09:04)

ab · 2014-12-03 14:41:14

Thanks for the input.
This is a limitation, in respect to how most other DBs are working, and when you want to have a consistent operation among all RDBMS - which is what an ORM expects.
So a transactional ORM over FireBird is more difficult to implement...
But you are right, this is indeed a feature, from the FB point of view - with the problem of inconsistent work at API level in respect to other RDBMS.

AFAIR you can have named transactions in PG,MSSQL or Oracle, to have several transactions within a single connection.
This is just how it is implemented at API/client level which is not the same with FB.

AFAIK multiple DB transactions are also available in PG, MSSQL and Oracle, but with a more bigger overhead at least with MSSQL + Oracle (you should run a transaction service to synchronize the process).
Light "double-commit" is indeed a nice feature of FB.
The problem is when one commit fail on one of the DB, whereas it has already been committed on the others: the rollback needs some kind of meta-service to perform this "dual phase" commit.

In SynDB units, we use one transaction per connection, which is very basic, but enough for REST operations.
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.
(and will work with all SQL databases, and also with NoSQL databases)

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.

alpinistbg · 2014-12-03 15:01:01

@ab,
Just FYI, If you find couple of free hours, I recommend you to read about the MVCC implementation in FB. You'll be surprised how simple and elegant is. Of course, I'm biased, because I have more than a decade experience with the enormous beast known as MSSQL .

mORMot Open Source

#1 2014-11-26 11:47:09

Firebird transactions

#2 2014-11-26 13:53:55

Re: Firebird transactions

#3 2014-11-26 14:28:53

Re: Firebird transactions

#4 2014-11-26 14:48:45

Re: Firebird transactions

#5 2014-11-26 14:49:34

Re: Firebird transactions

#6 2014-11-26 15:45:06

Re: Firebird transactions

#7 2014-11-26 16:11:13

Re: Firebird transactions

#8 2014-11-26 16:22:08

Re: Firebird transactions

#9 2014-11-26 16:54:17

Re: Firebird transactions

#10 2014-11-26 20:37:25

Re: Firebird transactions

#11 2014-11-26 21:42:38

Re: Firebird transactions

#12 2014-11-26 22:25:51

Re: Firebird transactions

#13 2014-11-26 23:09:22

Re: Firebird transactions

#14 2014-11-27 08:21:19

Re: Firebird transactions

#15 2014-11-27 08:30:21

Re: Firebird transactions

#16 2014-11-27 10:20:24

Re: Firebird transactions

#17 2014-11-27 11:29:42

Re: Firebird transactions

#18 2014-11-27 12:17:56

Re: Firebird transactions

#19 2014-11-27 15:12:34

Re: Firebird transactions

#20 2014-11-29 12:17:44

Re: Firebird transactions

#21 2014-11-29 15:24:32

Re: Firebird transactions

#22 2014-11-29 16:55:30

Re: Firebird transactions

#23 2014-11-29 20:41:19

Re: Firebird transactions

#24 2014-11-29 23:20:46

Re: Firebird transactions

#25 2014-11-30 08:37:10

Re: Firebird transactions

#26 2014-11-30 09:50:04

Re: Firebird transactions

#27 2014-11-30 10:10:10

Re: Firebird transactions

#28 2014-11-30 12:23:39

Re: Firebird transactions

#29 2014-11-30 12:52:48

Re: Firebird transactions

#30 2014-11-30 13:16:30

Re: Firebird transactions

#31 2014-11-30 14:53:48

Re: Firebird transactions

#32 2014-11-30 15:15:19

Re: Firebird transactions

#33 2014-11-30 15:55:48

Re: Firebird transactions

#34 2014-11-30 18:15:50

Re: Firebird transactions

#35 2014-11-30 19:18:43

Re: Firebird transactions

#36 2014-11-30 22:31:07

Re: Firebird transactions

#37 2014-11-30 23:15:11

Re: Firebird transactions

#38 2014-12-01 06:45:17

Re: Firebird transactions

#39 2014-12-01 09:27:31

Re: Firebird transactions

#40 2014-12-01 10:42:07