Multiple transactions

[egsuh]

I'd like to use two transactions with the same Firebird Database.

Code: Pascal  [Select][+][-]

1.      fbAQ: TIBConnection;
2.      trAQ: TSQLTransaction;
3.      trRollback: TSQLTransaction;
4.  
5.      qr1, qr2, qr3: TSQLQuery;
6.  

fbAQ.Transaction is defined as trAQ.
qr1 and qr2's transaction is trAQ, while qr3.transaction = trRollback.

trAQ.stoExplicitStart is on.

When I try to open qr3 with trRollback, I get error saying "trying to implicitly start trAQ".  -- This does not happen at first try from opening the application. It happens at second try of opening qr3.

Is there something wrong with my approach?  I need two transactions, as while I'm operating on one set of dataset, I have to check other values in the database.

[babycode]

If you are using SQLdb, I noticed that it always closes the query after iteration. One solution I found was to create a temporary query to query the data behind while another already created query is inserting the data.

[egsuh]

@babycode

Thank you for your concern. Transaction.Commit or Transaction.Rollback closes the related TDataSets like TSQLQuery, etc. So there are CommitRetaining and RollbackRetaining.

[rvk]

When I try to open qr3 with trRollback, I get error saying "trying to implicitly start trAQ".  -- This does not happen at first try from opening the application. It happens at second try of opening qr3.

Is that the COMPLETE error message ???

You have a problem starting a transaction.
So you need to look at all (both) transaction parameters.
Are they read/write/snapshot/record_version etc.

https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref40/fblangref40-transacs.html

It could be that you are trying to read something, that the other transaction has exclusive write access to, prohibiting reading on another transaction.
In that case you need to change the transaction params to record_version.

I normally use the params: read_committed + rec_version + nowait

If you still have problems, then we need to know the database structures (tables etc) and the SQL you are trying to use, to determine if there is some exclusion and deadlock.

[egsuh]

Is that the COMPLETE error message ???

Not exactly the same, but that's all.

Trying to start transaction for fbAQ implicitly, transaction "trAQ" is nearer to exact message.

Hmmm... SQL's are not simply reading ...  it tries to read from stored procedure, and tries to write to a field of another table. But shouldn't this a problem as it is controlled within a transaction that opens the stored procedure?  The stored procedures' contents are similar to the following:

Code: SQL  [Select][+][-]

1.   FOR
2.      SELECT * FROM table2 WHERE pid=:pid
3.          info :f1, :f2, ...
4.  
5.      do suspend;
6.  
7.   UPDATE table1 SET pid_open=TRUE WHERE pid=:pid;
8.  

Please notice table names, table1 and table2.

But, the same error continues to happen even though I comment out the last update statement.

[rvk]

Trying to start transaction for fbAQ implicitly, transaction "trAQ".

But this isn't really an error message. It's just a status update of what it's trying to do.

A real error should be something like "failed to start transaction" with some error codes.

What's exactly the problem?
Is this an exception error?
Can you (press) continue?

[alpine]

EMFJI, but isn't it that the connection:transaction are 1:1? Unless the second transaction is strictly read-only?

[egsuh]

But this isn't really an error message. It's just a status update of what it's trying to do.

If I set the (should be idle) transaction stoExplictStart then this raise exception. I cannot continue the run.
But when I do not set stoExplicitStart then this doesn't do big harm to me right now (it gets implicitly active and nothing else), but I'm worried about the possibility that an active transaction may slow down other operations on the database. Just wondering the default transaction for a database might start automatically.

When I studied Delphi, the examle codes were like

          if not transaction.InTransaction then Transaction.Starttransaction;

I think this is assuming transaction may be intransaction state, without my explicit start. So I'll study further.

[rvk]

But when I do not set stoExplicitStart then this doesn't do big harm to me right now (it gets implicitly active and nothing else), but I'm worried about the possibility that an active transaction may slow down other operations on the database. Just wondering the default transaction for a database might start automatically.

Everything that happens with a database needs to be in a transaction.
Even reading the database.

So if have it set at explicit you NEED to start the transaction yourself.
If you don't, then you'll get an error the transaction isn't started.
Maybe that's what's the error is trying to say.

https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref40/fblangref40-transacs.html

If you don't want to use the default transaction attached in the database you can set that one to explicit and just never start it. But you do need to start the transactions attached to a query, either explicitly or implicitly. If you don't, Firebird would know in what transaction context the query should be done.

[egsuh]

I’m using another transaction. The point is, when I try to open a query with transaction2  transaction1 is started as well. The operation intended with transaction2 is done correctly.

[rvk]

I’m using another transaction. The point is, when I try to open a query with transaction2  transaction1 is started as well. The operation intended with transaction2 is done correctly.

Then transaction1 is somehow connected to the first (or its dataset).
A transaction wont start automatically unless it's instructed to do so (either directly or by its connected components).