Re: Commit after transacton is completed

"Pradeep" <agarwalp_at_eeism.com> 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.

True.

> 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.

Is that good or bad? The reason you take locks is to prevent data corruption. Thus not keeping those locks when they are necessary causes data corruption.

>
> So wouldn't second option be a better choice.

Some times it is, but those times are the exception rather than the rule.

> 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.

1. The broken-up update most either be of such a nature that it doesn't matter if a row is updated twice, or it most be self documenting within that physical transaction. This is necessary so that a aborted operation can be restarted.
2. Every other query/DML operating in the database, during the duration of your piecemeal update, must be indifferent to update-status of the column being updated.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB