Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Avoid too long active transaction with IBX components



Permlink Replies: 3 - Last Post: Feb 20, 2018 10:44 AM Last Post By: Jeff Overcash (...
Marco Andreolli

Posts: 10
Registered: 5/10/13
Avoid too long active transaction with IBX components
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 17, 2018 7:01 AM
My question is very simple, what is the best practice to avoid to have too long active transaction with an application that use many component TIBDataSet? I would avoid to have very old OAT and than have very bad performance

My application have more dataset that must be always opened (until the application is running), I would avoid to close and reopen the transaction because I will be reopen all dataset.

I must be replace this component? And if yes, what is the best choice?
ClientDataSet with DataSetProvider or what?

Thanks in advance

Edited by: Marco Andreolli on Feb 17, 2018 7:02 AM

Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Avoid too long active transaction with IBX components [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 17, 2018 9:53 AM   in response to: Marco Andreolli in response to: Marco Andreolli
On 2/17/2018 10:02 AM, Marco Andreolli wrote:
My question is very simple, what is the best practice to avoid to have too long active transaction with an application that use many component TIBDataSet? I would avoid to have very old OAT and than have very bad performance

My application have more dataset that must be always opened (until the application is running), I would avoid to close and reopen the transaction because I will be reopen all dataset.

I must be replace this component? And if yes, what is the best choice?
ClientDataSet with DataSetProvider or what?

Thanks in advance

Edited by: Marco Andreolli on Feb 17, 2018 7:02 AM


First run all read only queries through a Read Committed Read Only transaction
level (Pre-Committed). This type of query can stay open forever and not bother
anything.

For your Read/Write stuff that is staying open for a long time run it through a
ClientDataset. When the CDS has finished pulling all its data it will shut down
the transaction and only start new ones during update phases.

Also remember "long" is not really time related but in relation to the number of
transactions started/ended since it started. So long might be a few hours on
some systems or a few days on others.

The gap usually has to be in the millions for any real speed degradation to be
noticeable. What you will find though is that a long running snapshot
transaction causes an ever increasing amount of memory required to start the
next snapshot so be extra careful about long running snapshot transactions more
so than read committed.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Marco Andreolli

Posts: 10
Registered: 5/10/13
Re: Avoid too long active transaction with IBX components [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 19, 2018 11:32 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Jeff Overcash (TeamB) wrote:
For your Read/Write stuff that is staying open for a long time run it through a
ClientDataset. When the CDS has finished pulling all its data it will shut down
the transaction and only start new ones during update phases.

So, this could be the better solution then manage data with read-only transaction with tables not too big...

Also remember "long" is not really time related but in relation to the number of
transactions started/ended since it started. So long might be a few hours on
some systems or a few days on others.

This is a very interesting observation ;)

The gap usually has to be in the millions for any real speed degradation to be
noticeable. What you will find though is that a long running snapshot
transaction causes an ever increasing amount of memory required to start the
next snapshot so be extra careful about long running snapshot transactions more
so than read committed.

In one case I noticed many temps file and abnormal use of hard drive. It could be a consequence of abuse of snapshots transaction?
Now I have change to read-committed all snapshots transaction I had, because i don't really need snapshots transaction

Edited by: Marco Andreolli on Feb 19, 2018 11:32 AM
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Avoid too long active transaction with IBX components [Edit] [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 20, 2018 10:44 AM   in response to: Marco Andreolli in response to: Marco Andreolli
On 2/19/2018 2:32 PM, Marco Andreolli wrote:
Jeff Overcash (TeamB) wrote:
For your Read/Write stuff that is staying open for a long time run it through a
ClientDataset. When the CDS has finished pulling all its data it will shut down
the transaction and only start new ones during update phases.

So, this could be the better solution then manage data with read-only transaction with tables not too big...

Yes. There is a totally IBX solution that should work (I've never actually
worked this way). Use the IBUpdateSQL. So leave the Modify/Insert/Delete SQL's
blank for the IBDataset and set them in the UpdateSQL object. Set the
IBDataset's transaction to your precommitted transaction. In the BeforeOpen
event Set the Update Object's transactions to a Read/Write one like

procedure TForm6.dts1BeforeOpen(DataSet: TDataSet);
begin
   // trReadWrite is the Read Write transaction to use when updating data
   TIBUpdateSQL(dts1.UpdateObject).Query[ukModify].Transaction := trReadWrite;
   TIBUpdateSQL(dts1.UpdateObject).Query[ukInsert].Transaction := trReadWrite;
   TIBUpdateSQL(dts1.UpdateObject).Query[ukDelete].Transaction := trReadWrite;
end;


Then for single operations you can just start the transaction in the BeforePost
event and commit it in the AfterPost event (and BeforeDelete/AfterDelete but
they can point to the same events as the Post ones).

Or for more than one change per transaction start it. Do all your operations.
Commit/Rollback it. The main component can display all the data it wants and
the updating is done with a secondary transaction.

I've considered this actually at the IBCustomDataset level (an optional
read/write transaction) but I have never been satisfied with how to convey it
properly on how to use use cleanly.


Also remember "long" is not really time related but in relation to the number of
transactions started/ended since it started. So long might be a few hours on
some systems or a few days on others.

This is a very interesting observation ;)

The gap usually has to be in the millions for any real speed degradation to be
noticeable. What you will find though is that a long running snapshot
transaction causes an ever increasing amount of memory required to start the
next snapshot so be extra careful about long running snapshot transactions more
so than read committed.

In one case I noticed many temps file and abnormal use of hard drive. It could be a consequence of abuse of snapshots transaction?

No, temp files are sorting files. Up the sort file size in the IBConfig (server
restart needed). I tend to go 10 MB (default is 1MB). The main thing is
knowing how many of those temp files are getting created at the same time. What
you do not want to do is have the total memory needs to exceed system RAM and
getting off onto the paging file. It is rare to find systems though making more
than 5-8 sort files at the same time so 10MB is only 50-80MB more but keeps the
sorting in RAM and a speed boost.

Now I have change to read-committed all snapshots transaction I had, because i don't really need snapshots transaction

Unfortunately when I took over IBX back in 2000, the default (going back to the
FIB days) was to just have blank parameters for a newly created IBTransaction.
This is a snapshot transaction (or very close). If not for the backwards
compatibility issues I would have switched it to default to ReadCommitted back
then. It is so easy to just drop a IBTransaction on the form and not remember to
bring up the component editor and select the radio button for what you want and
end up with a snapshot.

It is one of the reasons I added SetTransactionLevel to TIBTransaction a few
releases ago to make it easier when creating things in code to quickly set it to
one of the standard transaction levels with one line of code vs adding all the
parameters by hand.

ibtrSnapshot, ibtrReadCommitted, ibtrReadOnlyTableStability,
ibtrReadWriteTableStability, ibtrPrecommitted


Edited by: Marco Andreolli on Feb 19, 2018 11:32 AM

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02