Recent

Author Topic: Firebird Separate Read and Update Transactions SQLdb  (Read 4846 times)

Turbonobo

  • New Member
  • *
  • Posts: 23
Firebird Separate Read and Update Transactions SQLdb
« on: July 13, 2017, 10:42:31 am »
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

  • Sr. Member
  • ****
  • Posts: 286
Re: Firebird Separate Read and Update Transactions SQLdb
« Reply #1 on: July 13, 2017, 02:05:23 pm »
The MSEgui version of Sqldb has separate transactions for reading and writing .


goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: Firebird Separate Read and Update Transactions SQLdb
« Reply #2 on: July 13, 2017, 02:52:15 pm »
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

  • New Member
  • *
  • Posts: 23
Re: Firebird Separate Read and Update Transactions SQLdb
« Reply #3 on: July 13, 2017, 04:19:49 pm »
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.

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

  • Sr. Member
  • ****
  • Posts: 286
Re: Firebird Separate Read and Update Transactions SQLdb
« Reply #4 on: July 13, 2017, 06:26:03 pm »
FIBPlus'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

  • Sr. Member
  • ****
  • Posts: 297
Re: Firebird Separate Read and Update Transactions SQLdb
« Reply #5 on: July 13, 2017, 06:43:02 pm »
Firebird 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.

- 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

  • New Member
  • *
  • Posts: 23
Re: Firebird Separate Read and Update Transactions SQLdb
« Reply #6 on: July 15, 2017, 10:01:51 am »
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.




goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: Firebird Separate Read and Update Transactions SQLdb
« Reply #7 on: July 15, 2017, 04:20:57 pm »
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.

 

TinyPortal © 2005-2018