On 2/19/2018 2:32 PM, Marco Andreolli 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)
Connect with Us