Multiple transactions lazarus / 16-Databases ---------------------------------------------------------------------- egsuh · 2024-05-22 03:38 I'd like to use two transactions with the same Firebird Database. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i      fbAQ: TIBConnection;     trAQ: TSQLTransaction;     trRollback: TSQLTransaction;      qr1, qr2, qr3: TSQLQuery; 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 · 2024-05-22 12:50 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 · 2024-05-24 02:41 @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 · 2024-05-24 06:57 Quote from: egsuh on May 22, 2024, 05:38:56 amWhen 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/fblangref4 0/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 · 2024-05-24 08:36 QuoteIs 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   FOR      SELECT * FROM table2 WHERE pid=:pid         info :f1, :f2, ...       do suspend;    UPDATE table1 SET pid_open=TRUE WHERE pid=:pid;  Please notice table names, table1 and table2. But, the same error continues to happen even though I comment out the last update statement. ---------------------------------------------------------------------- rvk · 2024-05-24 09:19 Quote from: egsuh on May 24, 2024, 10:36:39 amTrying 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 · 2024-05-24 09:43 EMFJI, but isn't it that the connection:transaction are 1:1? Unless the second transaction is strictly read-only? ---------------------------------------------------------------------- egsuh · 2024-05-25 04:14 QuoteBut 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 · 2024-05-25 06:32 Quote from: egsuh on May 25, 2024, 06:14:42 amBut 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/fblangref4 0/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 · 2024-05-25 12:31 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 · 2024-05-25 12:34 Quote from: egsuh on May 25, 2024, 02:31:33 pmI’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).