SQL NOLOCK 提示在 SELECT 语句中的作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/210171/
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
Effect of NOLOCK hint in SELECT statements
提问by Bob Probst
I guess the real question is:
我想真正的问题是：
If I don't care about dirty reads, will adding the with (NOLOCK)hint to a SELECT statement affect the performance of:
如果我不关心脏读，将with (NOLOCK)提示添加到 SELECT 语句会影响以下性能：
- the current SELECT statement
- other transactions against the given table
- 当前的 SELECT 语句
- 针对给定表的其他事务
Example:
例子：
Select *
from aTable with (NOLOCK)
回答by tom.dietrich
1) Yes, a select with NOLOCKwill complete faster than a normal select.
1)是的，选择 withNOLOCK将比普通选择完成得更快。
2) Yes, a select with NOLOCKwill allow other queries against the effected table to complete faster than a normal select.
2)是的，选择 withNOLOCK将允许对受影响表的其他查询比普通选择完成得更快。
Why would this be?
为什么会这样？
NOLOCKtypically (depending on your DB engine) means give me your data, and I don't care what state it is in, and don't bother holding it still while you read from it. It is all at once faster, less resource-intensive, and very very dangerous.
NOLOCK通常（取决于您的数据库引擎）意味着给我您的数据，我不在乎它处于什么状态，并且在您读取数据时不要打扰它保持静止。它一下子变得更快，资源占用更少，而且非常非常危险。
You should be warned to never do an update from or perform anything system critical, or where absolute correctness is required using data that originated from a NOLOCKread. It is absolutely possible that this data contains rows that were deleted during the query's run or that have been deleted in other sessions that have yet to be finalized. It is possible that this data includes rows that have been partially updated. It is possible that this data contains records that violate foreign key constraints. It is possible that this data excludes rows that have been added to the table but have yet to be committed.
应该警告您永远不要更新或执行任何系统关键的事情，或者使用源自NOLOCK读取的数据需要绝对正确的地方。此数据绝对有可能包含在查询运行期间删除的行或已在尚未完成的其他会话中删除的行。此数据可能包含已部分更新的行。此数据可能包含违反外键约束的记录。此数据可能不包括已添加到表但尚未提交的行。
You really have no way to know what the state of the data is.
你真的无法知道数据的状态。
If you're trying to get things like a Row Count or other summary data where some margin of error is acceptable, then NOLOCKis a good way to boost performance for these queries and avoid having them negatively impact database performance.
如果您正在尝试获取诸如行计数或其他可以接受某些误差幅度的汇总数据之类的内容，那么这NOLOCK是提高这些查询性能并避免它们对数据库性能产生负面影响的好方法。
Always use the NOLOCKhint with great caution and treat any data it returns suspiciously.
始终NOLOCK非常谨慎地使用提示，并处理它返回的任何可疑数据。
回答by Pittsburgh DBA
NOLOCK makes most SELECT statements faster, because of the lack of shared locks. Also, the lack of issuance of the locks means that writers will not be impeded by your SELECT.
由于缺少共享锁，NOLOCK 使大多数 SELECT 语句更快。此外，不发布锁意味着编写者不会受到您的 SELECT 的阻碍。
NOLOCK is functionally equivalent to an isolation level of READ UNCOMMITTED. The main difference is that you can use NOLOCK on some tables but not others, if you choose. If you plan to use NOLOCK on all tables in a complex query, then using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is easier, because you don't have to apply the hint to every table.
NOLOCK 在功能上等同于 READ UNCOMMITTED 的隔离级别。主要区别在于您可以在某些表上使用 NOLOCK，但如果您愿意，则不能在其他表上使用。如果您计划对复杂查询中的所有表使用 NOLOCK，那么使用 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 会更容易，因为您不必将提示应用于每个表。
Here is information about all of the isolation levels at your disposal, as well as table hints.
以下是有关您可以使用的所有隔离级别的信息以及表格提示。
回答by Pittsburgh DBA
In addition to what is said above, you should be very aware that nolock actually imposes the risk of you notgetting rows that has been committed beforeyour select.
除了上面所说的，您应该非常清楚 nolock 实际上会给您带来风险，即您无法获取在选择之前已提交的行。
回答by StingyHyman
It will be faster because it doesnt have to wait for locks
它会更快，因为它不必等待锁定
回答by WonderWorker
The answer is Yesif the query is run multiple times at once, because each transaction won't need to wait for the others to complete. However, If the query is run once on its own then the answer is No.
Yes. There's a significant probability that careful use of WITH(NOLOCK) will speed up your database overall. It means that other transactions won't have to wait for this SELECT statement to finish, but on the other hand, other transactions will slow down as they're now sharing their processing time with a new transaction.
如果查询一次运行多次，答案是肯定的，因为每个事务不需要等待其他事务完成。但是，如果查询单独运行一次，那么答案是否定的。
是的。小心使用 WITH(NOLOCK) 很有可能会加快您的数据库的整体速度。这意味着其他事务不必等待此 SELECT 语句完成，但另一方面，其他事务会变慢，因为它们现在与新事务共享处理时间。
Be careful to onlyuse WITH (NOLOCK)in SELECT statements on tables that have a clustered index.
注意仅WITH (NOLOCK)在具有聚集索引的表上的 SELECT 语句中使用。
WITH(NOLOCK) is often exploited as a magic way to speed up database read transactions.
WITH(NOLOCK) 通常被用作加速数据库读取事务的神奇方式。
The result set can contain rows that have not yet been committed, that are often later rolled back.
结果集可以包含尚未提交的行，这些行通常稍后回滚。
If WITH(NOLOCK) is applied to a table that has a non-clustered index then row-indexes can be changed by other transactions as the row data is being streamed into the result-table. This means that the result-set can be missing rows or display the same row multiple times.
如果 WITH(NOLOCK) 应用于具有非聚集索引的表，则当行数据正在流入结果表时，其他事务可以更改行索引。这意味着结果集可能缺少行或多次显示同一行。
READ COMMITTED adds an additional issue where data is corrupted within a single column where multiple users change the same cell simultaneously.
READ COMMITTED 增加了一个额外问题，即多个用户同时更改同一单元格的单个列中的数据损坏。
