Stephen McMahon (sl*******@gmai l.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****@sommarsk og.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
