Re: When to Commit?

Mark D Powell wrote:
> 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 --

I agree with Sybrand on this one. I have worked on many a multi-TB database and everytime I see the issues you bring up I go back and fix the underlying design and/or processes.

Physical transactions should be as large as the underlying logical transaction.

Take for example one of your statements: "but even a two second wait may be two long for an OLTP user." They may not wish to wait but if it takes that long to complete the logical transaction then they should either wait or, as previously stated, the design needs to be re-examined.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)