Re: frequent commits

Chuck wrote:

> Pradeep wrote:
>

>>Please fix what i am missing here.
>>
>>The argument against frequent commits is that oracle needs the
>>transaction details, which is wiped out by COMMIT, to supports its
>>multiversioning facility.

No, that's not really it.

> The primary argument against frequent commits is that you can't roll
> them back. In theory, a transaction should be just that - one
> transaction from start to finish with a single commit or rollback at the
> end.

Right. The primary argument against frequent commits is that you should commit when the business rules indicate that a logical piece of work has been completed. Sometimes that is one row. Sometimes that is a lot of rows.

Sometimes programmers will throw in a commit every 100 or 1000 rows in the interest of performance. This is misguided - for one thing it is usually less performant since commits "cost" the same whether you're committing one row or a thousand. But the bigger reason is that by breaking your logical transaction into multiple physical transactions the database does not stay in a consistent state while you do the work. Moreover, if something goes wrong you have a half-completed mess on your hands to clean up.

Commit when the business rules indicate it's appropriate to do so, not for performance reasons.

> In reality however sometimes you need to do more frequent commits
> regardless of what some will say. If you need to run an update on every
> row of a 90 zillion row table, you're going to have to commit every now
> and then.

If you're updating 90 zillion rows, there's probably something wrong with the data design, i.e. no code table where ther should have been, non-normal data that needs to be recomputed, etc.

//Walt Received on Tue Jan 17 2006 - 10:28:41 CST