When is it appropriate to use NOLOCK?

Question

I am having timeout issues and deadlocks from time to time with some long running queries.

I'm wondering when is it most appropriate to use NOLOCK and where?

Do I use it on the updates & inserts? or reads?

Answer 1

Note that you can specify nolock on a per table basis.

I typically used nolock in complex SELECT queries, but only for the little lookup tables that almost never changed, and for display-only data. You know the tables that list the prices for the current half year, or lookups of ids to strings etc. Stuff that only changes with major updates after which the servers are usually restarted routinely anyway.

This improved performance significantly, reduced the chance of deadlock in the busiest times, and more importantly it was really noticable during the worst case moments for queries that touched a lot of tables (which is logical, they have to obtain less locks, and those sidetables are often used nearly everywhere, often decreasing from 7-8 to 4 tables that need to be locked)

But be very careful adding it, don't rush it, and don't do it routinely. It won't hurt when used properly, but it will hurt horribly when used improperly.

Don't use it for highly critical stuff, stuff that calculates etc, because it will get inconsistent, anything that leads to a write sooner or later.

Another such optimization is ROWLOCK, which only locks on row level. This is mainly useful when updating (or deleting in) tables where the rows are not related to eachother, like tables where you only put in log records (and the order in which they are inserted doesn't matter). If you have a scheme that somewhere in the end of an transaction a log record is written to some table, this can speed up considerably too.

If your database has a relatively low percentage writes it might not be worth it. I had a read:write ratio of under 2:1.

Some URLs I saved when working on this:

http://www.developerfusion.com/article/1688/sql-server-locks/4/

Answer 2

There are four transaction isolation levels in SQL Server:

1. READ UNCOMMITTED
2. READ COMMITTED
3. REPEATABLE READ
4. SERIALIZABLE

For the tables it's applied to, NOLOCK is the equivalent of "read uncommitted". That means you can see rows from transactions that might be rolled back in the future, and many other strange results.

Still, nolock works very well in practice. Especially for read-only queries where displaying slightly wrong data is not the end of the world, like business reports. I'd avoid it near updates or inserts, or generally anywhere near decision making code, especially if it involves invoices.

As an alternative to nolock, consider "read committed snapshot", which is meant for databases with heavy read and less write activity. You can turn it on with:

ALTER DATABASE YourDb SET READ_COMMITTED_SNAPSHOT ON;

It is available for SQL Server 2005 and higher. This is how Oracle works by default, and it's what stackoverflow itself uses. There's even a coding horror blog entry about it.

P.S. Long running queries and deadlocks can also indicate SQL Server is working with wrong assumptions. Check if your statistics or indexes are out of date:

SELECT 
    object_name = Object_Name(ind.object_id),
    IndexName = ind.name,
    StatisticsDate = STATS_DATE(ind.object_id, ind.index_id)
FROM SYS.INDEXES ind
order by STATS_DATE(ind.object_id, ind.index_id) desc

Statistics should be updated in a weekly maintenance plan.