FireDac Uncommitted Transaction on Select Query

[Tommo_194 0]

Hi, I have an issue with FireDac and Firebird DB, I spent some time tracking this issue down and found that if my select query returned greater or equal to the rowset size (default 50 in the firedac fetch options) (eg. select first 51) then a transaction would remain Active / uncommited, however if I select lower that the rowset size (eg. select first 49) the transaction will commit. We discovered while testing that we lost a large amount of data entry due to a crash (a separate issue), as it seems transactions were queued until the first transaction was committed or rolled back, no other transactions would be committed. Right now I have defaulted the fetch options - mode to fmAll, this gets all records and the select transaction commits this way. I know that will impact on performance but at least we get insert and updates committing as they happen now.

Anyone come across this? I can't see the logic in this and wonder if its a bug.

[FredS 140]

46 minutes ago, Tommo_194 said:

Right now I have defaulted the fetch options - mode to fmAll

To be sure add a TRACE to your db (server end), actually pretty simple thing to do.

 

Check this out:  https://stackoverflow.com/questions/77201632/growing-firebird-database-caused-by-commitretaining/77202004

More: https://docwiki.embarcadero.com/InterBase/2020/en/Using_isc_commit_retaining()

 

You have to call Commit to end a chain of CommitRetaining. If your TConnection uses Rollback as its default action all is lost, change that to RollbackRetaining.

 

But without Commit and while using Pooling this can get messy because the default query is usually set to ignore non committed data.

 

Edited Sunday at 06:43 PM by FredS

[Tommo_194 0]

Thanks for the reply, my TFDConnection (TxOptions) is set to AutoCommit=True AutoStart=True AutoStop=True DisconnectAction=xdCommit. If an initial select query is not performed (that returns record count over the rowset size) so no transactions are uncommitted at the time of performing updates or inserts, then all those Updates/inserts are committed and hit the database as they are executed. If however the first thing to happen is to open customer screen (has 125 records - fetch options rowset set to 50) the a transaction for that select query is left active (uncommitted) and then updates/inserts thereafter are not committed as they happen and the uncommitted customer query must first be committed to commit the updates/inserts. I know I could manually commit after doing the customer select query but it seems odd that I only would need to do this on tables with a record count over the rowset size, I also expected the AutoCommit setting to do this for me

[FredS 140]

1 minute ago, Tommo_194 said:

Thanks for the reply, my TFDConnection (TxOptions) is set to AutoCommit=True AutoStart=True AutoStop=True DisconnectAction=xdCommit. If an initial select query is not performed (that returns record count over the rowset size) so no transactions are uncommitted at the time of performing updates or inserts, then all those Updates/inserts are committed and hit the database as they are executed. If however the first thing to happen is to open customer screen (has 125 records - fetch options rowset set to 50) the a transaction for that select query is left active (uncommitted) and then updates/inserts thereafter are not committed as they happen and the uncommitted customer query must first be committed to commit the updates/inserts. I know I could manually commit after doing the customer select query but it seems odd that I only would need to do this on tables with a record count over the rowset size, I also expected the AutoCommit setting to do this for me

Somewhere in all that info in those two links you can see that AutCommit "may" mean CommitRetaining..

 

Either way, do a Trace and you will know why this happens.. far easier than attempting to find the cause in code.

 

[Tommo_194 0]

Commit retaining on trace is false, it seems to be intended logic with Mode rmOnDemand, I can see that the cursor is not closed until there is nothing more to fetch. Just seems problematic not to commit and use a new transaction each fetch. So will just use rmAll option 

[Olli73 7]

Is Connection.UpdateTransaction set?

 

What is the setting of Connection.TxOptions.StopOptions?

"xoIfCmdsInactive" could show your behavior.

[Anders Melander 2073]

There seems to be a misunderstanding of the relationship between transactions and cursors here - or maybe I just haven't understood your description.

 

Anyway, a server-size cursor is only valid within the transaction in which is was created. A client-side cursor is just a row pointer into a client-side rowset and does not need a transaction. I believe we are talking about server-size cursors here.

 

For an auto-transaction, the transaction will remain active while the cursor is active. So until you have fetched all rows, or closed the query, the transaction must remain active. You can't fetch some rows from a query, close the transaction, start a new transaction, and then continue fetching rows from the query.

 

Firebird (and Interbase) supports multiple transactions per connection so what you are doing should be possible. However, it sounds as if you are using implicit transactions which, as far as I know, will result in all implicit transactions sharing a single transaction.

What you should do is either use explicit transactions everywhere, or use implicit transactions when fetching data, but explicit when updating. This will isolate your updates from whatever goes on with the select queries. Of course you'll need to have some sort of synchronization in case the two parts operate on the same rows.