On 2/17/2018 10:02 AM, Marco Andreolli wrote:

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)

On 2/19/2018 2:32 PM, Marco Andreolli wrote:

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.


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.


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

--
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)