Systems Engineering and RDBMS

Do not use NOLOCK hint without knowing what it does

Posted by decipherinfosys on July 9, 2009

We have seen this in a majority of our consulting engagements related to SQL Server.  The developers are very trigger happy when it comes to blocking locks issue and their first answer to resolving it is to use the NOLOCK locking hint without even realizing the issues that they are going to create or the issues that they need to be aware of when using this locking hint.  A lot of it has to do with how things used to work prior to SQL Server 2005.  Writers (DML statements) used to block readers (Selects or implicit selects) regardless of the isolation level that you chose to go with.  So, developers used to working on Oracle were taken aback by this because their entire application was written with multiversion concurrency in mind.  So, instead of supporting SQL Server the right way, in comes the NOLOCK hint.

So, what does NOLOCK locking hint do really?  It is the same as the READ UNCOMMITTED transaction isolation level with the difference that this does it at the statement level rather than the session level.  If you are on version SQL Server 2005 or 2008, look into the snapshot isolation levels – you can read more on it here.  Using NOLOCK hint is fraught with dangers of ending up corrupting the data in the system (when you read uncommitted data and make decisions based on it and the other session rolls back the transaction) or other consistency issues like missing previously committed rows.

There are a lot of very good posts on why not to use the NOLOCK hint blindly (Yes – when we say blindly, it implies that there are some scenarios where it is fine to use it – when accuracy is not of prime importance and you are looking for just some high level analysis of the data – though beginning SQL Server 2005, there really is no need to use it).  Here are some of the posts for your reference and the next time someone tells you to use NOLOCK, point them to these posts and ask them to read them:

SQLCAT post over here.

MVP Tony Rogerson’s post over here.

2 Responses to “Do not use NOLOCK hint without knowing what it does”

  1. avani said

    Good stuff

  2. Rajesh said

    Very Useful!!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: