What Can Happen as a Result of Using (Nolock) on Every Select in SQL Server

What can happen as a result of using (nolock) on every SELECT in SQL Server?

Reposting this answer:

---

NOLOCK means placing no locks at all.

Your query may returns portions of data as of before UPDATE and portions as of after UPDATE in a single query.

Like, a debit without a credit and these kinds of stuff.

For instance, I just ran this query on a large table:

SELECT  SUM(LEN(name))
FROM    master WITH (NOLOCK)
OPTION (MAXDOP 1)

---
18874367

All name's have length of 1.

Then I reran it and in the middle of the query updated the table:

UPDATE  master
SET     name = 'tt'
WHERE   id <= 10000

SELECT  SUM(LEN(name))
FROM    master WITH (NOLOCK)
OPTION (MAXDOP 1)

---
18874944

As we can see, this query noticed 577 rows as updated (length 2), all other rows as not updated (length 1).

SELECT  SUM(LEN(name))
FROM    master WITH (NOLOCK)
OPTION (MAXDOP 1)

---
18884367

And this query, run right after the previous one finished, sees all updates.

Effect of NOLOCK hint in SELECT statements

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.

Select with (nolock)

Nolocks should be used with extreme caution. The most common understanding of nolock (read uncommitted) hint is that it reads data that has not been committed yet. However, there are other side effects that can be very dangerous. (search for "nolock" and "page splits")

There's a really good write up here... https://www.itprotoday.com/sql-server/beware-nolock-hint

In short, "nolocking"ing everything is not always a good idea... if ever.

What is with (nolock) in SQL Server?

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.

SQL Server NOLOCK and joins

I won't address the READ UNCOMMITTED argument, just your original question.

Yes, you need WITH(NOLOCK) on each table of the join. No, your queries are not the same.

Try this exercise. Begin a transaction and insert a row into table1 and table2. Don't commit or rollback the transaction yet. At this point your first query will return successfully and include the uncommitted rows; your second query won't return because table2 doesn't have the WITH(NOLOCK) hint on it.

Using WITH NOLOCK Table Hint in Query Using View - Does it Propagate Within the View?

Yes, NOLOCK will propagate to the tables used by the view definition (at least in SQL Server 2005).

See Table Hints in MSDN:

In SQL Server 2005, all lock hints are propagated to all the tables and views that are referenced in a view. Also, SQL Server performs the corresponding lock consistency checks.

However,

If a table contains computed columns and the computed columns are computed by expressions or functions accessing columns in other tables, the table hints are not used on those tables. This means the table hints are not propagated. For example, a NOLOCK table hint is specified on a table in the query. This table has computed columns that are computed by a combination of expressions and functions that access columns in another table. The tables referenced by the expressions and functions do not use the NOLOCK table hint when accessed.

If you're using indexed views you might want to read a bit more as there are some special cases there too.

Also see View Resolution for more info.

Is the NOLOCK (Sql Server hint) bad practice?

With NOLOCK hint, the transaction isolation level for the SELECT statement is READ UNCOMMITTED. This means that the query may see dirty and inconsistent data.

This is not a good idea to apply as a rule. Even if this dirty read behavior is OK for your mission critical web based application, a NOLOCK scan can cause 601 error which will terminate the query due to data movement as a result of lack of locking protection.

I suggest reading When Snapshot Isolation Helps and When It Hurts - the MSDN recommends using READ COMMITTED SNAPSHOT rather than SNAPSHOT under most circumstances.

Do I get any performance gain by using WITH (NOLOCK) on SQL Server database where READ_COMMITTED_SNAPSHOT is switched on?

In theory there should be an improvement because although the query will not block in either case, in the READ_COMMITTED_SNAPSHOT case the book-keeping associated with the locks is still required, so that the database knows when it needs to create/retain/cleanup snapshotted rows/pages.

However as with all performance questions, you should try it and see if the difference exists in practice, and if it is, if it matters to your use-case.

---

Related Topics