NOLOCK may not be doing what you think it does

Posted on by

When working with developers, I’ve noticed that some have a tendency to put WITH (NOLOCK) on every table in a SELECT query. When I inquire about this, the typical response I get is usually along the lines of “I don’t want my query to create any locks.” or “It helps my query run faster.”  Well…that’s not exactly what NOLOCK is for. Always putting WITH (NOLOCK) on every table in your SELECT query is a really bad practice.  To quote the great DBA sage Inigo Montoya, “You keep using that query hint. I do not think it means what you think it means.”

What does NOLOCK do?

Adding NOLOCK to your query is the same as setting the Transaction Isolation Level to READ UNCOMMITTED, which Microsoft defines as:

Specifies that statements can read rows that have been modified by other transactions but not yet committed.

Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction.

Basically, what this means is the data returned to the SELECT statement may or may not actually exist in the database. When using the NOLOCK hint in a SELECT statement, SQL Server will not put read locks in place, and any INSERT/UPDATE/DELETE statements that may also be occurring are allowed to modify the data you are trying to read.  SQL Sever then returns the data to the query even though those transactions may not have been committed.  This is known as a Dirty Read, and in some cases it may cause a query to return the same row multiple times or even skip rows.

When should I use NOLOCK?

There are some advantages to using NOLOCK, the main one being that it helps avoid deadlocks with other queries running against the same data.  However I would point out that if you are getting frequent deadlocks with a query, this is an indication that using NOLOCK is going to return incorrect results.

There may be a case when your query doesn’t necessarily need to return precise figures, and can tolerate some inconsistencies (think of a query that returns sales trends over the last 6 months, precise up to the minute data may not be necessary.)  If you can deal with “not 100{9aaef31dc5e5b7f5049f8f082b0a92ca8701bd6baf49d5704ff4650a929dabe3} accurate” results from your query, then NOLOCK may be OK.  But if you consistently need accurate results, do not use NOLOCK.

NOLOCK can also be useful when querying data that doesn’t change often, or in the case of an OLAP database where data writing only occurs during a scheduled time. (Ex: A Data Warehouse that only runs INSERT/UPDATE/DELETE statements at night, and users do reporting during the day)

But NOLOCK makes my query faster!

It makes your query faster because it is ignoring the safeguards put in place to ensure that your query is returning accurate data.  The data returned to your query may not actually exist in the database depending on the final outcome of any INSERT/UPDATE/DELETE statements that may also be taking place.  What is the point in having a faster query if it doesn’t return the correct results?

The bottom line…

There are not many cases where you can justify using NOLOCK in an OLTP database, and you certainly shouldn’t be using it on every table in all of your SELECT queries. If you need accurate and precise results consistently returned from your query, NOLOCK is not really an option.  If you are using NOLOCK as a “turbo boost” to make your queries faster, stop it.  If you are using NOLOCK to avoid a problem with blocking on your query, you need to address the underlying blocking issue instead of trying to use NOLOCK as a “magic button”.

Resources:

For a more detailed explanation of NOLOCK, see the below resources:

(Visited 3,784 times, 1 visits today)