問題描述
我正在 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的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！