Effect of Nolock Hint in Select Statements

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.

Does NOLOCK hint slow down operation?

The short answer to the question as stated is: "No."

In most cases the NOLOCK hint will speed up the query in question, as well as, any other queries operating against the specified table at the same time. The reason is that no locks are checked or obtained. You've listed the possible side effects in your question so I won't cover those here.

At the end of the day the query will be faster, but the results will be suspect.

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.

Can NOLOCK cause DISTINCT to fail?

Sure this can happen. The engine is smart enough to know that since ID is your primary key it isn't going to waste resources looking for duplicates. However, you have introduced the dreaded NOLOCK hint. And you said that TableB is being updated during this process.

What you are almost certainly experiencing here is one the side affects of NOLOCK brought on by page splits. These page splits can cause the engine to return duplicate rows and as I said before the engine assumes you have no duplicates because you are selecting the primary key and there can't be duplicates. This is NOT a bug in sql server, it is yet another reason so stop using the hint.

Lock SQL Table/Row and using NOLOCK/READPAST

1. This is highly dependent on your servers settings. Generally speaking, you want to lock records, even when you are just reading them because you don't want data to change while you are reading it. This isn't just something that affects updated records, but also inserts. You can learn more about read commits and snapshop isolation here:
   https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

2. Both NOLOCK/READPAST should be avoided at all cost. There are a very small handful of scenarios where these make sense, but they are exceedingly rare. You are better off optimizing your query to perform better and reduce the amount of records being locked and the time that the records spend being locked. One case that I can see NOLOCK being useful would be a log table that only has inserts, and your query doesn't join the data to other tables, AND a dirty record wouldn't cause problems.

3. NOLOCK doesn't lock records that it reads. The risk here is that records you are reading can literally change mid read. This means you can begin reading a record and get some values for some columns before the update was made and some column values from after the update. If another transaction rolls back you could end up reading records that were never actually committed to the database.
   READPAST skips any rows that are locked. If another query runs and the criteria causes rows 1-25 of 100 to be locked while you are querying the same data you are only going to see records 26-100. To your query locked rows don't exist.
   Great article with the details:
   https://www.mssqltips.com/sqlservertip/4468/compare-sql-server-nolock-and-readpast-table-hints/

You would be far better served by spending time learning to optimize your queries to reduce the number of records they need to lock, and improving the performance so that the amount of time those locks exist is kept to a minimum.

Understanding the NOLOCK hint

The NOLOCK table hint will cause that no shared locks will be taken for the table in question; same with READUNCOMMITTED isolation level, but this time applies not to a single table but rather to everything involved. So, the answer is 'no, it won't lock the table'. Note that possible schema locks will still be held even with readuncommitted.

Not asking for shared locks the read operation will potentially read dirty data (updated but not yet committed) and non-existent data (updated, but rolled back), transactionally inconsistent in any case, and migh even skip the whole pages as well (in case of page splits happening simultaneously with the read operation).

Specifying either NOLOCK or READUNCOMMITTED is not considered a good practice. It will be faster, of course. Just make sure you're aware of consequences.

Also, support for these hints in UPDATE and DELETE statements will be removed in a future version, according to docs.

Is the With(NoLock) hint dangerous in my case?

Yes, there could well be issues if you are collecting data that has to be accurate - you may grab "dirty" / incorrect data and then persist it. Have you looked at whether indexing can correct the deadlock problem instead? Often a deadlock scenario can be solved by controlling the access pattern to the data pages, which is controlled by the available indexes.

What I like to do is look at the deadlock graph to see where the conflict is, then look at the order of operations in the code, and which indexes are being used to access/modify the data, to see if I can adjust to remove the deadlock risk.

---

Related Topics