Updated with an author's note
When this article was first published, it produced some lively debate.
It was believed by some that the article misled readers into thinking
that NOLOCK should be a tool for performance tuning, which it is not.
There is a divided opinion on the appropriate use of NOLOCK, and
therefore I am adding this foreword to my article in an effort to
clarify matters.
NOLOCK is a query optimizer hint. It has advantages, and disadvantages,
and it potentially breaks one of the fundamental rules of databases â€
data integrity through the use of a locking mechanism. In a nutshell,
NOLOCK does not take locks on the data it reads. This provides benefits
for concurrency and performance, but data integrity suffers.
I can't speak for Microsoft or the MVP's, but if Microsoft offers an
ISOLATION LEVEL which does the same thing as NOLOCK, then there must be
an appropriate place for the use of NOLOCK. We've discussed the
advantages of NOLOCK, so let's take a look at some of the
disadvantages.
Firstly, when reading with NOLOCK you are reading uncommitted data.
This means the data has not been committed to the database, and could
be rolled back (undone) after you have read it. So, you may find your
application is processing invalid data. This is not so much of a
problem with Name and Address data, but is particularity problematic
with Accounts, Finance and Sales data. This is where your data
integrity would suffer.
Secondly, as noted by one of the SQLServerCentral.com forum posters,
Itzik Ben-Gan demonstrated to the UK SQL Server User Group that NOLOCK
can return duplicate rows when reading data. To quote the forum
posting: "Tony Rogerson's blog has code which demonstrates this.
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/16/1345.aspx
".
This article was intended as a clinical approach to the effect
of NOLOCK on performance, without advocating you use NOLOCK in an
attempt to speed up your queries. If you do decide to use it that way,
I would like you to know the risks.
That's all I have to add, folks. I hope you read on and enjoy the article!
Cheers, Wayne Fillis
鿴ȫ
xxdxxdxxd
:2007.03.26 16:43
:::
(
SQL Server & ADO
)
::Ķ:(118)
::
(0)
::
(0)