1

In our company we tend to use views and stored procedures.

We've recently started to implement the NOLOCK statement to a lot of our views.

I was wondering: if I am applying NOLOCK to a view, it "trickles down" to the stored procedure

Say I have a view called viewPartyPackage and view statement was...

SELECT   
    PartyPackageID, Name, Created, LastModified, Deleted 
FROM        
    dbo.PartyPackage WITH (NOLOCK) 
WHERE     
    (Deleted = 0)

and also I had a stored procedure:

ALTER proc [dbo].[partypackage_Select]
    (@PartyPackageID bigint = null) 
AS 
    SELECT * 
    FROM [viewPartyPackage] PartyPackage 
    WHERE (@PartyPackageID IS NULL OR @PartyPackageID = [PartyPackageID])

Would I lose the NOLOCK feature because I'm calling from a stored procedure and in turn would I need to put a (NOLOCK) on the stored procedure as well? Or does the NOLOCK that's in the view come into play?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mike
  • 2,547
  • 1
  • 24
  • 36
  • If you want to use this throughout, use `SET TRANSACTION READ UNCOMMITTED` at the start. Does your company know the risks of using `NOLOCK`? i.e. you can get incorrect numbers – Nick.Mc Dec 24 '16 at 10:17

2 Answers2

2

See the answers to this SO question. To quote:

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."

Community
  • 1
  • 1
Oded
  • 489,969
  • 99
  • 883
  • 1,009
  • sorry to repeat the question on this post. What about the other way round: if theres a NOLOCKon the store proc and the view didn't have the NOLOCK? – Mike Jul 30 '10 at 14:10
  • 1
    @Mike - stored procedures do not have NOLOCK defined. They may define NOLOCK on the tables/views they call. – Oded Jul 30 '10 at 14:15
1

The NOLOCK in the view will take effect no matter where the view is called from.

Fosco
  • 38,138
  • 7
  • 87
  • 101
  • How about the other way round? if the NOLOCK was on the store proc and the view didn't have the NOLOCK? – Mike Jul 30 '10 at 14:07
  • If you select from the view with NOLOCK in a stored procedure, it will use NOLOCK for that specific call... but it wouldn't magically make the view always use NOLOCK. – Fosco Jul 30 '10 at 14:23