Committing inside a loop (was: Re: Deadly sins againts database performance/scalability)

Frank <fbortel_at_nescape.net> wrote:
> Mark D Powell wrote:
>
> > pagesflames_at_usa.net (Dusan Bolek) wrote in message
> > news:<1e8276d6.0311250803.3cbc43be_at_posting.google.com>...
> >
> >>Hello,
> >>
> >>I'm just creating some document how developers should/must not behave
> >>when dealing with Oracle database. I written down some basic rules and
> >>came with idea what's the most stupid thing that you can do to your
> >>database.
> >>I have two on mind, first one is not using BIND variables and the
> >>second one is using COMMIT in a loop statement. However, there will be
> >>more of these.
> >>So I have question to you here. What's the most stupid, but in real
> >>world foundable, mistakes in development applications using Oracle
> >>datatabase?
> >
> >
> > Dusan, I would suggest you change your wording about issuing commit
> > within a loop since in many cases the commits need to be performed
> > within a loop. I think it is the frequency of commits rather than the
> > logical coding structure that is the issue. In my opinion how
> > frequently a commit should be issued is dependent on how likely
> > another session will need update access to the rows being processed.
> > In a transaction driven system the inventory rows may well require
> > commits be issued on a per-row or very few row basis. While another
> > program in the application, which is the only source of update
> > activity for a table that has low select activity, would best be
> > served by one or very few commits.
> >
> > Be careful in the process of trying to get something into the hands of
> > developers that you do not over simplify the problem and end up making
> > a statement that you later consider "dumb". Writing for developers is
> > not an easy task.
> >
> > Good luck
> > -- Mark D Powell --
>
> I'm sure Dusan referred to the "we have a possible 1555, so let's
> commit every ...uhm, 5000 processed rows".

But I think this just demonstrates Mark's point. If you have to already know what he is talking about in order to know what he is talking about, then it isn't a very good educational document.

However, your supposition seems to require that developer are 1) reading the manuals and thinking carefully about what problems may arise, 2) taking pre-emptive action to forestall those problems, and 3) making things worse rather than better.

Now 3) I buy completely, but the first two seem foreign to most of my experience.

> Committing within a loop to "prevent" 1555's from happening is
> creating the 1555's!

I recall first encountering 1555s, and they were definately not created by committing inside a loop, because I wasn't doing that. In fact, the solution to the 1555s did involve moving the commit from outside the loop to inside a loop, but not doing it in the naive manner I first tried. (And it also solved the companion problem, of locking others out of the rows for too long).

So if you tell people that committing inside the loop is causing the 1555s, when those existed even before they moved the commit into the loop, it isn't going to inspire developers to take you seriously.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB