What is NOLOCK?

NOLOCK is a table hint for SQL Server that tells the engine to not take locks and ignore any locks when querying that table. This is equivalent to the READUNCOMMITTED table hint.

From MSDN:

Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions. This may generate errors for your transaction, present users with data that was never committed, or cause users to see records twice (or not at all). For more information about dirty reads, nonrepeatable reads, and phantom reads, see Concurrency Effects.

So you might be thinking that this NOLOCK thing is great and will solve all your performance problems…

There’s a Trade-off…

What’s the cost of using the NOLOCK hint or setting the Isolation Level to READ UNCOMMITTED everywhere? Well, you cannot guarantee the accuracy of your queries anymore. If the queries can read data in flight, you might read data that never gets committed. You might read data twice. You might read data that’s in the process of being updated or deleted. There’s a lot of mights in there. You need to ask yourself and the person that depends on this query, do they value accuracy or speed.

NOLOCK is a Last Resort

Many of the problems cause by locking and blocking, which NOLOCK gets around, can be solved by basic performance tuning. Limiting the data you are working with by using appropriate WHERE clauses is a good first step. SQL Server by default tries to lock data at the row level and only escalates to the table level if it determines it would be cheaper to do so. It might escalate quicker than you think though. If  two queries are operating on different rows in the same table, they will not block each other unless escalation has occurred. Note that I mention rows, not columns. If two queries are making changes to different columns in the same row, one will block the other until done. Adding appropriate indexes or modifying existing indexes would be a second step. These can speed up readers which let writers in quicker.

Another alternative to using NOLOCK is to use some level of row versioning within SQL Server such as READ_COMMITTED_SNAPSHOT and / or ALLOW_SNAPSHOT_ISOLATION. This allows more concurrency at the cost of increased tempdb usage. It does this by storing old version(s) of a row in tempdb until it is no longer needed. If you’re doing a quick read with no other connection trying to alter that data, then the lifespan in tempdb is very short. If more concurrent operations happen on that row, the lifespan can grow. Before implementing either of these levels, be sure to have a good understanding of the trade-offs of row versioning as it might cause more problems that it solves.

The NOLOCK Resort

Earlier I said to use NOLOCK as a last resort… in transactional workloads. NOLOCK might make a good fit for when you’re reporting out of your transactional database. Reporting is better done from a reporting copy of the data. Sometimes we don’t have that luxury and reporting must be done against the live data. In most cases, we want the transactional workload to take priority over the reports. Telling our reports not to take locks allows the transactions to continue unhindered by locks, though they still might be fighting for CPU and I/O resources. The same rules apply about speed vs accuracy, but usually reports don’t have to be to the second or penny accurate. In this case, NOLOCK may make sense. Just make sure your report consumers are aware that there might be a margin of error.

Further Reading:

Kendra Little (blog | twitter) made up a poster that helps visualize the different isolation levels. She also put together a great list of links about NOLOCK and the other isolation levels.

Paul Randal (blog | twitter) has some great content on locking. He also does a good job of explaining locking & blocking in this video.

Kimberly Tripp (blog | twitter) has more info about indexing.