This is a far more opinionated post than usual. It is not meant to inflammatory, my goal for this post is to have something tangible that I can point to the next time an Oracle Developer thinks they want or need to “do” incremental commits.
In this post an incremental commit is defined as code which commits a transaction but the cursor is kept open. They are also known as a fetch across commit.
I encounter incremental commits in PL/SQL code that issues a commit inside a loop such as the examples below:
Example
...
CURSOR customers_cur
IS
SELECT columns
FROM some_tables;
BEGIN
FOR i in customers_cur
LOOP
-- doing something interesting with the row
COMMIT; -- ARGH!
END LOOP;
END;
The commit may be decorated with variations of:
IF rows_processed > some_arbitary_number
THEN
COMMIT;
Or
IF mod(customers_cur%rowcount, v_commit_limit) = 0
THEN
COMMIT;
Why are they an Anti-Pattern?
They introduce side effects that the Developer is not aware of, usually a self inflicted ORA-01555 Snapshot too old exception. I will come back to this in the final part of this post.
Why are Incremental commits used?
Over the years I have had many conversations with other Oracle Developers regarding the problems incremental commits cause. The common explanation I have heard for the introduction of incremental commits is that the developer didn’t want to “blow” the rollback segments.
I disagree with this. You should never commit inside a loop. You should commit when your transaction is complete and only then. If your rollback segments are too small to support your transactions then you need to work with your DBA and get them resized.
ORA-01555 Snapshot too old
I am going to spend the remainder of this post explaining why you will see this error when you perform an incremental commit. It will not be a deep dive into all the nuances of this exception just it’s relevance to incremental commits. The best explanation for ORA-01555 is this AskTom post which originally started some 18 years old. Much of what follows is distilled from this thread.
An ORA-01555 occurs when the database is unable to obtain a read consistent image. In order to obtain this information the database uses the rollback segment but if that information has been overwritten then the database can not use it and the ORA-01555 is raised. So what causes this information to be overwritten? In the context of incremental commits the fuse is lit when a commit is issued…
Here is the steps leading to this error taken from Oracle Support Note:40689.1
1. Session 1 starts query at time T1 and Query Environment 50
2. Session 1 selects block B1 during this query
3. Session 1 updates the block at SCN 51
4. Session 1 does some other work that generates rollback information.
5. Session 1 commits the changes made in steps ‘3’ and ‘4’.
(Now other transactions are free to overwrite this rollback information)
6. Session 1 revisits the same block B1 (perhaps for a different row).
Now, Oracle can see from the block’s header that it has been changed and it is later than the required Query Environment (which was 50). Therefore we need to get an image of the block as of this Query Environment.
If an old enough version of the block can be found in the buffer cache then we will use this, otherwise we need to rollback the current block to generate another version of the block as at the required Query Environment.
It is under this condition that Oracle may not be able to get the required rollback information because Session 1’s changes have generated rollback information that has overwritten it and returns the ORA-1555 error.
I have marked the key point – 5. By issuing a commit you are saying I have finished with this data, other transactions feel free to reuse it. Except you haven’t finished with it and when you really need it, it will have been overwritten.
Edge Cases?
I am not aware of any edge cases that require incremental commits. If you know of any please let me know via the comments.
Acknowledgements:
This post would not have been possible without the help from the following sources:
AskTom question Snapshot too old
Stackoverflow Question Commit After opening cursor
