Using SQL Server Table Hints: NOLOCK

Hey everybody, Geon here again with another exciting instalment of “SQL Something”.

Today we are going to be looking at querying using NOLOCK.

When I first started working where I am, I used to run my SELECT queries ‘normally’, that is, without using the NOLOCK feature. I remember one day I put a large query to run (one that normally took about 30 minutes) with the mind-set that I’ll “check back on it in a bit”. Ten minutes later one of our sys admins pointing out that one of our applications started throwing a bunch of errors, all database related. This app almost continuously wrote data to its database. Suddenly, it could not insert anything into one of its database tables and was kicking up a massive fuss because of it.

You can probably guess why. That’s right: my query locked the table and was holding back everything in life for about 15 minutes until we determined that it was yours truly. From then on I was advised to use NOLOCK.

So for a while I’ve been using NOLOCK without really thinking about it. It really seemed to be a case of being exactly what it says on the tin, that is, it sounded like it ran SELECT queries without placing any locks and well, it also greatly sped up query time.

Recently I had decided to look into it in order to see what I really have been doing all this time and, to my surprise, I found out not only does it not do exactly as the name suggests, but in some situations it can also lead to some potentially dangerous or, at the very least, misleading results.

What exactly is NOLOCK?

NOLOCK is part of a group of items known as “Table Hints”. It is also known as READUNCOMMITTED. Table Hints are specified in the FROM clause in a SQL query and they change the default query optimizer behaviour for the specific table(s) referenced in the FROM clause. They can change the way locks are issued at query time, for example.

Simple Example:

SELECT Column1, Column2
FROM Table1 with (NOLOCK)
WHERE SomeCondition

With regard to the NOLOCK table hint specifically, it specifies that the SELECT query (NOLOCK can only be used with SELECT statements) will be run, well, with “no locks”, so that other users and applications can continue using and modifying your DB table without being blocked by your query. You’ll notice that I put “no locks” in quotes because it actually does apply what is known as a schema lock, which prevents any changes to a database’s schema while the query is being run; so that means no table dropping or column changes, etc. at query runtime for you.

This is good for apps (as they won’t crash due to inability to access a table) but it may not be so good for reporting.

The problem is that of “Dirty Reads”, where the query actually just pulls whatever data is in the buffer pool, regardless of whether it was committed or not. You essentially tell SQL Server that data consistency is not important and you can end up reading partially updated/inserted rows as you won’t be waiting for a process to finish its query. 

This can lead to the query displaying data that is in the process of being rolled back (i.e. data that never was actually written to the DB), data that is in the process of being inserted/updated, rows of actual data can be accidentally skipped and sometimes data may even be displayed twice.

Now before you think its all doom and gloom and you swear off NOLOCK, let me say this: From what I’ve read, NOLOCK still seems to be a somewhat decent solution when it comes to querying. Firstly of course it prevents deadlocking when attempting reads. It speeds up SELECT statements greatly as you don’t have to wait for locks placed by other queries to be released. Also if the table you are querying is only used for inserts rather than updates and deletes, then the problems with using NOLOCK are greatly reduced.

At the end of the day you’ll have to decide for yourself if the benefits of using NOLOCK in your production environment outweigh its cons. It is not something that should be taken lightly.

As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed.  :-)