Recent

Author Topic: Using second transaction to modify table  (Read 3252 times)

kapibara

  • Hero Member
  • *****
  • Posts: 610
Using second transaction to modify table
« on: May 24, 2018, 01:26:26 pm »
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.
« Last Edit: May 24, 2018, 01:31:05 pm by kapibara »
Lazarus trunk / fpc 3.2.2 / Kubuntu 22.04 - 64 bit

JanRoza

  • Hero Member
  • *****
  • Posts: 672
    • http://www.silentwings.nl
Re: Using second transaction to modify table
« Reply #1 on: May 24, 2018, 01:37:16 pm »
Why use a second transaction? Use CommitRetaing instead of just Commit. This way the update is done and your table remains open.
OS: Windows 10 (64 bit) / Linux Mint (64 bit)
       Lazarus 3.2 FPC 3.2.2
       CodeTyphon 8.40 FPC 3.3.1

kapibara

  • Hero Member
  • *****
  • Posts: 610
Re: Using second transaction to modify table
« Reply #2 on: May 24, 2018, 02:39:37 pm »
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.

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?
« Last Edit: May 24, 2018, 03:38:54 pm by kapibara »
Lazarus trunk / fpc 3.2.2 / Kubuntu 22.04 - 64 bit

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: Using second transaction to modify table
« Reply #3 on: May 25, 2018, 12:22:19 pm »
Use second SQLQuery to execute your query. But not advisable, because it's not updating your first query tot the latest data
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

taazz

  • Hero Member
  • *****
  • Posts: 5368
Re: Using second transaction to modify table
« Reply #4 on: June 06, 2018, 08:26:29 am »
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.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: Using second transaction to modify table
« Reply #5 on: June 07, 2018, 01:32:57 pm »
Long running server transactions always consume server resources unnecessarily. This problem can be solved with a TSQLQueryReadOnly.Refresh in the AfterPost event of the write query.

Think this confusion comes from the popularity of FireBird around here which seams to handle transaction differently from most database engines.
« Last Edit: June 07, 2018, 04:03:19 pm by goodname »

 

TinyPortal © 2005-2018