I've been searching for a while and couldn't find this. I'm working with Oracle and have a For loop similar to:
BEGIN
FOR YEARIDs IN (SELECT DISTINCT YEARID From MyTable)
LOOP
UPDATE (
SELECT ......
)
SET MyFlag = 1;
COMMIT; -- Added
END LOOP;
END;
Committing inside a loop is generally a bad idea (so is allowing any tool to automatically commit).
Committing inside a loop makes it much harder to write restartable code. What happens if you encounter an error after 3 iterations? You've now successfully committed the results of 2 UPDATE
statements. Presumably, you'd need to then either figure out which rows were updated and write code to reverse the updates or you would have to add code that avoids attempting to update the data for those two successful yearid
values. That's certainly possible. But it involves writing a bunch of code to track your progress and generally makes your code much more complex.
Committing inside a loop makes your code much slower. Committing is generally a rather expensive operation. Doing it in a loop, therefore, is generally a bad idea. It's less of a problem if you only have a few dozen loop iterations. But if you have hundreds or thousands of iterations, you can easily end up spending the vast majority of your time committing.
Committing inside a loop substantially increases the risk that you'll cause an ORA-01555 error. Your query against MyTable
needs a read consistent view of the data. If you commit inside the loop, however, you're telling Oracle that your session no longer needs older UNDO
data. If Oracle happens to purge UNDO
data that you need for a subsequent iteration of the loop, you'll get an error. And then you're back dealing with non-restartable code where you'e successfully gone through N iterations but you don't know which years have been processed or which need to be processed.
Committing inside a loop can create data consistency issues. If some other session is running reports, for example, it is easy for those reports to see partially updated data which will often mean that the data will be inconsistent. If the data for 3 years has changed but other years have not, it can be very difficult to make sense of the reports and people (or processes) can easily make incorrect decisions.
Committing inside a loop also makes your code less reusable. If your code includes commits (or rollbacks other than to a savepoint you established inside the block), it cannot be called by any other piece of code that doesn't want its transaction committed yet. That leads people to try to re-implement your logic without the transaction control or to incorrectly violate transactional integrity which inevitably leads them to build applications that introduce data consistency issues.