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 an 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 an additional select in the loop has a 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.
1 Like