What is "with (nolock)" in SQL Server?

sql-server nolock
Andy White picture Andy White · Mar 26, 2009 · Viewed 672.5k times · Source

Can someone explain the implications of using with (nolock) on queries, when you should/shouldn't use it?

For example, if you have a banking application with high transaction rates and a lot of data in certain tables, in what types of queries would nolock be okay? Are there cases when you should always use it/never use it?

Answer

David M picture David M · Mar 26, 2009

WITH (NOLOCK) is the equivalent of using READ UNCOMMITED as a transaction isolation level. So, you stand the risk of reading an uncommitted row that is subsequently rolled back, i.e. data that never made it into the database. So, while it can prevent reads being deadlocked by other operations, it comes with a risk. In a banking application with high transaction rates, it's probably not going to be the right solution to whatever problem you're trying to solve with it IMHO.

Related questions

SQL Server NOLOCK and joins

Background: I have a performance-critical query I'd like to run and I don't care about dirty reads. My question is; If I'm using joins, do I have to specify the NOLOCK hint on those as well? For instance; is: SELECT * …

Read More
Is the NOLOCK (Sql Server hint) bad practice?

I'm in the business of making website and applications that are not mission critical -> eg. banking software, space flight, intensive care monitoring application, etc. You get the idea. So, with that massive disclaimer, is it bad using the …

Read More
syntax for nolock in sql

I have seen sql statements using nolock and with(nolock) e.g - select * from table1 nolock where column1 > 10 AND select * from table1 with(nolock) where column1 > 10 Which of the above statements is correct and why?

Read More