TSQL NOLOCK VIEW和存储过程

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.

我们最近开始在很多观点中实现NOLOCK语句。

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

我想知道:如果我将NOLOCK应用于视图,它会“逐渐减少”到存储过程

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

假设我有一个名为viewPartyPackage的视图,并且视图语句是......

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?

我是否会丢失NOLOCK功能,因为我是从存储过程调用的,而我又需要在存储过程中放置一个(NOLOCK)?或者视图中的NOLOCK是否会发挥作用?

2 个解决方案

#1

See the answers to this SO question. To quote:

查看此SO问题的答案。去引用:

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

请参阅MSDN中的表提示:“在SQL Server 2005中,所有锁定提示都会传播到视图中引用的所有表和视图。此外,SQL Server还会执行相应的锁定一致性检查。”

#2

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

无论从何处调用视图,视图中的NOLOCK都将生效。

#1