Do I need NO LOCK in my CREATE View query

Question

I am creating a view on a MS SQL Server. I have not had much exposure to MS SQL and am not real familiar with the NO LOCK hint. I understand what it does, but I don't know if I need to use it in my situation. I have been asked if I should include it and I don't know.

Do I need to add NO HINT after all the queries I am using to create my view? Or will that have any affect on the user querying the view itself? Should the USER add the NO LOCK to the query against the VIEW?

Any guidance on the best approach and any clarification is appreciated!

Solution 1

I will answer your question first.

It is better to have the NOLOCK hint on the view from outside instead of on the tables in the view.

For example

select * from vwTest with (nolock)

or

set transaction isolation level read uncommitted
select * from vwTest

Doing it this way you as the creator is catering for a wider user base who may or may not be as experienced at SQL as yourself. By not encapsulating NOLOCK hints in the view encourages other developers to really think about how they would like to retrieve the data in a safe and efficient manner.

Now more info on NOLOCK. It is a nice trick if you are 100% sure the underlying data is no longer changing, a good example is when a ETL system finishes loading data for the day. It is also handy in a read-only reporting system where again you are sure there is no data movement between report runs.

Otherwise, it is not a recommended hint to use in your system. It does more harm than good if you don't really understand the implications.

Please refer to the following links for the damages NOLOCK can cause: Previously committed rows might be missed if NOLOCK hint is used

Solution 2

Straight from the Doc:

Caution: Because the SQL Server query optimizer typically selects the best execution plan for a query, we recommend that hints be used only as a last resort by experienced developers and database administrators.

The NOLOCK hint in particular is a notoriously pernicious and bad idea. It should only be used for special circumstances and specific needs: Such as when you are not concerned if the data returned is correct.

Comments

[ErikE]

Design your database and its queries properly so that you don't need WITH (NOLOCK). I promise you that you don't really want dirty reads--why would you want to allow the possibility of getting phantom rows that never even existed in the database? The best approach is to work carefully to minimize the time that any locks of any kind will be held on rows--including read locks. This means proper table and index design, and consistent update/insert/delete patterns from the application. Liberal use of SET TRANSACTION ISOLATION LEVEL READ UNCOMMMITTED and NOLOCK is a sign of a novice.

[Leslie]

Except I didn't design the database. And it may just be that everyone before me just put NO LOCK on everything without understanding what it did. That is why I was asking what would be the right thing to do. I appreciate everyone's help and I will not put the NO LOCK on my underlying query because I don't think that these users will be using the view in a way that the data will be changing as they are looking at it, but I will confirm that.

[Ken Smith]

Just for what it's worth - I disagree about NOLOCK being a sign of somebody who doesn't know what they're doing. Some of the sharpest DBA's I know use it regularly - in the right places, of course. I've encountered lots of scenarios over the years where I would easily live with the tradeoff of a few miscounted records if I can avoid taking a key table offline (e.g., if my aggregation query escalates to a table lock). That's certainly not every scenario, but I've run into that situation often enough that I'm confident such a blanket statement isn't terribly helpful.

[Leslie]

thanks for answering my specific question and for the additional information. I will continue to learn more about the NOLOCK and hints in general.