0

Could you help evaluate the "nolock" hint necessity for the View.

From the view definition, we notice that there’s no WITH(NOLOCK) SQL hint added to any table reference. Thus, all the reports regarding the view have a potential to block other transactions (insert/update/delete) while it executes.

4

In case you don't know, NOLOCK is equivalent to the transaction isolation level named "READ UNCOMMITTED." I'd recommend reading this blog post from Paul White to understand some of the data integrity implications of using the NOLOCK hint: The Read Uncommitted Isolation Level

To your question though, you mention this justification for potentially adding the hint:

Thus, all the reports regarding the dbo.rptdepitemdetailsResearch have a potential to block other transactions (insert/update/delete) while it executes.

I would say not to worry about this unless your users / applications are actually experiencing blocking due to this reporting view.

If there is blocking, you could use the READ UNCOMMITTED isolation level, but note (from Paul's blog post) that it's possible to see totally invalid data in the report.

Another option would be to enable SNAPSHOT ISOLATION in the database:

ALTER DATABASE YourDatabase  
SET ALLOW_SNAPSHOT_ISOLATION ON;

And then have any code that queries your view use the SNAPSHOT isolation level:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

This will prevent your reporting query from blocking insert / update queries, and will also make the reporting query accurate to the point in time the query started.

There is overhead to using this option, including increases to the database size and increased usage of TempDB. So make sure you test accordingly.

1

After reading those blog above,you should analyse that ,

what are the chances of "Read Uncommitted" data ?

How often you can get "Uncommitted data' ?

What is the consequence of reading few Uncommitted data ?

Like if you find, query view is very important, very frequently use,chances of getting "uncommitted /dirty data is rare.

Like uyour answer is "It do not matter if there are few dirty or uncommitted data in report."

So after analysing all these thing question, you can use "Nolock" hint in some places especially in Select queries

In case you decide to use "No Lock" hint you can use it in view instead of Table.

Nolock hint use in view propagate to table .Table Hint

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.