Clever Geek Handbook

Interbank transaction monitoring

I have a very strange problem with transactions in Interbase 7.5 that seem to get stuck.

I can track the issue with IBConsole -> right click DB -> Performance Monitor -> Transactions

Typically, this list should show only a few active transactions. But when starting the application I get several hundred active transactions (web module for apache web server using Delphi 7 Interbase components like IBQuery, IBTransaction, ...)

The transaction type is always displayed as a snapshot, if relevant.

I've already triple checked all sql statements and can't find anything that should cause problems like this ...

Is there any way to get the sql statements of a specific transaction?

Any other suggestion on how to find such a problem would be highly welcome.

+3


source to share


2 answers


Is there any way to get the sql statements of a specific transaction?

Yes, you can choose from TMP$STATEMENTS WHERE TRANSACTION_ID = ...

. This is from memory, but you have to start.



In IB Performance Monitor, you can find the transaction from the instructions tab using the button on the toolbar. Can't remember if you can go the other way in this app. I've been writing this for a long time!

+2


source


Active IBX datasets require an active transaction at all times. If you don't have active datasets, just remember to commit all active transactions.

If you have active datasets, you can configure all of your components to use the same object TIbTransaction

, and you can also configure a unique TIbTransaction to commit or rollback after a period of inactivity timeout via IdleTimer

and DefaultAction

.

Aborting a transaction (manually or automatically committing or rolling back) closes all associated datasets ( TIBQuery

, TIBTable

etc.).



You may be tempted to use CommitRetaining

or methods RollbackRetaining

to abort a transaction without closing the relevant datasets, but this can affect server performance, and my advice should always be to avoid using it.

If you want to improve your application, you should consider changing your database connection layer, or implementing a memory-aware dataset in IBX

Delphi TClientDataSet

, for example , which allows you to fetch data and store it in memory by closing all underlying datasets (and transactions), allowing you to use traditional Insert / Append / Edit / Delete methods to modify data, and then apply those changes to the database in a new short transaction.

+2


source






More articles:


All Articles