BEGIN TRAN and WHILE LOOP advice

Question

Post reply

BEGIN TRAN and WHILE LOOP advice

sql_lock

SSCrazy Eights

Points: 9373
More actions
May 28, 2009 at 2:29 am

#384670

Hi all
What wondering if someone could give me some advice.
We have a invoice routine that updates over 300000 rows, this causes major locking on the table which effects all user (150-200).
I have been reading up on batching the updates so that other users are not affect by the update.
Would a BEGIN TRAN within a WHILE LOOP help with this?
WHILE xxx=xxxx
BEGIN
BEGIN TRANSACTION
UPDATE TOP (500) tbInvoice
SET Invoiced = 1
WHERE Invoiced = 0 AND InvoicedMonth = '05' ...
COMMIT
END
Thanks
JL

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Tejas Shah Old Hand Points: 308 More actions · Answer 1

Hi,

Updates in batch will lock whole table, if half of the data from table is being updated.

its better to use update one by one or in small batch, I preferred one by one update on frequently use table. It will lock one row at a time, plus if any record has an issue, it will continue update with next one. You can also make an entry in any table (log purpose), for which record, its get an error and we can analyze it.

So its better to update it one by one.

Thanks,

Tejas Shah

http://www.SQLYoga.com

Tejas Shah

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 2

Hi

Thanks for the reply.

If I am updating 3-4 tables using the TOP function, will added the BEGIN TRANSACTION outside the WHILE Loop cause an issue?

Rgds

JL

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 3

Moving the transaction outside of the loop will result in the appropriate locks being held for the duration of the loop.

The trick is (in my opinion) to have the transaction open for the shortest possible time, hence I would have it in the loop and then close it again as soon as the update is done.

Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 4

if its SQL 2005 and above you can use snapshot isolation level to avoid locking as committing so many times inside the loop will have the performance hit .

The best practice says commit only once when the batch completes .So the begin tran should be outermost and the while loop should be inside it .you can also try using the hint with rowlock

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)