Recent

Author Topic: Question for IBX developers  (Read 1310 times)

ttomas

  • Full Member
  • ***
  • Posts: 224
Question for IBX developers
« on: September 13, 2021, 07:42:57 pm »
In FB Pascal API, in file FB30Statement.pas (also in FB25Statement) in function TFB30Statement.InternalExecute, line 1393, also in InternalOpenCursor line 1439, there is a code
Code: Pascal  [Select][+][-]
  1.   if (FSQLParams.FTransactionSeqNo < (FTransactionIntf as TFB30transaction).TransactionSeqNo) then
  2.     IBError(ibxeInterfaceOutofDate,[nil]);
  3.  
Why is this necessary? Any problem without this code?
You can prepare statement in one transaction and execute same statement with another transactions.
For example for bulk inserts, prepare statement and loop with commit/starttransaction every X rows.
I'm working on mormot2 Firebird driver using your FB Pascal API to have fine control of transactions. In mORMot I implement Prepare and Execute interface of mormot db layer, but mormot cache statements and call only Execute for cached statements. This call can arrive with different transaction (I hard commit every statement execution if explicit transaction is not started).
I comment this lines in my code and no problems found with testing.
Can this line be removed or some how configure to use/not use in FB Pascal API?

ttomas

  • Full Member
  • ***
  • Posts: 224
Re: Question for IBX developers
« Reply #1 on: September 14, 2021, 03:30:14 am »
More details and source of mormot ibx interface at
https://synopse.info/forum/viewtopic.php?pid=35743#p35743

tonyw

  • Full Member
  • ***
  • Posts: 225
    • MWA Software
Re: Question for IBX developers
« Reply #2 on: September 14, 2021, 10:58:04 am »
Will IBX work without this check - the answer is yes it will.

This is really an internal consistency check to avoid stale references. When an SQL Statement is prepared, the data areas created to hold the statement parameters and results are tagged with the internal sequence number of the transaction used to prepare the query.

Later on, if you try and execute the query/open the cursor and there is a mis-match between the current transaction sequence number and the sql parameters transaction sequence number then an exception is raised. The same check is applied when closing a cursor.

The check was put there out of concerns that a COM interface to an SQL Param or result may get out of step with the underlying query. Has this ever happened in practice? I don't recall seeing the error since early testing. Perhaps it could be removed - but then without it there may be, at some time in the future, a hard to diagnose error when setting a param or reading a result - so there seems to be no good reason to remove this check.

ttomas

  • Full Member
  • ***
  • Posts: 224
Re: Question for IBX developers
« Reply #3 on: September 14, 2021, 11:41:12 pm »
Thanks tonyw for your response.

I understand your concern to remove this code.
Please, can you consider to add some property or method in IAttachment for ex.
Code: Pascal  [Select][+][-]
  1. att.DisableInterfaceOutOfDateCheck := True;
Default will be False and no problem for existing and future code.
And change if statement to:

Code: Pascal  [Select][+][-]
  1.   if (not FAttachmentIntf.DisableInterfaceOutOfDateCheck) and
  2.      (FSQLParams.FTransactionSeqNo < (FTransactionIntf as TFB30transaction).TransactionSeqNo) then
  3.     IBError(ibxeInterfaceOutofDate,[nil]);
  4.  
mORMot2 IBX driver is already in trunk as beta
https://github.com/synopse/mORMot2/blob/master/src/db/mormot.db.sql.ibx.pas
and this changes is very important for future users to not have to patch next versions of fbintf.
It is not possible to workaround about this problem in mormot code.

I'm very happy with quality and performance of your code. Now I work to implement IBatch interface, current solution use execute block for Batch operations.

Thanks in advance


tonyw

  • Full Member
  • ***
  • Posts: 225
    • MWA Software
Re: Question for IBX developers
« Reply #4 on: September 16, 2021, 08:56:42 pm »
Perhaps you could explain why this check is a problem for you.

ttomas

  • Full Member
  • ***
  • Posts: 224
Re: Question for IBX developers
« Reply #5 on: September 17, 2021, 04:06:38 pm »
tonyw,
I need to run the code like this
Code: Pascal  [Select][+][-]
  1.   att: IAttachment;
  2.   trans, trans2: ITransaction;
  3.   stmt: IStatement;
  4.   ...
  5.   trans := att.StartTransaction(fInternalTPB, TACommit);
  6.   stmt := att.Prepare(trans, 'insert into ...' );
  7.   MyFillAllParams1;
  8.   stmt.Execute(trans);  //OK
  9.   trans.Commit;
  10.   trans.Start(TACommit);
  11.   MyFillAllParams2;
  12.   stmt.Execute(trans);  // raise Exception
  13.   trans.Commit;
  14.  
  15.   // also some times I need
  16.   trans2 := att.StartTransaction(fInternalTPB, TACommit);
  17.   stmt.Execute(trans2);  // raise Exception
  18.   trans2.Commit;  
  19.  
In Firebird Statement can be executed with different transaction then prepare transaction. This protection in IBX is unique compared with other Firebird libraries (ZeosLib, IBObjects, UIB, FireDAC, UniDac, IBPP (C), etc).
Just checked some old code running for years with UIB
Code: Pascal  [Select][+][-]
  1.   // pseudo code for big import
  2.   StartTransaction;
  3.   Prepare;
  4.   for i:=1 to largeN do
  5.   begin
  6.      FillParams;
  7.      Execute;
  8.      if (i mod 100)=99 then
  9.      begin
  10.         Commit;
  11.         sleep(1000);  // give some time to other users
  12.         StartTransaction;
  13.      end;
  14.   end;
  15.  
For mORMot2 integration, client code like this:
Code: Pascal  [Select][+][-]
  1.   for i:=1 to 1000 do
  2.   begin
  3.      R.YearOfBirth := n;
  4.      Client.Add(R, true);
  5.   end;
This code send 1000 http request to mormot server to insert R (TOrm Object). Only first call on server side call Prepare then Execute. After first call statement is cached and next calls (999) calls only Execute method of DB driver. There is no problem with logic with any database SQLite, Oracle, PosgreSQL, MySQL, MS SQL or Firebird. I trace and audit calls from ZeosLib/Firebird and I see Driver only once Prepare statement and for all other calls only execute Execute with different transaction then Prepare transaction (First call Prepare and Execute use same transaction).
If explicit transaction is not started in mORMot IBX driver I Start before and Commit after every execution of statement (software auto commit). Preparing every execution will kill performance and stress memory with allocation and free of Data/Param buffers/metadata.
This check (FSQLParams.FTransactionSeqNo < (FTransactionIntf as TFB30transaction).TransactionSeqNo) disable running Execute with different transaction from prepare used transaction!

I understand your concern to remove this code.
Please, can you consider to add some property to Disable this check in statements execute and opencursor methods?
« Last Edit: September 17, 2021, 07:30:53 pm by ttomas »

tonyw

  • Full Member
  • ***
  • Posts: 225
    • MWA Software
Re: Question for IBX developers
« Reply #6 on: September 17, 2021, 06:01:34 pm »
The exception at line 12 is exactly what should happen when using Firebird. The statement was prepared using the transaction 'trans' at line 6 and then committed at line 9. When you commit thr transaction the prepare context is lost, and you will get an exception if you try and execute the statement without re-preparing the transaction. The exception is telling you explicitly that you have lost your transaction context.

You have three ways to avoid this happening.

1. Prepare the statement again after line 10, or

2. Use Commit Retaining at line 9, or

3. Use a different transaction to execute the query from the one used to prepare the query. In this case, the transaction used to prepare the query is kept active as long as you need access to the statement, The transaction used to execute the query can be committed and restarted (e.g.) after every execute.

Note that the API is intentionally low level and does not aautomatially prepare unprepared queries. A higher level database driver, such as IBX will "hide" the problem from you by automatically re-preparing the statement if it was in the unprepared state when you came to filling in the parameters.

ttomas

  • Full Member
  • ***
  • Posts: 224
Re: Question for IBX developers
« Reply #7 on: September 17, 2021, 07:30:03 pm »
The exception at line 12 is exactly what should happen when using Firebird. The statement was prepared using the transaction 'trans' at line 6 and then committed at line 9. When you commit thr transaction the prepare context is lost, and you will get an exception if you try and execute the statement without re-preparing the transaction. The exception is telling you explicitly that you have lost your transaction context.
Don't agree, line 12, not a Firebird exception, this is fbintf raise exception. re-preparing is not needed to execute. In my code this check/raise is //commented and everything works fine. Other Fb libs don't raise exception.

1. Prepare the statement again after line 10, or
This will kill performance

2. Use Commit Retaining at line 9, or
ZeosLib use CommitRetaining and me and other users complained by running 24x7 server and FB garbage collection, old active transaction and etc. This is one goal and inspiration for me to write mormot IBX driver with hard Commit.

3. Use a different transaction to execute the query from the one used to prepare the query. In this case, the transaction used to prepare the query is kept active as long as you need access to the statement, The transaction used to execute the query can be committed and restarted (e.g.) after every execute.
Not true, first call of statement start transaction, prepare, execute and commit transaction. Next call start transaction, execute and commit.
In mormot db driver you can explicitly start transaction BeginTransaction and every executed statement will be executed in this transaction context. At the end Commit or Rollback. In other case, if you don't call BeginTransaction every statement is atomic and I start and commit at end of execution. In IBX mormot db driver I have two transaction objects. One main transaction owned by connection object used for explicit BeginTransaction, and second internal transaction owned by Statement object used for software emulated auto commit when no explicit transaction is started. Same Statement can be executed with any of this two transactions. First call of statement execute Prepare and Execute in any of this two transaction, next calls only Execute this already prepared statement.
Default mormot server use 32 thread connection pool and per thread DB connection/attachment. Monitoring Fb for mvc-blog example, after some time "select * from MON$ATTACHMENTS" show 32 active connections.
"select * from MON$TRANSACTIONS" shows 0 transactions (ZeosLib 32 active transaction).
"select * from MON$STATEMENTS" shows <100 cached statements, column MON$STATE is 0 - idle. This idle statements exists on server as long as IStatement object exists and can be used to execute. MON$STATE can be: 0 - idle, 1 - active, 2 - stalled.

As I note early, everything work nice without this check/raise. Just ask for solution for users to not have to patch next versions of fbintf.
« Last Edit: September 17, 2021, 11:50:27 pm by ttomas »

 

TinyPortal © 2005-2018