tsql - SQL Server: VIEW with NO LOCK but called without NO LOCK

Question

I am on Microsoft SQL Server 2016 and I have the following (simplified) query that references a view:

SELECT Column1
FROM dbo.myView

The view looks as follows:

CREATE VIEW dbo.myView
AS
SELECT Column1
FROM dbo.SomeOtherTable WITH (NOLOCK)

My question now is: Does the fact that the outer SELECT does not use the NOLOCK table hint imply that the NOLOCK inside the VIEW is irrelevant? Or will the NOLOCK still be relevant when executing the outer SELECT?

The bigger context of my question is that I had a case where I was analyzing a blocking situation on a server. I found a query such as the SELECT described above: A SELECT query was using a VIEW. The VIEW was using a NOLOCK table hint, but the outer SELECT did not. Still, the query was a head blocker and I am trying to identify the reason for this.

I already did some research and the only confirmation I could find is for the questions where people want to know if a NOLOCK is propagated down to the inner tables.

question from:https://stackoverflow.com/questions/65923426/sql-server-view-with-no-lock-but-called-without-no-lock

Answer 1

This is very easy to test. Firstly, in a sandbox environment, run the following:

CREATE TABLE dbo.MyTable (ID int);
GO


CREATE VIEW dbo.MyView AS

    SELECT ID
    FROM dbo.MyTable WITH (NOLOCK);
GO

CREATE VIEW dbo.MyView2 AS

    SELECT ID
    FROM dbo.MyTable;
GO

BEGIN TRANSACTION Test;

    INSERT INTO dbo.MyTable
    VALUES(1);

Notice I don't COMMIT the transaction. Now in a new window, run SELECT * FROM dbo.MyView;. Notice it returns results. If you also try SELECT * FROM dbo.MyView2 WITH (NOLOCK); You'll also get results. Try SELECT * FROM dbo.MyView2;, however, and the query will "hang".

You can then "clean up" by returning to your original query window and running the following:

COMMIT;
GO

DROP VIEW dbo.MyView2;
DROP VIEW dbo.MyView;
DROP TABLE dbo.MyTable;

Of course, the real question is, do you need NOLOCK, but that isn't what this question is about.