The Danger of using NOLOCK in SQL Server queries

This is a topic most of us never consider carefully when using nolock in our select queries. This week, one of the database administrators came out with a clear note to really discuss this topic with many developers at my work and l think its good to share some of those discussions on here.  Most developers will use the with (NOLOCK) hint when selecting from tables to make the queries run faster without acquiring locks on the underlying object. But the reality is, this hint should be a last resort, after all else has failed, and only then when the possible scenarios have been worked through, the risks understood and accepted by your business. Here’s why: you may not count all rows, you may in fact count rows twice and you won’t know which, if either. There is no accuracy, no consistency when you use “WITH (NOLOCK)”.

There are well known issues that result from using NOLOCK, such as:

• Dirty Reads – this occurs when updates are done, so the data you select could be different.
• Nonrepeatable Reads – this occurs when you need to read the data more than once and the data changes during that process
• Phantom Reads – occurs where data is inserted or deleted and the transaction is rolled back. So for the insert you will get more records and for the delete you will get less records.

However, it is less well understood that this means rows can be double counted or not counted at all, resulting in incorrect counts being returned. So the question is will your business be willing to accept $99 intead of $100. Do you  want to report 2000 customer count instead of 2010 customer count?

These were some of the questions raised in the discussion and l think its good to rethink using the with (nolock) any time the need arises.

Another point is If isolation level is set to READ UNCOMMITTED in your procedure  – you don’t need to also use NOLOCK. So my recommendation is if you have lot of tables in your query and want to add with nolock then it is better to just put at the top of the query  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to avoid adding with nolock to all the tables.

These are very important things to know about the with nolock when using it. Just in case you haven’t used the with (nolock) yet, then the quey below shows how to use it.

SELECT NAME, AGE

FROM CUSTOMER WITH (NOLOCK)

When executing this query, sql server does not have to acquire lock on the table customer before retrieving the name and age so during the query execution, anything that happened such as updates or new inserts into the customer table will not be reflected in your result set.

Hope this helps.

One of my biggest role models in Life, Nelson Mandela died  this week. May Madiba rest in peace. He did good to our world and l am going to watch the  movie about his life, Mandela Long walk to freedom, which is coming out to theaters on Christmas day. Go watch and pay tribute to one of the greatest freedom fighters our generation has the opportunity to know.