Could someone give me some guidance on when I should use WITH (NOLOCK) as opposed to SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
有人能告诉我什么时候应该使用(NOLOCK)而不是设置未提交的事务隔离级别吗
What are the pros/cons of each? Are there any unintended consequences you've run into using one as opposed to the other?
它们的优缺点是什么?你使用其中一种而不是另一种会产生什么意想不到的后果吗?
6 个解决方案
#1
92
They are the same thing. If you use the set transaction isolation level statement, it will apply to all the tables in the connection, so if you only want a nolock on one or two tables use that; otherwise use the other.
它们是一样的。如果您使用set事务隔离级别语句，它将应用于连接中的所有表，因此如果您只想要一个或两个表上的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 snapshot or serializable hints instead.
两者都会让你感到恶心。如果你对此没有意见，那就使用它们。如果不能进行脏读，那么考虑快照或可序列化提示。
#2
22
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
#3
9
- NOLOCK is local to the table (or views etc)
- NOLOCK是表的本地属性(或视图等)
- READ UNCOMMITTED is per session/connection
- READ UNCOMMITTED是每个会话/连接
As for guidelines... a random search from * and the electric interweb...
至于指南……来自*和electric interweb的随机搜索……
- Is the NOLOCK (Sql Server hint) bad practice?
- NOLOCK (Sql Server提示)是错误的做法吗?
- When is it appropriate to use NOLOCK?
- 什么时候使用NOLOCK合适?
- Get rid of those NOLOCK hints…
- 摆脱那些NOLOCK的暗示…
#4
8
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提示在一个表上，而读取在会话中没有提交。
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年添加的，它通过向读者提供数据库快照来阻止作者阻塞读者。我将包含一个链接，并把进一步的研究留给读者:
MVCC
数据库隔离级别
#5
4
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事务隔离级别读取Uncommitted(实际上，您只能在其中有一个脚本)，因此如果要包含脏行，就必须使用(nolock)。
#6
3
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?
因为必须对每个表使用(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 not want one can make the difference in certain circumstances.
忘记带(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).
因此，如果选择的当前数据不正确，可以使用(NOLOCK)，因为稍后可能会回滚。当您想要提高性能时，这通常是使用的，并且应用程序上下文的需求允许它承担不一致的数据显示的风险。但是你或者你的负责人必须权衡使用WITH (NOLOCK)的利弊。
#1
92
They are the same thing. If you use the set transaction isolation level statement, it will apply to all the tables in the connection, so if you only want a nolock on one or two tables use that; otherwise use the other.
它们是一样的。如果您使用set事务隔离级别语句，它将应用于连接中的所有表，因此如果您只想要一个或两个表上的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 snapshot or serializable hints instead.
两者都会让你感到恶心。如果你对此没有意见，那就使用它们。如果不能进行脏读，那么考虑快照或可序列化提示。
#2
22
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
#3
9
- NOLOCK is local to the table (or views etc)
- NOLOCK是表的本地属性(或视图等)
- READ UNCOMMITTED is per session/connection
- READ UNCOMMITTED是每个会话/连接
As for guidelines... a random search from * and the electric interweb...
至于指南……来自*和electric interweb的随机搜索……
- Is the NOLOCK (Sql Server hint) bad practice?
- NOLOCK (Sql Server提示)是错误的做法吗?
- When is it appropriate to use NOLOCK?
- 什么时候使用NOLOCK合适?
- Get rid of those NOLOCK hints…
- 摆脱那些NOLOCK的暗示…
#4
8
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提示在一个表上，而读取在会话中没有提交。
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年添加的，它通过向读者提供数据库快照来阻止作者阻塞读者。我将包含一个链接，并把进一步的研究留给读者:
MVCC
数据库隔离级别
#5
4
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事务隔离级别读取Uncommitted(实际上，您只能在其中有一个脚本)，因此如果要包含脏行，就必须使用(nolock)。
#6
3
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?
因为必须对每个表使用(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 not want one can make the difference in certain circumstances.
忘记带(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).
因此，如果选择的当前数据不正确，可以使用(NOLOCK)，因为稍后可能会回滚。当您想要提高性能时，这通常是使用的，并且应用程序上下文的需求允许它承担不一致的数据显示的风险。但是你或者你的负责人必须权衡使用WITH (NOLOCK)的利弊。