Are explicit commits okay when autocommit is on?
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;
AutoCommit is turned on, but it appears that the commit doesn't happen until the entire FOR loop completes. Therefore, I have added the Commit statement in the above code. Is this going to cause any unexpected results, or does this violate any best practices? (i.e. should I not make explicit calls to commit when AutoCommit is turned on?)
Thanks,
Scott
EDIT: Oops... I'm using Oracle 11g and Oracle SQL Developer as the client.
EDIT: Thank you for the responses, so far. At the point in time where the query is running, the data is being generated & tweaked. No other connections should be attempting to access the data. As to why I'm committing so often, during development, I run the query against a subset of the data and the query runs just fine. The table holds about 14 million records and I'm testing against about 100k. The query is fairly complex, and runs in about 5 minutes against this subset. When I move to run it against the whole table, the query runs for over 14 hours and fails to update any records. My theory is that holding that much undo information may be consuming all of the available resources on the development server. And if I make frequent commits, that undo information can be released and reused. Yes, it's slow. But if the query will actually complete, even if it takes all night, then it can be moved to the test server. (And performance tuning can be done at a later date.) The deadline for this has long since passed. (I was brought in to help out after the deadline was missed. And my area of expertise is not with Oracle.)
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.