Open transactions

[TopControl]

Hello,

we are using IBDAC with our application and for a while we have problems with open transactions.
After several checks over the MON$ tables we found, that a select statement from a IBDAC component is involved with the open transactions.
So I would ask you, if you just have heard from this problem?

We have running a firebird database on a server and our application on a other server. The two servers are in two different networks.
To control the incoming and outgoing network traffic between these networks is used a firewall. Unfortunately we don't manage the firewall.

So we asume that the firewall sometimes will cut us the communication and then the transaction from this select statement remains open:

Code: Select all

SQL := 'SELECT CST.RDB$CHARACTER_SET_ID CONN_CH, CST.RDB$BYTES_PER_CHARACTER CONN_CH_LEN,' + #13#10 +
        'DB.RDB$CHARACTER_SET_NAME DB_CH, DB_CST.RDB$BYTES_PER_CHARACTER DB_CH_LEN, ' +
        'DB_CST.RDB$CHARACTER_SET_ID DB_CH_ID' + #13#10 +
        'FROM RDB$CHARACTER_SETS CST, RDB$DATABASE DB, RDB$CHARACTER_SETS DB_CST' + #13#10 +
        'WHERE (DB_CST.RDB$CHARACTER_SET_NAME = DB.RDB$CHARACTER_SET_NAME)';
        if CharsetIdNeeded then
          SQL := SQL + #13#10 + 'AND (CST.RDB$CHARACTER_SET_NAME = UPPER(''' + vCharset + '''))'
        else
          SQL := SQL + #13#10 + 'AND (CST.RDB$CHARACTER_SET_NAME = DB.RDB$CHARACTER_SET_NAME)';

Do you can tell me, why this select statement of the transaction remains open?
We use IBDAC Version 4.6.12 on Delphi2009! IsolationLevel ReadCommitted!
Firebird 2.5.1 and Firebird 2.5.4 on Windows Server 2008 and 2012 and clients on Win XP and Win 7!

Thanks in advanced!

[ViktorV]

InterBase/Firebird requires an active transaction for any operation with data, even for opening a dataset. Therefore, when TIBCQuery.Open is called, the transaction related to it is checked to be active, and if the transaction is inactive - it is started automatically. When closing the dataset, transactions related to it are not closed automatically.

[TopControl]

Thank you for your answer! Do you have any suggestion, how we can fix this? Maybe by committing the DefaultTransaction from the DefaultConnection?

[ViktorV]

In such case, you should use the TIBCQuery.StopTransaction method. Note, when closing a transaction, the dataset related to it will also be closed.

[emailx45]

Hey guys DV,

Why dont to use one OPTION to explicite ACTIVE in obj TRANSACTION as in FIBPLus?

In FIBPlus there is the INTERNAL TRANSACTION (as you using), BUT the user can START or STOP one obj TRANSACTION when desire. THIST DONT CLOSE THE INTERNAL TRANSACTION!

Thats the way, we have major control about obj TRANSACTIONS, and, WHEN on transaction was STARTED we dont have none ERROR (exception), because the code internal see that TRANSACTION already is OPENED and nothing is done.

Other, why we dont have one property INTRANSACTION in obj TRANSACTION, but have in DATABASE CONNECTION?

I believe that we need to see the TRANSACTION, dont UniConnection! The UniConnection can (too) see the status in INTRANSACTION from obj TRANSACTIONS.

thanks

[emailx45]

I my projects, yet, use FIBPLus, but i would like buy DevUNIDAC for new projects.

In my projects, FIBPLUS its very (more) easy than UniDac (IBDac) (my opinion) when using transaction (1 to RO (live all time) and 1 to RW (live only in update - delete)

Its very easy make one Transaction pattern and later, create obj Transactions on-the-fly - without lost the control.

The INTERNAL TRANSACTION is only used by FIBPLUS, and my obj Transaction I define when to use or not.

Now, I dont have none problem with that tactic when using my FIREBIRD Server.

Maybe, will be good the user have options to control your application conversation with your server SQL without stay "stuck" to the arbitrary code.

We must to know that we're in 2016, no in 1980

[ViktorV]

To control transactions manually, you can use separate transactions for each dataset, using the properties: TCustomIBCDataSet.Transaction and TCustomIBCDataSet.UpdateTransaction. You should use the TIBCTransaction.Active property to determine whether the specified transaction is active.

[flls]

You get 100% control of transactions as follows:

1. There will always be an open transaction when using Interbase / Firebird, so leave the default transaction exclusively for that;
2. Add another UniTransaction component;
3. Link the new UniTransaction to the UpdateTransaction property of the TUniQuery components;
4. The final step and the MOST IMPORTANT OF ALL: in each TUniQuery, in SpeciftOptions, Interbase drive, set AutoCommit = False.

To use, just do the StartTransaction, Commit and Rollback using the TUnITransaction component (not the connection). All queries linked by the UpdateTransaction property shared the same transaction ... if you rollback, any changes to any linked query will be undone.

Enjoy.

[ViktorV]

Thank you for interest to our product.
You are absolutely right.
Feel free to contact us if you have any further questions about our products.