Effect of NOLOCK hint in SELECT statements

sql sql-server locking
Bob Probst picture Bob Probst · Oct 16, 2008 · Viewed 338.9k times · Source

I guess the real question is:

If I don't care about dirty reads, will adding the with (NOLOCK) hint to a SELECT statement affect the performance of:

  1. the current SELECT statement
  2. other transactions against the given table

Example:

Select * 
from aTable with (NOLOCK)

Answer

tom.dietrich picture tom.dietrich · Oct 16, 2008

1) Yes, a select with NOLOCK will complete faster than a normal select.

2) Yes, a select with NOLOCK will allow other queries against the effected table to complete faster than a normal select.

Why would this be?

NOLOCK typically (depending on your DB engine) means give me your data, and I don't care what state it is in, and don't bother holding it still while you read from it. It is all at once faster, less resource-intensive, and very very dangerous.

You should be warned to never do an update from or perform anything system critical, or where absolute correctness is required using data that originated from a NOLOCK read. It is absolutely possible that this data contains rows that were deleted during the query's run or that have been deleted in other sessions that have yet to be finalized. It is possible that this data includes rows that have been partially updated. It is possible that this data contains records that violate foreign key constraints. It is possible that this data excludes rows that have been added to the table but have yet to be committed.

You really have no way to know what the state of the data is.

If you're trying to get things like a Row Count or other summary data where some margin of error is acceptable, then NOLOCK is a good way to boost performance for these queries and avoid having them negatively impact database performance.

Always use the NOLOCK hint with great caution and treat any data it returns suspiciously.

Related questions

How to find out what is locking my tables?

I have a SQL table that all of a sudden cannot return data unless I include with (nolock) on the end, which indicates some kind of lock left on my table. I've experimented a bit with sys.dm_tran_locks …

Read More
SQL Server - How to lock a table until a stored procedure finishes

I want to do this: create procedure A as lock table a -- do some stuff unrelated to a to prepare to update a -- update a unlock table a return table b Is something like that possible? Ultimately I …

Read More
What is (are) difference between NOLOCK and UNCOMMITTED

I use SQL Server 2012. I write two queries but what is a different between NOLOCK and UnCommitted ? SELECT lastname, firstname FROM HR.Employees with (READUNCOMMITTED) SELECT lastname, firstname FROM HR.Employees with (NoLock)

Read More