"Pradeep" <agarwalp_at_eeism.com> wrote in message
news:1132913827.282589.222410_at_g43g2000cwa.googlegroups.com...
> I am sorry, but i think i wasn't clear enough. Let me rephrase it
>
> Lets say i have table (Col1,Col2) . This table has 100,000 rows.
>
> Now I run a query to update Col1. This update might take lets say 5
> minutes. At this point of time if someone else wants to update Col2. he
> cannot because its locked my be. He has to wait for that 5 minutes.
>
> If I had run that update query with a COMMIT after every 500 rows, i am
> locking only 500 hundred rows and the other user can update the
> unlocked data.
>
> So wouldn't second option be a better choice.
> I know that commit only after a transaction is the best choice, its
> just that i wanted to have a clear solution in case of the above issue.
>
> Thanks
> Pradeep
>
>
>
> William Robertson wrote:
> > Pradeep wrote:
> > > Expert-one-on-one mentions that we should commit only after the
> > > transaction completes, otherwise it takes more time and generates more
> > > redo. I agree to it. But suppose if my transaction takes a few minutes
> > > to complete, i will be holding locks for that time and other people
> > > will be reading the history data. So isn't this the disadvantage of
> > > commiting after transaction??
> > >
> > > thanks
> > >
> > > Pradeep
> >
> > Well, a commit ends a transaction by definition anyway, but I don't
> > think that's what you mean.
> >
> > The other things you mention seem like good things to me, so I don't
> > really see much of a disadvantage. If your transaction fails or you
> > just change your mind you can roll back and nobody else has read the
> > wrong data.
>
Imagine that it is a banking application and you are adding interest to
savings accounts. You either want to do all the savings accounts or none of
them. So you use a transaction not commit every 500 rows. If you commit
every 500 rows then people can see the changes before you are done and not
all the changes, but some of them. So you would have created a bug. Bad
idea.
Jim
Received on Fri Nov 25 2005 - 08:15:38 CST
