Re: When to Commit?

Edmond, I will disagree with some of the other posts on the thread. because while committing within a process that reads and updates can result in 1555 errors it is also sometimes a necessity for concurrency and can sometimes result in a performance benefit.

Where there are concurrent DML operations against a target table then holding a row lock for the duration of a long running update task may be very undesirable. It should be acceptable for a batch process to wait a few seconds for a row, but even a two second wait may be two long for an OLTP user. You have to balance the two competing needs.

Tasks that update and then revisit Oracle data (including index) blocks have to build consistent read images of the block. For some processes this consistent read count can be very large and a adding a few commits to the process can result in significantly reduced run time. Again there is a trade off.

The choice of how often to commit depends on the task and the concurrent DML demand on the rows being processed. Generally speaking you want to do as much work as possible with as few commits as concurrent access needs allow.

HTH -- Mark D Powell -- Received on Sat Jul 02 2005 - 16:22:07 CDT