Recent

Author Topic: TIBTransaction not firing AfterTransactionEnd event beyond first time  (Read 6866 times)

RedOctober

  • Sr. Member
  • ****
  • Posts: 452
I'm struggling with trying to learn the proper way to use TIBTransaction.  I've always used Delphi and DevArt components so this was always taken care of in the background.  I have 3 buttons on a form... Run, Commit, Rollback.  I run an SQL statement using the IBXScript parser.  I'm following the IBX Script example.  I'm trying to make an "Execute SQL Statement" window just like what is in FlameRobin, which has a "play" (F4) button, a "commit"
 F5 button, and a "rollback" F8 button.  I cannot make this work because once the transaction has been opened and commited or rollbacked ONCE, it never again fires the OnStartTransaction event nor the AfterTranscactionend event.  What am I doing wrong?  I need to have these two events fire reliably each time a transaction is started or ended.
I have attached a sample project. Click the "Help" link in the project for more explanation.

Platform:  Lazarus 1.8.1, FPC 3.0.5, IBX 2.2.0

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: TIBTransaction not firing AfterTransactionEnd event beyond first time
« Reply #1 on: March 09, 2018, 11:04:25 pm »
I'm not sure but that does seem like a bug.

In IBDatabase.pas:
Code: Pascal  [Select][+][-]
  1. procedure TIBTransaction.StartTransaction;
  2. var
  3.   i: Integer;
  4.   Attachments: array of IAttachment;
  5.   ValidDatabaseCount: integer;
  6. begin
  7.   CheckNotInTransaction;
  8.   CheckDatabasesInList;
  9.   if TransactionIntf <> nil then
  10.   begin
  11.     TransactionIntf.Start(DefaultAction);
  12.     Exit; // <-- this seems like a problem after the first time
  13.   end;
  14.  
  15.   // and all the way at the bottom
  16.  
  17.   if not (csDesigning in ComponentState) then
  18.       MonitorHook.TRStart(Self);
  19.   DoOnStartTransaction; // <-- which calls the OnStartTransaction
  20. end;

You could try putting DoOnStartTransaction; before the exit on line 12 but it seems like this bug needs to be fixed for real.

I changed it to this and then it works:
Code: Pascal  [Select][+][-]
  1.   if TransactionIntf <> nil then
  2.   begin
  3.     TransactionIntf.Start(DefaultAction);
  4.     DoOnStartTransaction;
  5.     Exit;
  6.   end;

RedOctober

  • Sr. Member
  • ****
  • Posts: 452
Re: TIBTransaction not firing AfterTransactionEnd event beyond first time
« Reply #2 on: March 09, 2018, 11:17:46 pm »
Thanks rvk.  I've emailed it to support at mwa

dsiders

  • Hero Member
  • *****
  • Posts: 1052
Re: TIBTransaction not firing AfterTransactionEnd event beyond first time
« Reply #3 on: March 09, 2018, 11:21:28 pm »
I'm struggling with trying to learn the proper way to use TIBTransaction.  I've always used Delphi and DevArt components so this was always taken care of in the background.  I have 3 buttons on a form... Run, Commit, Rollback.  I run an SQL statement using the IBXScript parser.  I'm following the IBX Script example.  I'm trying to make an "Execute SQL Statement" window just like what is in FlameRobin, which has a "play" (F4) button, a "commit"
 F5 button, and a "rollback" F8 button.  I cannot make this work because once the transaction has been opened and commited or rollbacked ONCE, it never again fires the OnStartTransaction event nor the AfterTranscactionend event.  What am I doing wrong?  I need to have these two events fire reliably each time a transaction is started or ended.
I have attached a sample project. Click the "Help" link in the project for more explanation.

Platform:  Lazarus 1.8.1, FPC 3.0.5, IBX 2.2.0

The assumptions you make in the Help procedure are incorrect. Calling Commit or Rollback makes the transaction inactive. Its events are never fired in subsequent passes because it is inactive.

Option A is to call CommitRetaining and/or RollbackRetaining to keep the transaction active.

Option B is to get really defensive and use explicit transaction management. Set IBScript.Transaction.Active := True immediately prior to running the script.

The difference is that IBX doesn't autostart transactions by default at runtime - but it does in the IDE. And TIBScript doesn't have the AllowAutoTransactionActive property that is in datasets.
« Last Edit: March 09, 2018, 11:25:08 pm by dsiders »
Preview Lazarus 3.99 documentation at: https://dsiders.gitlab.io/lazdocsnext

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: TIBTransaction not firing AfterTransactionEnd event beyond first time
« Reply #4 on: March 09, 2018, 11:33:25 pm »
The difference is that IBX doesn't autostart transactions by default at runtime - but it does in the IDE. And TIBScript doesn't have the AllowAutoTransactionActive property that is in datasets.
True, but the example still exposes a bug.

The TIBScript calls a separate routine in the example on a SELECT statement. In there IBDataset1.Open is done which again activates an inactive transaction (after a Commit). So the dataset does activate/start the transaction. That Transaction.Start should call the OnStartTransaction. But it only does this the first time.

I looked in the source and found the mentioned procedure (see my post). In there an internal TransactionIntf is used. And if that exists (not nil which is after the first time) the DoStartTransaction (and subsequent OnStartTransaction) is never fired.

I "hacked" that routine and after that it works as expected.

tonyw

  • Sr. Member
  • ****
  • Posts: 319
    • MWA Software
Re: TIBTransaction not firing AfterTransactionEnd event beyond first time
« Reply #5 on: March 10, 2018, 12:22:27 am »
I am not sure what the bug is that you are referring to.

TIBTransaction has worked the way it does since Borland Delphi. Commit/Rollback ends a transaction and all datasets that use it are implicitly closed. This is because the data is now stale and you need to start a new transaction and re-open them to get a fresh set of data.

The DBAdmin example shows one strategy for doing this. The "CurrentTransaction" on the DataModule has an AfterTransactionEnd event which starts a new transaction and re-opens the datasets - but only if the database is not being closed. That's an important check because a transaction may be being closed because the program is now terminating.

CommitRetaining is another strategy - but the downside of this is that the data _cached_ in the datasets may start to get out-of-date. You want to use this with care in multi-user applications.

TIBXScript can use either its own internal transaction or an external one. The reasons why you might want an external transaction are to have different transaction parameters or to control when the transaction is committed. If you do use an external transaction, the design of TIBXScript assumes that you do not commit the transaction while it is running. It may well fail if you do. On the other hand, if the "COMMIT;" statement is present in a script it will commit its transaction and immediately restart it.

RedOctober

  • Sr. Member
  • ****
  • Posts: 452
Re: TIBTransaction not firing AfterTransactionEnd event beyond first time
« Reply #6 on: March 10, 2018, 12:31:27 am »
Ok, so the proper way to use transactions is to close and reopen the database connection?  Seems like a lot of extra unnecessary overhead to be connecting and disconnecting to the database, perhaps 50,000 times a day for every mouse click of an app.  Versus just closing and re-issuing an SQL statement in a TQuery (or descendant)
« Last Edit: March 10, 2018, 12:33:42 am by RedOctober »

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: TIBTransaction not firing AfterTransactionEnd event beyond first time
« Reply #7 on: March 10, 2018, 12:49:34 am »
TIBTransaction has worked the way it does since Borland Delphi.
Yes, TIBTransaction works fine. But it has events that are not triggered correctly. I also use TIBTransaction massively in Delphi and I've never used the events of TIBTransaction. B.T.W. these events arent even in IBX for Delphi. But in IBX for Lazarus they are there (and they are faulty). I'm talking specifically about TIBTransaction.OnStartTransaction.

So they either should be removed from TIBTransaction or fixed.

It has nothing to do with TIBSQL and the given example is somewhat misleading. You can look at it much simpler. With the following lines you would start and end a transaction. But with the second and following Active := true TIBTransaction.OnStartTransaction is NEVER executed again. And that doesn't happen.

Code: Pascal  [Select][+][-]
  1.     IBTransaction1.Active:= true; // OnStartTransaction is executed
  2.     IBTransaction1.Active:= false; // can also be .Commit
  3.     IBTransaction1.Active:= true; // Now it doesn't execute anymore
  4.     IBTransaction1.Active:= false;
  5.     IBTransaction1.Active:= true;
  6.     IBTransaction1.Active:= false;
« Last Edit: March 10, 2018, 12:51:40 am by rvk »

tonyw

  • Sr. Member
  • ****
  • Posts: 319
    • MWA Software
Re: TIBTransaction not firing AfterTransactionEnd event beyond first time
« Reply #8 on: March 10, 2018, 12:35:46 pm »
Ok, so the proper way to use transactions is to close and reopen the database connection?  Seems like a lot of extra unnecessary overhead to be connecting and disconnecting to the database, perhaps 50,000 times a day for every mouse click of an app.  Versus just closing and re-issuing an SQL statement in a TQuery (or descendant)
No. you don't re-open the database connection. All you have to do is to start a new transaction after the current one completes. Firebird is a transaction oriented database (same as Oracle for that matter) and transaction isolation is how you manage multi-user database updates. IBX gives you full control over Firebird transactions and allows you to have as many active transactions as you need: anything from one to some ridiculously large number. As with any transaction oriented database until you commit a transaction any updates you make are not visible to other users and they are locked out from that table/record. So you need to commit fairly regularly. Not committing also means that you could lose your updates if you (e.g.) lose your connection to the database.

IBX also works with the TDataset model and a TDataset caches query results locally for performance reasons. When you commit a transaction all data read by that transaction is flushed and you have to reload (i.e. start a new transaction and re-open the dataset). If this was not done then you can't see the changes made by other users.

If you believe that transaction control is not for you and you never need to worry about multi-user access then maybe you should be using something like MySQL instead.

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: TIBTransaction not firing AfterTransactionEnd event beyond first time
« Reply #9 on: March 10, 2018, 12:50:18 pm »
No. you don't re-open the database connection. All you have to do is to start a new transaction after the current one completes.
Did you read my post? The problem is when you do a TIBTransaction.Active := true a second time (after you closed a transaction with a .Commit or .Rollback) the TIBTransaction.OnStartTransaction event isn't triggered anymore. That's the entire problem.

I already diagnosed this problem and I am very familiar with Firebirds transaction system.

You could say you don't need TIBTransaction.OnStartTransaction at all (I myself don't use it because in Delphi these events don't even exist). But when they are there (and they are added in IBX for Linux) they should work correctly.

Quote
If you believe that transaction control is not for you and you never need to worry about multi-user access then maybe you should be using something like MySQL instead.
RedOctober is trying to (re)create a Database manager (like IBExpert and FlameRobin) for Firebird. Therefore manual transaction control would be essential. In his example the enabling of the buttons Commit/Rollback are controlled by the new events from TIBTransaction (which don't work after the first time).

tonyw

  • Sr. Member
  • ****
  • Posts: 319
    • MWA Software
Re: TIBTransaction not firing AfterTransactionEnd event beyond first time
« Reply #10 on: March 10, 2018, 01:00:25 pm »
TIBTransaction has worked the way it does since Borland Delphi.
Yes, TIBTransaction works fine. But it has events that are not triggered correctly. I also use TIBTransaction massively in Delphi and I've never used the events of TIBTransaction. B.T.W. these events arent even in IBX for Delphi. But in IBX for Lazarus they are there (and they are faulty). I'm talking specifically about TIBTransaction.OnStartTransaction.

So they either should be removed from TIBTransaction or fixed.

It has nothing to do with TIBSQL and the given example is somewhat misleading. You can look at it much simpler. With the following lines you would start and end a transaction. But with the second and following Active := true TIBTransaction.OnStartTransaction is NEVER executed again. And that doesn't happen.

Code: Pascal  [Select][+][-]
  1.     IBTransaction1.Active:= true; // OnStartTransaction is executed
  2.     IBTransaction1.Active:= false; // can also be .Commit
  3.     IBTransaction1.Active:= true; // Now it doesn't execute anymore
  4.     IBTransaction1.Active:= false;
  5.     IBTransaction1.Active:= true;
  6.     IBTransaction1.Active:= false;

Events such as "AfterTransactionEnd" did exist in IBX for Delphi and were generated by the TIBTransaction, the difference is that they were only published by TIBCustomDataset descendents. IBX for Lazarus also publishes the event for TIBTransaction. The problem with the Delphi approach is that you almost end up selecting a dataset at random to decide which one fields the event - which doesn't always make for a maintainable program. IBX for Lazarus gives you the choice of handling the event using the IBX for Delphi approach or handling it centrally on a per transaction basis (or even a combination of both).

BTW, the code is simple enough. The IBX for Delphi approach was to poll each dataset in turn and calls the event handler if specified for each one. IBX for Lazarus does the same thing and then calls the TIBTransaction event handler if specified.

Thanks for pointing out the problem with the OnStartTransaction event. The problem was introduced with an optimisation in IBX 2 and also meant that the ISQL Monitor call was also lost. Already fixed in the development version.


tonyw

  • Sr. Member
  • ****
  • Posts: 319
    • MWA Software
Re: TIBTransaction not firing AfterTransactionEnd event beyond first time
« Reply #11 on: March 10, 2018, 01:04:26 pm »
No. you don't re-open the database connection. All you have to do is to start a new transaction after the current one completes.
Did you read my post? The problem is when you do a TIBTransaction.Active := true a second time (after you closed a transaction with a .Commit or .Rollback) the TIBTransaction.OnStartTransaction event isn't triggered anymore. That's the entire problem.

I already diagnosed this problem and I am very familiar with Firebirds transaction system.

You could say you don't need TIBTransaction.OnStartTransaction at all (I myself don't use it because in Delphi these events don't even exist). But when they are there (and they are added in IBX for Linux) they should work correctly.

Quote
If you believe that transaction control is not for you and you never need to worry about multi-user access then maybe you should be using something like MySQL instead.
RedOctober is trying to (re)create a Database manager (like IBExpert and FlameRobin) for Firebird. Therefore manual transaction control would be essential. In his example the enabling of the buttons Commit/Rollback are controlled by the new events from TIBTransaction (which don't work after the first time).
Yes I did read your post and responded to it separately. This is a response to RedOctober's post and correcting his assertion about re-opening database connections.

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: TIBTransaction not firing AfterTransactionEnd event beyond first time
« Reply #12 on: March 10, 2018, 01:14:25 pm »
Thanks for pointing out the problem with the OnStartTransaction event. The problem was introduced with an optimisation in IBX 2 and also meant that the ISQL Monitor call was also lost. Already fixed in the development version.
Ok, thanks.


RedOctober

  • Sr. Member
  • ****
  • Posts: 452
Re: TIBTransaction not firing AfterTransactionEnd event beyond first time
« Reply #13 on: March 10, 2018, 04:15:42 pm »
Message threading (who is responding to who) is always a problem on forums (even social media like Twitter).   Thanks for all the help from everyone.  I have added the required line to fix the bug and look forward to getting the next update of IBX from MWA.

All the history and approach options in this thread are very helpful.  Thanks again to all.  I will be able to proceed now.

tonyw

  • Sr. Member
  • ****
  • Posts: 319
    • MWA Software
Re: TIBTransaction not firing AfterTransactionEnd event beyond first time
« Reply #14 on: March 10, 2018, 06:04:48 pm »
  I have added the required line to fix the bug and look forward to getting the next update of IBX from MWA.
Be careful what you wish for, the next version will probably have a re-architectured version of the IBServices components -hopefully much easier to use a:d understand. The older version will continue to be available given that it is a radical change. If you want to see an early version then I can make one available.

 

TinyPortal © 2005-2018