What is the purpose of ROWLOCK on Delete and when should I use it?

Question

Ex)

When should I use this statement:

DELETE TOP (@count) FROM ProductInfo WITH (ROWLOCK) WHERE ProductId = @productId_for_del; 

And when should be just doing:

DELETE TOP (@count) FROM ProductInfo WHERE ProductId = @productId_for_del; 

Answer 1

The with (rowlock) is a hint that instructs the database that it should keep locks on a row scope. That means that the database will avoid escalating locks to block or table scope.

You use the hint when only a single or only a few rows will be affected by the query, to keep the lock from locking rows that will not be deleted by the query. That will let another query read unrelated rows at the same time instead of having to wait for the delete to complete.

If you use it on a query that will delete a lot of rows, it may degrade the performance as the database will try to avoid escalating the locks to a larger scope, even if it would have been more efficient.

Normally you shouldn't need to add such hints to a query, because the database knows what kind of lock to use. It's only in situations where you get performance problems because the database made the wrong decision, that you should add such hints to a query.

Answer 2

Rowlock is a query hint that should be used with caution (as is all query hints).

Omitting it will likely still result in the exact same behaviour and providing it will not guarantee that it will only use a rowlock, it is only a hint afterall. If you do not have a very in depth knowledge of lock contention chances are that the optimizer will pick the best possible locking strategy, and these things are usually best left to the database engine to decide.

ROWLOCK means that SQL will lock only the affected row, and not the entire table or the page in the table where the data is stored when performing the delete. This will only affect other people reading from the table at the same time as your delete is running.

If a table lock is used it will cause all queries to the table to wait until your delete has completed, with a row lock only selects reading the specific rows will be made to wait.

Deleting top N where N is a number of rows will most likely lock the table in any case.