Update and Loop Same Table Object

Hello Everyone ,

I want to update and loop same table object .But which way is better approach?

Examples are as follows.

1----->>>>

mytablebuffer mytablebuffer; //declaration

while select forupdate mytablebuffer

{

if (condition)

{

ttsbegin;

mytablebuffer.field = anyvalue;

mytablebuffer.update();

ttscommit;

}

}

2----->>>>

mytablebuffer mytablebuffer1,mytablebuffer2; //declaration

while select mytablebuffer1

{

if (condition)

{

select forupdate mytablebuffer2

where mytablebuffer2.recid == mytablebuffer1.Recid

ttsbegin;

mytablebuffer2.field = anyvalue;

mytablebuffer2.update();

ttscommit;

}

}

I don’t like either, because it never runs select forUpdate in transaction, which would be needed for pessimistic locking. If you used pessimistic locking, the latter case (if it was written correctly) would take update lock for individual records instead of locking the whole table as in your first case (which would have to use transaction around the whole loop).

Doing additional select in the loop has very bad impact on performance, of course. If you want to update most records, it makes little sense.

Nevertheless the first decision should be how to design transactions. If the process fails in the middle, should previous updates be rolled back or kept in database? Only when you know functional requirements, you can design your implementation.