ttsCommit in a while select loop

[ch100]

It is sometimes a requirement to do a commit after a certain number of
transactions rather than wait until the end of processing. This would
typically happen where a large number or records are updated and waiting to
the end would create a huge update transaction with a lot of locked records
and memory implications. Embedding a ttsbegin/ttscommit after a certain
number of iterations within the 'while select for update' loop will commit
the transaction but will reset the loop to the start of the select cursor.
This results in a never ending loop. Is there any solution for this problem ?

[Markus]

Use the RecordInsertList for larger numbers of records. It a lot mor
performant than doing insert() by hand in a loop. See the documentation ;)
--
My LinkedIn Profile:
http://www.linkedin.com/in/noebauer
Xing:
https://www.xing.com/profile/Markus_Noebauer

Post by ch100
It is sometimes a requirement to do a commit after a certain number of
transactions rather than wait until the end of processing. This would
typically happen where a large number or records are updated and waiting to
the end would create a huge update transaction with a lot of locked records
and memory implications. Embedding a ttsbegin/ttscommit after a certain
number of iterations within the 'while select for update' loop will commit
the transaction but will reset the loop to the start of the select cursor.
This results in a never ending loop. Is there any solution for this problem ?

[unknown]

"ch100"

Ch100, look at 2 examples below. They can be applied it you are able to
resume the processing after application crash, (you can distinguish the
processed data from unprocessed).

Because system may stop work in ANY PLACE of the code, and if you cannot
clearly distinguish 'done' from 'to do' you have to have LONG transaction.

HTH
--
Michal

while(lastProcessed)
{
ttsbegin;
counter = 0;
lastProcessed = '';
while
select forUpdate table
where table.Key >= lastProcessed
&& !isProcessed(table)
&& [.....]
{
[...your stuff here...]
lastProcessed = table.Key;
counter += 1;
if(counter > limit)
break;
}
}

Second try, should also work:

while
select table
where !isProcessed(table)
&& [...selections...]
{
ttsbegin;
table.selectForUpdate(true);
table.reRead();
if( [...selections...])
{
[...your stuff...]
}
else
//skip record as it was changed after was fetched by outside
loop
ttscommit;
}