Forum > Databases
Using second transaction to modify table
kapibara:
How do I use a second transaction to modify and save data so that open queries displaying data are not closed when calling commit? (sqlDB)
I have assigned the second transaction to the query used to save data with, but nothing is saved.
JanRoza:
Why use a second transaction? Use CommitRetaing instead of just Commit. This way the update is done and your table remains open.
kapibara:
I'm using CommitRetaining at the moment, but was adviced not to a while ago:
--- Quote ---Try not to use the CommitRetaining or RollbackRetaining in transactions. It leads to long running idle transactions which is generally considered to be a program bug. Instead use TSQLQuery option sqlAutoCommit or sqlKeepOpenOnCommit with Commit.
--- End quote ---
But then all queries have to be sqlKeepOpenOnCommit or else those that are not are closed when calling commit. And sqlKeepOpenOnCommit sets the PacketRecords property to -1 meaning all records will be read and loaded at once instead of ten or so at a time.
So I thought a second, shortlived, transaction would be the solution. But how to do it?
mangakissa:
Use second SQLQuery to execute your query. But not advisable, because it's not updating your first query tot the latest data
taazz:
Because readonly long leaved transactions are handled better by the underline database. So a doulbe transaction one long leaved so the user can spend the complete day browsing the same data over and over again and a write transaction for those one second commits is far better supported by the data engine than the long run or stack and possible forgotten read write transaction.
Navigation
[0] Message Index
[#] Next page