SQL 在 SELECT 语句上使用 NOLOCK 的目的是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15304764/
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
What is purpose of using NOLOCK on a SELECT statement?
提问by rstuppi
Can anyone suggest a good reason to issue a NOLOCK on a SELECT statement?
任何人都可以提出在 SELECT 语句上发出 NOLOCK 的好理由吗？
I am re-factoring some stored procedures that are littered with them and it is my understanding that a NOLOCK on a SELECT statement is all but useless.
我正在重构一些杂乱无章的存储过程，据我所知，SELECT 语句上的 NOLOCK 几乎是无用的。
回答by Just Aguy
I see some bad info flying around already. WITH(NOLOCK) on a select is not a performance tool to be used wherever you need speed. Read this articleit dumps on the answer provided by eSamuel because that ISwhat most people think, as you can see by the votes. People who don't know but regurgitate what they've heard as how it should be done. Learn to optimize your data and queries first. The NOLOCK hint should only be used in systems where you are consistently dealing with records being deadlocked or another issue that NOLOCK provides a solution for.
我已经看到一些不好的信息四处飞扬。选择上的 WITH(NOLOCK) 不是在需要速度的任何地方使用的性能工具。阅读这篇文章，它转储了 eSamuel 提供的答案，因为这是大多数人的想法，正如您从投票中看到的那样。那些不知道但反刍他们所听到的应该如何做的人。首先学习优化数据和查询。NOLOCK 提示应该只用于您一直在处理死锁记录或 NOLOCK 提供解决方案的其他问题的系统中。
I work at a financial institution developing in an OLTPsystem which processes numerous transactions a minute and provides real-time reporting capabilities to clients and customers. These people perform reads on our fresh data all day long and if we don't do report queries using NOLOCK it's only a matter of time before a deadlock occurs.
我在一家金融机构工作，开发OLTP系统，该系统每分钟处理大量交易，并为客户和客户提供实时报告功能。这些人整天都在读取我们的新数据，如果我们不使用 NOLOCK 进行报告查询，那么死锁发生只是时间问题。
Do your due diligence and read from reputable, professional sources before jumping on NOLOCK as the holy graile to SQL query speed because there's much more to it.
在将 NOLOCK 作为 SQL 查询速度的圣杯之前，请进行尽职调查并阅读信誉良好的专业资源，因为还有更多内容。
回答by Martin Smith
So the SELECTisn't blocked by concurrent data modification queries.
所以SELECT并发数据修改查询不会阻止它。
Some people believe it is a magic go faster button and apply it liberally. It can be worse than useless unless your use case is accepting of the possibility of reading dirty (uncommitted) data that may be rolled back after you have read it (and hence never logically existed) and the greater possibility of certain types of anomaly.
有些人认为这是一个神奇的“更快”按钮并大量应用它。它可能比无用更糟糕，除非您的用例接受读取脏（未提交）数据的可能性，这些数据在您读取后可能会回滚（因此从逻辑上不存在）以及某些类型异常的可能性更大。
Compared to read committed isolation level nolockwill give you a greater probability of your scan missing data, reading it twice or completely failing with a data movement error.
与已提交读隔离级别相比nolock，您的扫描丢失数据、读取两次或完全失败并导致数据移动错误的可能性更大。
回答by eSamuel
NOLOCK is equivalent to READ-UNCOMMITTED on the transaction.
NOLOCK 相当于事务上的 READ-UNCOMMITTED。
In short, using NOLOCK ensures your SELECT statements are fast because it doesn't have to wait for existing locks or transactions to complete before returning the results of your query. The down side is that you can end up pulling back "dirty" data - things that might have been rolled back before being committed but after your SELECT statement was run.
简而言之，使用 NOLOCK 可确保您的 SELECT 语句快速，因为它不必等待现有锁或事务完成后再返回查询结果。不利的一面是，您最终可能会拉回“脏”数据——在提交之前但在运行 SELECT 语句之后可能已经回滚的数据。
Additional information can be found in Using NOLOCK and READPAST table hints in SQL Server.
可以在 SQL Server中使用 NOLOCK 和 READPAST 表提示中找到其他信息。
回答by cclusetti
NoLocks are especially useful if you are reading from lookup tables where the content rarely changes.
如果您从内容很少更改的查找表中读取数据，则 NoLocks 尤其有用。
The problem with nolock is,of course, that you may potentially get bad data if the table is being updated. There are some scenarios for tables that are constantly updated where nolock can be used correctly. For instance, you have the table:
当然，nolock 的问题在于，如果表正在更新，您可能会获得错误的数据。对于不断更新的表，有一些场景可以正确使用 nolock。例如，你有一张表：
UserConfig { id int PRIMARY KEY, is_active_flag bit, allow_x_feature bit, allow_y_feature bit }
UserConfig { id int PRIMARY KEY、is_active_flag 位、allow_x_feature 位、allow_y_feature 位}
If you are running select queries and filtering by id, then you want to use nolock even if this table is frequently updated.
如果您正在运行选择查询并按 id 过滤，那么即使此表经常更新，您也希望使用 nolock。
