Recent

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

ttomas

  • Full Member
  • ***
  • Posts: 227
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: 227
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: 229
    • 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: 227
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: 229
    • 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: 227
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: 229
    • 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: 227
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 »

tonyw

  • Full Member
  • ***
  • Posts: 229
    • MWA Software
Re: Question for IBX developers
« Reply #8 on: September 19, 2021, 05:47:47 pm »
The problem with your approach is that you run the risk of the statement BLR becoming out-of-date. If you are keeping a statement prepared over a long time - which you seem to suggest that you are - then this could be a serious problem.

My recommended approach in this situation is to have a separate transaction for the prepare and the execute phases. The former you keep open for as long as you want to keep using the same statement BLR and it acts as a guard against a ddl change. The cost of do this is at most one extra transaction start/commit and I don't see why that  should be a big problem.

On the other hand, and in another universe, I am very critical of the Gnome 3/gtk3 devs for ignoring user complaints and enforcing their preferred style on everyone. The result was inevitably a fork of Gnome 2 (MATE) and that gtk2 remains in widespread use (e.g. with Lazarus). I thus do not want to go down the road of ignoring requests that I don't like.

Given that adding an option to suspend stale reference checks will only affect the users that use it, I am willing to add  to IStatement

procedure SetStaleReferenceChecks(Enable:boolean);
function GetStaleReferenceChecks: boolean;

with the defeult being "enable=true". However, expect the documentation to include some pointed comments about those who choose to use it.

ttomas

  • Full Member
  • ***
  • Posts: 227
Re: Question for IBX developers
« Reply #9 on: September 19, 2021, 08:33:18 pm »
Thank you very much for adding Set/Get methods in IStatement interface.

The problem with your approach is that you run the risk of the statement BLR becoming out-of-date. If you are keeping a statement prepared over a long time - which you seem to suggest that you are - then this could be a serious problem.
Creating web services with Db is not a easy task, where Db layer is main а bottleneck for performance.
Risk of keeping prepared statements for long period of time is a real concern for me too. One solution is using ConnectionTimeOutMinutes property of Db connection pull and destroy all connections and associated statements after some period of inactivity. For some systems for ex. data logger service where 24x7 serve hundreds of automation devices and never reach ConnectionTimeOutMinutes some periodic destroy of connection pull will be used.

My recommended approach in this situation is to have a separate transaction for the prepare and the execute phases. The former you keep open for as long as you want to keep using the same statement BLR and it acts as a guard against a ddl change. The cost of do this is at most one extra transaction start/commit and I don't see why that  should be a big problem.
DDL change on working production system is no option to resolve at runtime. Usually mormot server on startup in main thread/connection do all ddl changes, before start of connection pull.
I have several systems developed with mormot/ZeosLib/Firebird and monitoring, trace and audit is very hard to do, to find some bugs. Having a system were nobody is connected and you have open >32 transactions, monitoring MON$TRANSACTIONS is no go for me. This is main inspiration for me to create Ibx mormot driver. Now I have 0 transactions when nobody is connected and if some transaction or active statement exists then I know some bad things happen.

Thank you again for everything

tonyw

  • Full Member
  • ***
  • Posts: 229
    • MWA Software
Re: Question for IBX developers
« Reply #10 on: September 21, 2021, 12:14:22 am »
I have now implemented the requested feature. You can checkout a development snapshot using

svn co https://svn.mwasoftware.co.uk/public/ibx/trunk ibx

or just go to https://svn.mwasoftware.co.uk/ibx to view the update and download a tarball. Disabling stale reference checks is now included in the test suite as part of Test 16.

 

TinyPortal © 2005-2018