SQL Server NOLOCK 和联接
背景：我想运行一个性能关键的查询，并且我不关心脏读。
我的问题是；如果我使用连接，我是否还必须在这些连接上指定 NOLOCK 提示？
例如;是：
SELECT * FROM table1 a WITH (NOLOCK)
INNER JOIN table2 b WITH (NOLOCK) ON a.ID = b.ID
相当于：
SELECT * FROM table1 a WITH (NOLOCK)
INNER JOIN table2 b ON a.ID = b.ID
或者我需要在连接上指定 (NOLOCK) 提示以确保我没有锁定连接表？
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?
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我不会解决 READ UNCOMMITTED 论点，只是解决您原来的问题。
是的，您需要在连接的每个表上使用
WITH(NOLOCK)。不，您的查询不一样。尝试这个练习。开始事务并向 table1 和 table2 中插入一行。暂时不要提交或回滚事务。此时，您的第一个查询将成功返回并包含未提交的行；您的第二个查询不会返回，因为 table2 上没有
WITH(NOLOCK)提示。I won't address the
READ UNCOMMITTEDargument, just your original question.Yes, you need
WITH(NOLOCK)on each table of the join. No, your queries are not the same.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.我非常确定您需要为查询中的每个
JOIN指定NOLOCK。但我的经验仅限于SQL Server 2005。当我查找MSDN只是为了确认时，我找不到任何明确的东西。下面的陈述似乎确实让我认为，对于 2008 年，您上面的两个陈述是等效的，尽管对于 2005 年情况并非如此：
此外，请注意 - 这适用于 2005 年和 2008 年：
I was pretty sure that you need to specify the
NOLOCKfor eachJOINin the query. But my experience was limited to 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:
Additionally, point to note - and this applies to both 2005 and 2008:
两者都不。您将隔离级别设置为
READ UNCOMMITTED这总是比给出单独的锁定提示更好。或者，更好的是，如果您关心 一致性，使用 快照隔离。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.