Re: Commit after transacton is completed

Pradeep wrote:
> 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.

As Sybrand said, you need to conside a logical transaction. In this case the updating of the entire table would be a logical transaction.

To commit every 500 rows (don't do this!!) you will need to do something like

for row in (select foo from bar)
  update foo set bar = ??
  count := count+1;
  if count > 500 then
    commit;
    count := 0
  end if;
end loop;

This will be much less efficient than a plain old

update foo set bar=??
where ...

commit;

What are you going to if the update fails half way through in you commit every 500 rows? Then you have a partially completed transaction that your are going to need to manually fix. It may be easy to fix in the simple example of updating the entire table, but in a more real world general case it would be much more tricky! Received on Fri Nov 25 2005 - 05:15:27 CST