What is purpose of using NOLOCK on a SELECT statement?

sql select nolock
rstuppi picture rstuppi · Mar 8, 2013 · Viewed 37k times · Source

Can anyone suggest a good reason to issue a NOLOCK on a SELECT statement?

I am re-factoring some stored procedures that are littered with them and it is my understanding that a NOLOCK on a SELECT statement is all but useless.

Answer

Just Aguy picture Just Aguy · Mar 9, 2013

I see some bad info flying around already. WITH(NOLOCK) on a select is not a performance tool to be used wherever you need speed. Read this article it dumps on the answer provided by eSamuel because that IS what most people think, as you can see by the votes. People who don't know but regurgitate what they've heard as how it should be done. Learn to optimize your data and queries first. The NOLOCK hint should only be used in systems where you are consistently dealing with records being deadlocked or another issue that NOLOCK provides a solution for.

I work at a financial institution developing in an OLTP system which processes numerous transactions a minute and provides real-time reporting capabilities to clients and customers. These people perform reads on our fresh data all day long and if we don't do report queries using NOLOCK it's only a matter of time before a deadlock occurs.

Do your due diligence and read from reputable, professional sources before jumping on NOLOCK as the holy graile to SQL query speed because there's much more to it.

Related questions

with(nolock) or (nolock) - Is there a difference?

Everything is based on the assumption that with(nolock) is entirely appropriate for the situtation. There are already plenty of questions out there debating whether or not to use with(nolock). I've looked around and haven't been able to find …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
SQL SELECT WHERE field contains words

I need a select which would return results like this: SELECT * FROM MyTable WHERE Column1 CONTAINS 'word1 word2 word3' And I need all results, i.e. this includes strings with 'word2 word3 word1' or 'word1 word3 word2' …

Read More