问题描述:

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?

网友答案:

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

网友答案:

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

相关阅读:
Top