Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am new to SQL Server and am not sure about how the locking mechanism works here. Please help

Issue: Need to delete close to 5 mil records in a table. A website pulls data from that table quite frequently.

My understanding is: delete would lock only the row hence this shouldn't affect the website. Is that right? Then, what would happen if the website is trying to pull the record that is being deleted? I know I sound very amateurish but I badly need to understand these basics.

Could one of the gurus please share a link where I can read up about the various kinds of locks in SQL Server as well

share|improve this question
    
Apart from the guide on locking basics I would advise you to , if at all possible, delete the records in smaller batches. This way you keep locking contention to a minimum but also you keep your transaction log from exploding. –  Edward Dortland Jan 7 at 12:12
1  
Best article Break large delete operations into chunks by Aaron Bertrand - goes into more details. –  Kin Jan 7 at 14:49
    
@Kin I thought Aaron had written about this, but couldn't find the article. Thanks for digging it up. –  vonPryz Jan 7 at 18:16
add comment

1 Answer

up vote 1 down vote accepted

Sql Server's locks are described in good detail in the product documentation. A lock might or might not be escalated to page/table level, the behavior is documented, again, in Technet.

A fairly common tactic for deleting large amount of rows is to split up the delete in batches. Instead of deleting all the 5M rows at once like so,

DELETE FROM MyTable WHERE foo='bar'

rather try and delete data in, say, 100 * 50k row batches like so,

WHILE (@rows > 0)
BEGIN
    DELETE TOP (50000) FROM MyTable
    WHERE foo='bar'    
    SET @rows = @@ROWCOUNT
END

By splitting the delete in batches, you should lock way less data than with a all-encompassing single-pass delete. YMMV, depending on the selectivity and other activity in your table.

In addition, keep in mind that delete is logged and you might get a nasty surprise with your transaction log size.

This article has nice an overview about how to batch delete data and mind the logs too.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.