sql server hint NOLOCK and ROWLOCK

Question

I'm currently investigating an annoying problem on my website. We're giving away prizes regularly on the website, but to enter the competition people have to login. So the website becomes a lot busy at times. I found that when a lot of people trying to login and register, I get heaps of error about deadlocks on UpdateUser, CheckPassword and GetUser functions then the server gets too busy and other requests start timing out.

When I look into the stored procedures, I found there's ROWLOCK used on 'UpdateUser'. Are those ROWLOCKs causing the deadlock? or only select would result a deadlock?

I was thinking about using NOLOCK for my situation, but after a little research, apparently it's not recommended...

Answer 1

As barry already explained, these two hints can complement each other, but they are typically used in different contexts and to solve different resource contention issues.

The WITH (NOLOCK) tells the server to employ READ UNCOMMITTED transaction isolation level, which means you are exposed to the risk of reading uncommitted ("dirty") rows which may be subsequently rolled back and thus have never existed. It does prevent classic deadlocks on reads, but at the expense of getting invalid data.

If there is a chance GetUser or CheckPassword operations may access the profile of the user being updated through UpdateUser operation, then WITH (NOLOCK) is not recommended to use.

The WITH (ROWLOCK) table hint can be used with either SELECT, INSERT, UPDATE, and DELETE statements, to instruct the server to only apply a Range-Lock(s) on the rows being modified or added, and to avoid escalating the lock to page or table level. The rest of rows are not locked and can be accessed by another query.

However, if the default transaction isolation level at the SQL Server is READ COMMITTED or more restrictive, and SNAPSHOT READS are not enabled, then an active INSERT, UPDATE, or DELETE transaction may still block the SELECT query, if lookup conditions match or overlap.

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.