Re: commit or not commit inside a cursor loop

"Sybrand Bakker" <gooiditweg_at_nospam.demon.nl> schrieb im Newsbeitrag news:tms9dvop5bpq1hfaasom3ims26br486vt1_at_4ax.com...
> You should NOT commit inside a loop.
> a COMMIT ends a transaction, and releases all locks.
> Your read consistent view will be released, so the data will be
> possibly overwritten, resulting in ORA-1555 in your transactions.
> Just search the google archives and you will find hundreds of posts
> explaining why you shouldn't do it. Just search on ora-1555 or read
> the paper on Metalink with respect to ora-1555

There isn't any choice if you cannot enlarge your RBS/UNDO tablespaces for this transaction. The main problem is that Oracle has to maintain the read-consistency cursor so you might a receive (randomly) a 1555 anyway if other sessions are making changes to the data your cursor relies on. The problem here is RBS-contention. We got around this problem by running our program (VERY LARGE PL/SQL-program) in restricted mode. After migrating to Oracle 9i (and switching to UNDO-Management) the problem was finally solved.

BTW: Our main loop performs a COMMIT/ROLLBACK for each record, because every action is regarded as a transaction that might succeed or fail. What I want to say is: If the transactions are _enclosed_ in the loop it is OK to perform COMMITs to save disk-space. Most batch-jobs have such a loop where single contracts/etc. are transformed in the loop-body.

If performance comes into play: Do not COMMIT too often, since a COMMIT forces some synchronization in the engine so that there is a system specific limit for the tps.

Carsten Received on Wed May 28 2003 - 13:23:27 CDT