我的 CREATE View 查询中是否需要 NO LOCK

问题描述

我正在 MS SQL Server 上创建视图.我对 MS SQL 的接触不多，也不太熟悉 NO LOCK 提示.我明白它的作用，但我不知道是否需要在我的情况下使用它.有人问我是否应该包括它，但我不知道.

I am creating a view on a MS SQL Server. I have not had much exposure to MS SQL and am not real familiar with the NO LOCK hint. I understand what it does, but I don't know if I need to use it in my situation. I have been asked if I should include it and I don't know.

在我用来创建视图的所有查询之后，我是否需要添加 NO HINT?或者这会对查询视图本身的用户产生任何影响吗?USER 是否应该将 NO LOCK 添加到针对 VIEW 的查询中?

Do I need to add NO HINT after all the queries I am using to create my view? Or will that have any affect on the user querying the view itself? Should the USER add the NO LOCK to the query against the VIEW?

对最佳方法的任何指导和任何澄清表示赞赏！

Any guidance on the best approach and any clarification is appreciated!

推荐答案

我会先回答你的问题.

最好在视图外部而不是视图中的表上使用 NOLOCK 提示.

It is better to have the NOLOCK hint on the view from outside instead of on the tables in the view.

例如

select * from vwTest with (nolock)

或

set transaction isolation level read uncommitted
select * from vwTest

您作为创建者这样做是为了迎合更广泛的用户群，这些用户群可能与您一样在 SQL 方面经验丰富，也可能没有.通过不在视图中封装 NOLOCK 提示，可以鼓励其他开发人员真正考虑他们希望如何以安全有效的方式检索数据.

Doing it this way you as the creator is catering for a wider user base who may or may not be as experienced at SQL as yourself. By not encapsulating NOLOCK hints in the view encourages other developers to really think about how they would like to retrieve the data in a safe and efficient manner.

现在有更多关于 NOLOCK 的信息.如果您 100% 确定底层数据不再发生变化，这是一个很好的技巧，一个很好的例子是当 ETL 系统完成当天的数据加载时.它在只读报告系统中也很方便，您可以再次确定报告运行之间没有数据移动.

Now more info on NOLOCK. It is a nice trick if you are 100% sure the underlying data is no longer changing, a good example is when a ETL system finishes loading data for the day. It is also handy in a read-only reporting system where again you are sure there is no data movement between report runs.

否则，不建议在您的系统中使用它.如果您不真正了解其含义，那弊大于利.

Otherwise, it is not a recommended hint to use in your system. It does more harm than good if you don't really understand the implications.

NOLOCK 可能造成的损害请参考以下链接:如果使用 NOLOCK 提示，可能会丢失先前提交的行

Please refer to the following links for the damages NOLOCK can cause: Previously committed rows might be missed if NOLOCK hint is used

这篇关于我的 CREATE View 查询中是否需要 NO LOCK的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！