469,609 Members | 1,705 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,609 developers. It's quick & easy.

When to use the NOLOCK hint.

Background:
I am currently working on a mission critical web based application
that is accessed 24 hours a day by users from just about every time
zone. We use MS SQL Server as our database and we have lots of
problems with time-outs. We used to have lots of problems with locks
until my management decided that we would use the WITH (NOLOCK) hint
on EVERY select statement and WITH (ROWLOCK) on EVERY update
statement. I have argued since the beginning that the NOLOCK hint
should be the exception and not the rule. Meanwhile we continue to
have problems related to time-outs.

Problem:
I'm the one that they call when there are time-out errors.

I am a programmer first and a DBA when I have to be. I'd really like
to hear from some of you who are the opposite. I realize that there
are many factors that contribute to slow response from a database
server (indexes, RAM, disk speed, etc.), but what I really need to
hear from an expert is whether or not using NOLOCK on **EVERY** query
in a 30GB database that has 344 tables is a bad idea.

Thanks in advance,

Stephen McMahon
sm******@gmail.com
Jul 20 '05 #1
2 25054
Stephen,

With NOLOCK hint, the transaction isolation level for the SELECT statement
is READ UNCOMMITTED. This means that the query may see dirty and
inconsistent data. So in general this is not a good idea to apply as a
rule. Even if this dirty read behavior is OK for your mission critical web
based application, NOLOCK scan can cause 601 error which will terminate the
query due to data movement as a result of lack of locking protection.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
"Stephen McMahon" <sl*******@gmail.com> wrote in message
news:ab*************************@posting.google.co m...
Background:
I am currently working on a mission critical web based application
that is accessed 24 hours a day by users from just about every time
zone. We use MS SQL Server as our database and we have lots of
problems with time-outs. We used to have lots of problems with locks
until my management decided that we would use the WITH (NOLOCK) hint
on EVERY select statement and WITH (ROWLOCK) on EVERY update
statement. I have argued since the beginning that the NOLOCK hint
should be the exception and not the rule. Meanwhile we continue to
have problems related to time-outs.

Problem:
I'm the one that they call when there are time-out errors.

I am a programmer first and a DBA when I have to be. I'd really like
to hear from some of you who are the opposite. I realize that there
are many factors that contribute to slow response from a database
server (indexes, RAM, disk speed, etc.), but what I really need to
hear from an expert is whether or not using NOLOCK on **EVERY** query
in a 30GB database that has 344 tables is a bad idea.

Thanks in advance,

Stephen McMahon
sm******@gmail.com

Jul 20 '05 #2
Stephen McMahon (sl*******@gmail.com) writes:
I am currently working on a mission critical web based application
that is accessed 24 hours a day by users from just about every time
zone. We use MS SQL Server as our database and we have lots of
problems with time-outs. We used to have lots of problems with locks
until my management decided that we would use the WITH (NOLOCK) hint
on EVERY select statement and WITH (ROWLOCK) on EVERY update
statement. I have argued since the beginning that the NOLOCK hint
should be the exception and not the rule. Meanwhile we continue to
have problems related to time-outs.

Problem:
I'm the one that they call when there are time-out errors.

I am a programmer first and a DBA when I have to be. I'd really like
to hear from some of you who are the opposite. I realize that there
are many factors that contribute to slow response from a database
server (indexes, RAM, disk speed, etc.), but what I really need to
hear from an expert is whether or not using NOLOCK on **EVERY** query
in a 30GB database that has 344 tables is a bad idea.


Always using NOLOCK does certainly come with some risks. But what the
risks are depends on how the system is coded. And what purpose the
system has.

But say that you system maintain accounts, and there is a process that
performs some reallocation of assets, and it happens that new assets
are inserted before old are deleted. All this happens in one transaction.
Now we have a stored procedure that is to present the total value of
that account, which is called, because the account owner logs through
the web to look at his account. The procedure runs with NOLOCK, and the
account owner now finds that his assets has doubled. (And it is really
better if old are deleted first. The account owner now finds that he
has nothing!)

For a system which is more OLAP than OLTP, it may be more permissible to
use NOLOCK, since the users may be looking at overall trends and not
individual data points.

Then there are other ugly things that can happen with NOLOCK if you
come in the middle of a page split, or a page being moved because
a colunm in the clustered index has been updated. That can lead to
the NOLOCK query failing with a seemingly serious error.

To take this path requires very thorough knowledge of the ramifications.
What yuo really should do is trace slow queries, improve indexing etc.
If you have bad query plans, you still have bad query plans with NOLOCK.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by AKS | last post: by
6 posts views Thread by Mark P | last post: by
6 posts views Thread by laurenq uantrell | last post: by
2 posts views Thread by Roger Gordon | last post: by
3 posts views Thread by Rycho | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.