A blog about SQL and MS SQL Server

If you’ve been around SQL Server and SQL Server development for a while, you will undoubtedly have encountered the NOLOCK hint.

SQL Server allows you to modify how the optimizer processes a query in a wide variety of ways. One of the things you can change is locking behavior – you can modify behavior in an individual query by applying a lock hint, like this:

SELECT *
FROM dbo.Table WITH (NOLOCK)

This is extremely useful in some cases, but as with all things, it is open to misuse, and the NOLOCK hint is widely, widely misused.

Some background is in order. In an RDBMS, we have to balance all things (hence the title of this blog), and one of the things we have to balance is the line between consistency and concurrency. We could queue all database requests, forcing them to proceed one after another, which would mean that our database would never wind up in an inconsistent state. But this would cause insurmountable concurrency issues, reducing our multi-thousand dollar database server to a single-user machine. At the other end of the scale, we could allow anybody to do anything at any time. This would obviously increase concurrency to its greatest possible value, but would result in chaos. You wouldn’t have a database: you’d have a mess.

To address this, the ANSI committee came up with 4 isolation levels. These are, in increasing order of consistency: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. These are implemented by use of locks. A process updating a row locks it while the update occurs, a process inserting new data inserts it with locks on it and only releases those locks once the entire insert is complete, etc. Otherwise, you never know what you’re getting – the new data or the old data, just some of the data or all of it.

But locks introduce another issue, which is known as blocking. A process that wants to read all last nights orders must wait until the process updating those orders has finished doing so. The reading process is said to be blocking on the updating one, the updater is blocking the reader.

This sounds bad. But blocking is a normal and expected part of any relational database that sees any degree of multiuser activity. As long as blocks are of short duration, the database will handle them transparently, and nobody will be any the wiser.

The problem in SQL Server 2000 (and in DB2) is that databases that are abused and that must serve both long-running read operations in addition to everything an OLTP system has to do (insert, updated, delete) will start to suffer extensive blocking. A long read will block updates and deletes, and depending on other circumstances, can block inserts as well. Updates block readers, which block other updates, inserts, deletes… this is all part of how a proper relational database is designed.

Locks and blocks prevent inconsistency, but most procedural programmers don’t understand database consistency anyway, and so to them a lock is a block is bad. Anything that gets around that is, to those lacking understanding, a good thing.

In both SQL Server and DB2 (and some other products with which I have not worked), a common problem is readers blocking writers. When you have a database that has been poorly designed used for both OLTP and reporting, you tend to get heavy reporting queries holding up OLTP activity: the INSERT, UPDATE, DELETE statements sit and wait until the long-running read finishes with the data. The uninformed programmer, seeing the number of processes blocked, hunts on the web and finds the magical NOLOCK query hint.

What NOLOCK does is move a particular query to the READ UNCOMMITTED isolation level. The query does not set any shared locks¹, and it doesn’t honour any exclusive locks held by other processes. On a system suffering from poor design and resulting long-running reporting queries holding up normal business activity, this seems like a godsend: just throw NOLOCK on all queries, and the blocking problem magically goes away.

Or does it? What NOLOCK does is move the problem, from somewhere obvious to somewhere obscure. It doesn’t make much difference to data modification processes – those operations have their isolation levels escalated automatically anyway, because UPDATEs that don’t honour locks that other UPDATEs set makes the whole idea of concurrency a complete mockery. Where it makes a difference is in the read operation: because the read doesn’t honour exclusive locks, it will read another transaction’s uncommitted data.

That should scare you.

It will read data that could be rolled back. Allowing such reads violates both the C and the I of ACID (Atomic, Consistent, Isolated, Durable) – data is not consistent during such a read, and an uncommitted transaction is not isolated.

I have encountered a number of problems with NOLOCK (and with its DB2 counterpart, WITH UR) over the years. In SQL Server, it can result in an existing row either being missed or (more commonly) read twice if there is a data movement routine going on – say a page split, or a DBCC REINDEX – this will often manifest as a primary key violation in a routine that, when you look at it, couldn’t possibly be inserting values that already exist.

But if you don’t get immediate, obvious, in-your-face errors like primary key violations, the data rot that NOLOCK can induce is subtle and nearly impossible to track down. Say you have a summary routine that runs with NOLOCK. Say it reads a row of financial data that is later rolled back and adds the value there to the summary for the day, or hour, or whatever. That aggregate value is now incorrect, and anything derived from it is incorrect. And even a guy like me, who has a lot of experience and skill tracking down things like this, I will never be able to prove that this was caused by reading uncommitted data. The best I will be able to do is to say, well, on this day the summary was out.

NOLOCK is dangerous. The only people who should use it are people who understand the implications, and it should almost never be used in any sort of saved routine (a stored procedure, for example).

1. This is generally true, but not strictly true: any read, for example, running at READ UNCOMMITTED will still set a schema stability lock – you won’t be able to ALTER the table while the read operation is running.