SQL WITH (NOLOCK) vs SET 事务隔离级别读取未提交
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3550337/
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
WITH (NOLOCK) vs SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
提问by Jim B
Could someone give me some guidance on when I should use WITH (NOLOCK)as opposed to SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
有人可以给我一些指导，指导我什么时候应该使用WITH (NOLOCK)而不是SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
What are the pros/cons of each? Are there any unintended consequences you've run into using one as opposed to the other?
每个的优点/缺点是什么？使用一种而不是另一种时，您是否遇到过任何意外后果？
采纳答案by HLGEM
They are the same thing. If you use the set transaction isolation levelstatement, it will apply to all the tables in the connection, so if you only want a nolockon one or two tables use that; otherwise use the other.
他们是一样的东西。如果您使用该set transaction isolation level语句，它将适用于连接中的所有表，因此如果您只想要nolock一个或两个表中的一个，请使用该语句；否则使用另一个。
Both will give you dirty reads. If you are okay with that, then use them. If you can't have dirty reads, then consider snapshotor serializablehints instead.
两者都会给你脏读。如果您对此感到满意，请使用它们。如果你不能有脏读，那么考虑snapshot或serializable提示。
回答by Anon246
WITH (NOLOCK) is a hint on a table level. Setting the transaction isolation level to READ_UNCOMMITTED with affect the connection. The difference is in terms of scope. See READUNCOMMITTED and NOLOCK in the SQL Server documentation here:
WITH (NOLOCK) 是表级别的提示。将事务隔离级别设置为 READ_UNCOMMITTED 会影响连接。区别在于范围。请参阅此处 SQL Server 文档中的 READUNCOMMITTED 和 NOLOCK：
http://technet.microsoft.com/en-us/library/ms187373.aspx
http://technet.microsoft.com/en-us/library/ms187373.aspx
For TRANSACTION ISOLATION LEVEL: http://technet.microsoft.com/en-us/library/ms173763.aspx
对于事务隔离级别：http: //technet.microsoft.com/en-us/library/ms173763.aspx
回答by gbn
- NOLOCK is local to the table (or views etc)
- READ UNCOMMITTED is per session/connection
- NOLOCK 是表（或视图等）本地的
- READ UNCOMMITTED 是每个会话/连接
As for guidelines... a random search from StackOverflow and the electric interweb...
至于指导方针……从 StackOverflow 和电子互联网随机搜索……
回答by Sean Copenhaver
To my knowledge the only difference is the scope of the effects as Strommy said. NOLOCK hint on a table and the READ UNCOMMITTED on the session.
据我所知，唯一的区别是斯特罗米所说的影响范围。表上的 NOLOCK 提示和会话上的 READ UNCOMMITTED。
As to problems that can occur, it's all about consistency. If you care then be aware that you could get what is called dirty reads which could influence other data being manipulated on incorrect information.
至于可能出现的问题，一切都与一致性有关。如果您关心，那么请注意，您可能会获得所谓的脏读，这可能会影响其他数据在不正确的信息上被操纵。
I personally don't think I have seen any problems from this but that may be more due to how I use nolock. You need to be aware that there are scenarios where it will be OK to use. Scenarios where you are mostly adding new data to a table but have another process that comes in behind to check for a data scenario. That will probably be OK since the major flow doesn't include going back and updating rows during a read.
我个人认为我没有看到任何问题，但这可能更多是由于我如何使用 nolock。您需要注意在某些情况下可以使用。您主要向表中添加新数据但有另一个进程在后面检查数据的场景。这可能没问题，因为主要流程不包括在读取期间返回和更新行。
Also I believe that these days you should look into Multi-version Concurrency Control. I believe they added it in 2005 and it helps stop the writers from blocking readers by giving readers a snapshot of the database to use. I'll include a link and leave further research to the reader:
另外我相信现在你应该研究多版本并发控制。我相信他们在 2005 年添加了它，它通过为读者提供要使用的数据库快照来帮助阻止作者阻止读者。我会附上一个链接，并将进一步的研究留给读者：
回答by Rez.Net
You cannot use Set Transaction Isolation Level Read Uncommitted in a View (you can only have one script in there in fact), so you would have to use (nolock) if dirty rows should be included.
您不能在视图中使用 Set Transaction Isolation Level Read Uncommitted （实际上您只能在其中包含一个脚本），因此如果应包含脏行，则必须使用 (nolock)。
回答by Alex
As you have to use WITH (NOLOCK) for each table it might be annoying to write it in every FROM or JOIN clause. However it has a reason why it is called a "dirty" read. So you really should know when you do one, and not set it as default for the session scope. Why?
由于您必须对每个表使用 WITH (NOLOCK)，因此在每个 FROM 或 JOIN 子句中编写它可能会很烦人。然而，它被称为“脏”读是有原因的。所以你真的应该知道你什么时候做，而不是将它设置为会话范围的默认值。为什么？
Forgetting a WITH (NOLOCK) might not affect your program in a very dramatic way, however doing a dirty read where you do notwant one can make the difference in certain circumstances.
忘记 WITH (NOLOCK) 可能不会以非常显着的方式影响您的程序，但是在您不想要的情况下进行脏读可能会在某些情况下产生影响。
So use WITH (NOLOCK) if the current data selected is allowed to be incorrect, as it might be rolled back later. This is mostly used when you want to increase performance, and the requirements on your application context allow it to take the risk that inconsistent data is being displayed. However you or someone in charge has to weigh up pros and cons of the decision of using WITH (NOLOCK).
因此，如果允许选择的当前数据不正确，请使用 WITH (NOLOCK)，因为它可能会在以后回滚。这主要在您想要提高性能时使用，并且对应用程序上下文的要求允许它承担显示不一致数据的风险。但是，您或负责人必须权衡使用 WITH (NOLOCK) 的决定的利弊。
