Firebird Separate Read and Update Transactions SQLdb lazarus / 16-Databases ---------------------------------------------------------------------- Turbonobo · 2017-07-13 08:42 When using a Firebird database, I prefer to have separate long read and short update transactions. I can "easily" do this using FibPlus (Only Delphi?), Their connection component has a separate DefaultUpdateTransaction property. With SQLdb I can only set a single default transaction for both reads and updates.  Reading records and applying updates with TSQLQuery uses the same transaction. Is there a workaround? Or is there a different Freepascal/Lazarus compatible component set that supports this? ---------------------------------------------------------------------- mse · 2017-07-13 12:05 The MSEgui version of Sqldb has separate transactions for reading and writing . ---------------------------------------------------------------------- goodname · 2017-07-13 12:52 Every TSQLQuery can have it's own transaction. Create a read query and link it with a dedicated read transaction and a write query and link it with a dedicated write transaction. ---------------------------------------------------------------------- Turbonobo · 2017-07-13 14:19 Quote from: goodname on July 13, 2017, 02:52:15 pmEvery TSQLQuery can have it's own transaction. Create a read query and link it with a dedicated read transaction and a write query and link it with a dedicated write transaction. For some cases this approach will work. But often I want to achieve something different: Besides setting a default read and a default write transaction for a connection, FIBPlus's Dataset can also have two dedicated transactions: "Transaction" and "UpdateTransaction". Having separate read and update transactions allows to do this: - open the dataset and read records with the read transaction (a long lived read only transaction) - edit the dataset's buffer. - ApplyUpdates with the update transaction (a very short lived write transaction) Firebird works better with short write transactions. ---------------------------------------------------------------------- mse · 2017-07-13 16:26 Quote from: Turbonobo on July 13, 2017, 04:19:49 pmFIBPlus's Dataset can also have two dedicated transactions: "Transaction" and "UpdateTransaction". MSEgui also works with that principle. In MSEgui there are TSQLQuery.transaction/TSQLQuery.transactionwrite and T*Connection.transaction/T*Connection.transactionwrite. ---------------------------------------------------------------------- goodname · 2017-07-13 16:43 Quote from: Turbonobo on July 13, 2017, 04:19:49 pmFirebird works better with short write transactions. All databases work better with short lived write transactions because you want to block other transactions for as short a time as possible. Quote from: Turbonobo on July 13, 2017, 04:19:49 pm- open the dataset and read records with the read transaction (a long lived read only transaction) Long running idle transactions are generally considered to be a client side software bug. 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. ---------------------------------------------------------------------- Turbonobo · 2017-07-15 08:01 Quote from: goodname on July 13, 2017, 06:43:02 pmAll 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. Quote from: goodname on July 13, 2017, 06:43:02 pmLong 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 Quote from: goodname on July 13, 2017, 06:43:02 pmI 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. ---------------------------------------------------------------------- goodname · 2017-07-15 14:20 I've heard that Firebird has taken a different philosophical approach to the way they implemented transactions than most database engines. I'm sure there are pros and cons to the differing approaches. Good to see that sqoKeepOpenOnCommit is as useful for you as it has been for me. I think it should be the default behaviour but it is probably not due to backwards compatibility and possibly Delphi compatibility.