Using NOLOCK to avoid dead-locks

I want to talk about NOLOCK. NOLOCK is a SQL Server T-SQL hint which you can give to the compiler in your SQL to tell it that it needn’t bother locking the records it’s going to read.

NOLOCK is like dealing with a hangover by drinking more alcohol. It’s fixes the immediate problems, and works well in the short-term. However, it’s not a long-term solution, and the more you rely on it, the worse your problems will be.When you use NOLOCK you ask SQL Server not to use the build in locking system. You tell it not to worry about un-commited changes, not to wait for anything. For active databases, this is a big performance increase, and enticingly can reduce your chances of getting dead-locks. On the flip side, your data may not always be 100% accurate, because you won’t be including the results of outstanding, uncommitted transactions.

This doesn’t sound very bad, but sometimes it is, because transactions sometimes do more than just update/create the row. If a transaction forces a page split, then it’s possible that your NOLOCK statement may miss out rows, or count them twice.

It’s not that NOLOCK is always a terrible, terrible thing that should be banished (like @@identity was). There’s a time and a place for it. However, if you’re using it to duct-tape over your badly designed database structure to avoid dead-locks, you need to think again. That’s probably a sign that you need to revisit your indexes, make some more, overlap some clustered keys etc. But, to avoid dead-locks when performing a one-off SQL command for some pointy-haired report – that’s probably OK. Likewise if your database is a very read-heavy repository of data that is rarely updated, some NOLOCKs might be appropriate.

You can use NOLOCK in SELECT statements like so:

SELECT * FROM your_table WITH (NOLOCK)

or in JOINs:

JOIN another_table WITH (NOLOCK) ON your_table.ID = another_table.realtedID

You need to make a judgement call on whether you should be using NOLOCK or not, what your attitude to danger is, and your aversion to incorrect data. There are plenty of other people‘s experiences you can call on to help you, but only you know what’s right for the situation you’re in.