How not to lock your database server for large deletes.
by Alec Horn on May.14, 2009, under Uncategorized
So one of my coworkers recently brought down a website accidently since someone broke the session cleaning job. So, he of course runs the procedure to delete all of the old data, and kaboom it places a lock on the table while it deletes a few hundred thousand rows.
Cute except that without that table being able to be accessed no requests would technically go through. The workaround? Simply do small units of work at a time:
WHILE 1=1 BEGIN DELETE TOP (1000) FROM [db].[dbo].[table] WHERE Expires < @now; IF @@rowcount = 0 BREAK; END
2 comments for this entry:
May 15th, 2009 on 7:24 AM
This solution is useful as long as it’s used only in a maintenance capacity, it would only work if it is not transaction safe.
Locks are placed on the table for the duration of the transaction affecting the table. as such, if this were contained within a transaction (either issued by the calling app or wrapping the abov statement in BEGIN TRAN / COMMIT TRAN) then a lock will stll be placed on [Table]. In fact, it would maintain that lock significantly longer thn just issuing the straight-up delete, as the delete will have to re-run through the Command Parser, query optimizer (though cached execution plan should work for the parameterized query), and buffers for each pass.
You could use NOLOCK on the table, but this will result in the posibility of dirty reads by anything that reads from this table while the delete is taking place, and is generally not recommended.
June 11th, 2009 on 12:25 PM
Thanks for the useful info. It’s so interesting