What is “with (nolock)” in SQL Server?

Question

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 1

Simple answer - whenever your SQL is not altering data, and you have a query that might interfere with other activity (via locking).

It's worth considering for any queries used for reports, especially if the query takes more than, say, 1 second.

It's especially useful if you have OLAP-type reports you're running against an OLTP database.

The first question to ask, though, is "why am I worrying about this?" ln my experience, fudging the default locking behavior often takes place when someone is in "try anything" mode and this is one case where unexpected consequences are not unlikely. Too often it's a case of premature optimization and can too easily get left embedded in an application "just in case." It's important to understand why you're doing it, what problem it solves, and whether you actually have the problem.

Answer 2

If you are handling finance transactions then you will never want to use nolock. nolock is best used to select from large tables that have lots updates and you don't care if the record you get could possibly be out of date.

For financial records (and almost all other records in most applications) nolock would wreak havoc as you could potentially read data back from a record that was being written to and not get the correct data.

Answer 3

The question is what is worse:

• a deadlock, or
• a wrong value?

For financial databases, deadlocks are far worse than wrong values. I know that sounds backwards, but hear me out. The traditional example of DB transactions is you update two rows, subtracting from one and adding to another. That is wrong.

In a financial database you use business transactions. That means adding one row to each account. It is of utmost importance that these transactions complete and the rows are successfully written.

Getting the account balance temporarily wrong isn't a big deal, that is what the end of day reconciliation is for. And an overdraft from an account is far more likely to occur because two ATMs are being used at once than because of a uncommitted read from a database.

That said, SQL Server 2005 fixed most of the bugs that made NOLOCK necessary. So unless you are using SQL Server 2000 or earlier, you shouldn't need it.

Further Reading
Row-Level Versioning

Answer 4

Short answer:

Never use WITH (NOLOCK).

Long answer:

NOLOCK is often exploited as a magic way to speed up database reads, but I try to avoid using it wherever possible.

The result set can contain rows that have not yet been committed, that are often later rolled back.

An error or Result-set can be empty, be missing rows or display the same row multiple times.

This is because other transactions are moving data at the same time you're reading it.

READ COMMITTED adds an additional issue where data is corrupted within a single column where multiple users change the same cell simultaneously.

There are other side-effects too, which result in sacrificing the speed increase you were hoping to gain in the first place.

It can be argued that it is fine to use it in places where you can get away with it, but what's the point? I can't think of any situation where corrupted data is acceptable.

Never ever use NOLOCK ever.

(ever)