SQL Server NOLOCK 和联接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3783525/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server NOLOCK and joins
提问by DanP
Background: I have a performance-critical query I'd like to run and I don't care about dirty reads.
背景：我有一个想要运行的性能关键查询，我不关心脏读。
My question is; If I'm using joins, do I have to specify the NOLOCK hint on those as well?
我的问题是；如果我使用连接，是否还必须在这些连接上指定 NOLOCK 提示？
For instance; is:
例如; 是：
SELECT * FROM table1 a WITH (NOLOCK)
INNER JOIN table2 b WITH (NOLOCK) ON a.ID = b.ID
Equivalent to:
相当于：
SELECT * FROM table1 a WITH (NOLOCK)
INNER JOIN table2 b ON a.ID = b.ID
Or will I need to specify the (NOLOCK)hint on the join to ensure I'm not locking the joined table?
或者我是否需要(NOLOCK)在连接上指定提示以确保我没有锁定连接表？
回答by codeConcussion
I won't address the READ UNCOMMITTEDargument, just your original question.
我不会解决这个READ UNCOMMITTED论点，只是你最初的问题。
Yes, you need WITH(NOLOCK)on each table of the join. No, your queries are not the same.
是的，您需要WITH(NOLOCK)对每个表进行连接。不，您的查询不一样。
Try this exercise. Begin a transaction and insert a row into table1 and table2. Don't commit or rollback the transaction yet. At this point your first query will return successfully and include the uncommitted rows; your second query won't return because table2 doesn't have the WITH(NOLOCK)hint on it.
试试这个练习。开始一个事务并在 table1 和 table2 中插入一行。暂时不要提交或回滚事务。此时，您的第一个查询将成功返回并包含未提交的行；您的第二个查询不会返回，因为 table2 上没有WITH(NOLOCK)提示。
回答by Jagmag
I was pretty sure that you need to specify the NOLOCKfor each JOINin the query. But my experience was limited to SQL Server 2005.
我很确定您需要在查询中NOLOCK为每个指定JOIN。但我的经验仅限于 SQL Server 2005。
When I looked up MSDN just to confirm, I couldn't find anything definite. The below statements do seem to make me think, that for 2008, your two statements above are equivalent though for 2005 it is not the case:
当我查找 MSDN 只是为了确认时，我找不到任何确定的内容。以下陈述似乎确实让我认为，对于 2008 年，您上面的两个陈述是等效的，但对于 2005 年则并非如此：
[SQL Server 2008 R2]
All lock hints are propagated to all the tables and views that are accessed by the query plan, including tables and views referenced in a view. Also, SQL Server performs the corresponding lock consistency checks.
[SQL Server 2005]
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.
【SQL Server 2008 R2】
所有锁提示都传播到查询计划访问的所有表和视图，包括视图中引用的表和视图。此外，SQL Server 执行相应的锁一致性检查。
[SQL Server 2005]
在 SQL Server 2005 中，所有锁定提示都会传播到视图中引用的所有表和视图。此外，SQL Server 执行相应的锁一致性检查。
Additionally, point to note - and this applies to both 2005 and 2008:
此外，请注意 - 这适用于 2005 年和 2008 年：
The table hints are ignored if the table is not accessed by the query plan. This may be caused by the optimizer choosing not to access the table at all, or because an indexed view is accessed instead. In the latter case, accessing an indexed view can be prevented by using the
OPTION (EXPAND VIEWS)query hint.
如果查询计划未访问表，则忽略表提示。这可能是由于优化器选择根本不访问该表，或者因为访问了索引视图。在后一种情况下，可以使用
OPTION (EXPAND VIEWS)查询提示来阻止访问索引视图。
回答by Remus Rusanu
Neither. You set the isolation level to READ UNCOMMITTEDwhich is always better than giving individual lock hints. Or, better still, if you care about details like consistency, use snapshot isolation.
两者都不。您设置的隔离级别READ UNCOMMITTED总是比提供单独的锁提示更好。或者，更好的是，如果您关心一致性等细节，请使用快照隔离。
