All databases work better with short lived write transactions because you want to block other transactions for as short a time as possible.
Blocking is not really an issue with Firebird and its multi-generation architecture,
but overlapping transactions could cause many versions of the same record and degrade performance.
Long running idle transactions are generally considered to be a client side software bug.
The long running transaction is not really a problem as long as I use a read-only read-committed transaction.
Writes won't block reads and the read only transaction is considered by Firebird as an "already committed' transaction.
Both are a Firebird thing, but indeed could cause serious trouble for some RMDBS's
I suggest that you use either TSqlQuery.Options.sqoAutoCommit which will only open the transaction long enough to complete the query or TSqlQuery.Options.sqoKeepOpenOnCommit which means you can commit your transaction without closing your local dataset. I have not tried it but using sqoAutoApplyUpdates along with sqoAutoCommit might do the job for you.
I am not so fond of AutoCommit and AutoApplyUpdates, I prefer to control when transactions are committed myself to keep data consistent.
KeepOpenOnCommit on the other hand seems to be the way to go for me.
This worked for me:
I use TSQLQuery with KeepOpenOnCommit = true and hooked op to a TSQLTransaction with it's parameters set to 'read read_committed nowait rec_version'
Next I open the TSQL and immediately commit afterwards.
The data is in the buffer ready to be edited by the user.
But starting the transaction and Apply Updates is not possible now (read only transaction).
Changing the TSQLQuery's Transaction property is not possible either because the dataset is still open.
What does work is changing the parameters of the same Transaction to 'write read_committed wait no_rec_version',
start the transaction, Apply Updates and commit. (I verified by monitoring the MON$TRANSACTIONS table)
So both transactions are kept very short as a bonus and writes don't block reads
If anyone thinks this method could cause trouble... feel free to share your doubts.
I am even considering writing some kind of background service that periodically checks MON$TRANSACTIONS for write transactions that are kept alive for to long and rolls them back.