NOLOCK（Sql Server 提示）是不好的做法吗？

Is the NOLOCK (Sql Server hint) bad practice?

提问by Pure.Krome

I'm in the business of making website and applications that are notmission critical -> eg. banking software, space flight, intensive care monitoring application, etc. You get the idea.

我的业务是制作非关键任务的网站和应用程序-> 例如。银行软件、太空飞行、重症监护监控应用程序等。你懂的。

So, with that massive disclaimer, is it bad using the NOLOCK hint in some Sql statement? A number of years ago, it was suggested by a fellow Sql Administrator that I should use NOLOCK if I'm happy with a "dirty read" which will give me a bit more performance out of my system because each read doesn't lock the table/row/whatever.

那么，有了大量的免责声明，在某些 Sql 语句中使用 NOLOCK 提示是否不好？几年前，一位 Sql 管理员建议如果我对“脏读”感到满意，我应该使用 NOLOCK，这会给我的系统带来更多的性能，因为每次读取都不会锁定表/行/任何。

I was also told that it's a great solution if I'm experiencing dead-locks. So, I started following that thought for a few years until a Sql guru was helping me with some random code and noticed all the NOLOCKS in my sql code. I was politely scolded and he tried to explain it to me (why it's not a good thing) and I sorta got lost. I felt that the essence of his explanation was 'it's a band-aid solution to a more serious problem .. especially if you're experiencing deadlocking. As such, fix the root of the problem'.

我还被告知，如果我遇到死锁，这是一个很好的解决方案。所以，我开始遵循这个想法几年，直到 Sql 专家帮助我处理一些随机代码并注意到我的 sql 代码中的所有 NOLOCKS。我被礼貌地责骂，他试图向我解释（为什么这不是一件好事），我有点迷路了。我觉得他的解释的本质是'这是一个更严重问题的创可贴解决方案......特别是如果你正在经历僵局。因此，解决问题的根源'。

I did some googling recently about it and came across this post.

我最近做了一些谷歌搜索，发现了这篇文章。

So, can some sql db guru sensei's please enlighten me?

那么，可以请一些 sql db guru sensei 赐教吗？

采纳答案by OMG Ponies

With NOLOCK hint, the transaction isolation level for the SELECTstatement is READ UNCOMMITTED. This means that the query may see dirty and inconsistent data.

使用 NOLOCK 提示，SELECT语句的事务隔离级别为READ UNCOMMITTED. 这意味着查询可能会看到脏数据和不一致数据。

This is not a good idea to apply as a rule. Even if this dirty read behavior is OK for your mission critical web based application, a NOLOCK scan can cause 601 error which will terminate the query due to data movement as a result of lack of locking protection.

作为规则应用这不是一个好主意。即使这种脏读行为对于您的任务关键型基于 Web 的应用程序来说是可以的，但 NOLOCK 扫描可能会导致 601 错误，这将由于缺乏锁定保护而导致数据移动而终止查询。

I suggest reading When Snapshot Isolation Helps and When It Hurts- the MSDN recommends using READ COMMITTED SNAPSHOT rather than SNAPSHOT under most circumstances.

我建议阅读When Snapshot Isolation Help and When It Hurts- MSDN 建议在大多数情况下使用 READ COMMITTED SNAPSHOT 而不是 SNAPSHOT。

回答by Geoff Dalgas

Prior to working on Stack Overflow, I was against NOLOCKon the principal that you could potentially perform a SELECTwith NOLOCKand get back results with data that may be out of date or inconsistent. A factor to think about is how many records may be inserted/updated at the same time another process may be selecting data from the same table. If this happens a lot then there's a high probability of deadlocks unless you use a database mode such as READ COMMITED SNAPSHOT.

此前对堆栈溢出的工作，我是反对NOLOCK的主要是你可能执行SELECT与NOLOCK和找回数据的结果，可能是日期或不一致的。需要考虑的一个因素是，在另一个进程可能正在从同一个表中选择数据的同时，可以插入/更新多少记录。如果这种情况经常发生，那么除非您使用诸如READ COMMITED SNAPSHOT.

I have since changed my perspective on the use of NOLOCKafter witnessing how it can improve SELECTperformance as well as eliminate deadlocks on a massively loaded SQL Server. There are times that you may not care that your data isn't exactly 100% committed and you need results back quickly even though they may be out of date.

NOLOCK在目睹了它如何提高SELECT性能并消除大量负载的 SQL Server 上的死锁后，我改变了我对使用的看法。有时您可能不关心您的数据并非完全 100% 提交，即使结果可能已过时，您也需要快速返回结果。

Ask yourself a question when thinking of using NOLOCK:

在考虑使用时问自己一个问题NOLOCK：

Does my query include a table that has a high number of INSERT/UPDATEcommands and do I care if the data returned from a query may be missing these changes at a given moment?

我的查询是否包含一个具有大量INSERT/UPDATE命令的表，我是否关心从查询返回的数据是否可能在给定时刻丢失这些更改？

If the answer is no, then use NOLOCKto improve performance.

如果答案是否定的，则使用NOLOCK来提高性能。

---

---

我刚刚在NOLOCKNOLOCKStack Overflow 的代码库中快速搜索了关键字，找到了 138 个实例，因此我们在很多地方都使用了它。

回答by Mitch Wheat

If you don't care about dirty reads (i.e. in a predominately READ situation), then NOLOCKis fine.

如果您不关心脏读（即在以 READ 为主的情况下），那就没问题了NOLOCK。

BUT, be aware that the majority of locking problems are due to not having the 'correct' indexes for your query workload (assuming the hardware is up to the task).

但是，请注意，大多数锁定问题是由于您的查询工作负载没有“正确”的索引（假设硬件可以完成任务）。

And the guru's explanation was correct. It is usually a band-aid solution to a more serious problem.

大师的解释是正确的。它通常是针对更严重问题的创可贴解决方案。

Edit: I'm definitely not suggesting that NOLOCK should be used. I guess I should have made that obviously clear. (I would only ever use it, in extreme circumstances where I had analysed that it was OK). AS an example, a while back I worked on some TSQL that had been sprinkled with NOLOCK to try and alleviate locking problems. I removed them all, implemented the correct indexes, and ALL of the deadlocks went away.

编辑：我绝对不是建议应该使用 NOLOCK。我想我应该清楚地说明这一点。（我只会在极端情况下使用它，在我分析过它可以的情况下）。举个例子，不久前我研究了一些 TSQL，这些 TSQL 已经撒上 NOLOCK 来尝试缓解锁定问题。我将它们全部删除，实现了正确的索引，所有的死锁都消失了。

回答by Gats

Doubt it was a "guru" who'd had any experience in high traffic...

怀疑这是一位“大师”，他在高流量方面有任何经验......

Websites are usually "dirty" by the time the person is viewing the completely loaded page. Consider a form that loads from the database and then saves the data that's edited?? It's idiotic the way people go on about dirty reads being such a no no.

当人们查看完全加载的页面时，网站通常是“脏的”。考虑一个从数据库加载然后保存编辑过的数据的表单？人们继续说脏读如此不，这是愚蠢的。

That said, if you have a number of layers building on your selects, you could be building in a dangerous redundancy. If you're dealing in money or status scenarios, then you need not only transactional data read/writes, but a proper concurrency solution (something most "gurus" don't bother with).

也就是说，如果您在选择上构建了许多层，那么您可能会构建危险的冗余。如果您正在处理金钱或状态场景，那么您不仅需要读取/写入事务数据，还需要适当的并发解决方案（大多数“大师”不会为此烦恼）。

On the other hand, if you have an advanced product search for a website (ie something that likely won't be cached and be a little intensive) and you've ever built a site with more than a few concurrent users (phenominal how many "experts" haven't), it is rediculous to bottle neck every other process behind it.

另一方面，如果您有一个网站的高级产品搜索（即可能不会被缓存并且有点密集的东西）并且您曾经建立了一个拥有多个并发用户的网站（现象有多少“专家”还没有），阻碍其背后的所有其他过程是可笑的。

Know what it means and use it when appropriate. Your database will almost always be your main bottle neck these days and being smart about using NOLOCK can save you thousands in infrastructure.

了解它的含义并在适当的时候使用它。如今，您的数据库几乎总是您的主要瓶颈，明智地使用 NOLOCK 可以为您节省数千个基础设施。

EDIT:It's not just deadlocks it helps with, it's also how much you are going to make everybody else wait until you're finished, or vice versa.

编辑：它不仅可以帮助解决僵局，还可以让其他人等到你完成，反之亦然。

Using NOLOCK Hint in EF4?

在 EF4 中使用 NOLOCK 提示？

回答by realMarkusSchmidt

None of the answers is wrong, however a little confusing maybe.

没有一个答案是错误的，但可能有点令人困惑。

• When querying single values/rows it's alwaysbad practise to use NOLOCK -- you probably never want to display incorrect information or maybe even take any action on incorrect data.
• When displaying rough statistical information, NOLOCK can be very useful. Take SO as an example: It would be nonsense to take locks to read the exactnumber of views of a question, or the exact number of questions for a tag. Nobody cares if you incorrectly state 3360 questions tagged with "sql-server" now, and because of a transaction rollback, 3359 questions one second later.

• 在查询单个值/行时，使用 NOLOCK总是不好的做法——您可能永远不想显示不正确的信息，甚至可能不想对不正确的数据采取任何行动。
• 在显示粗略的统计信息时，NOLOCK 非常有用。以 SO 为例：使用锁来读取问题的确切查看次数或标签的确切问题数是无稽之谈。没有人会在意您现在是否错误地陈述了标记为“sql-server”的 3360 个问题，并且由于事务回滚，一秒后出现了 3359 个问题。

回答by luckyluke

As a professional Developer I'd say it depends. But I definitely follow GATS and OMG Ponies advice. Know What You are doing, know when it helps and when it hurts and

作为一名专业的开发人员，我会说这取决于。但我绝对遵循 GATS 和 OMG Ponies 的建议。知道你在做什么，知道什么时候有用，什么时候伤害，

read hints and other poor ideas

阅读提示和其他糟糕的想法

what might make You understand the sql server deeper. I generally follow the rule that SQL Hints are EVIL, but unfortunately I use them every now and then when I get fed up with forcing SQL server do things... But these are rare cases.

什么可能让您更深入地了解 sql server。我通常遵循 SQL Hints 是 EVIL 的规则，但不幸的是，当我厌倦了强迫 SQL Server 执行某些操作时，我时不时地使用它们……但这些情况很少见。

luke

卢克

回答by richard101

When app-support wanted to answer ad-hock queries from the production-server using SSMS (that weren't catered for via reporting) I requested they use nolock. That way the 'main' business is not affected.

当 app-support 想要使用 SSMS 回答来自生产服务器的临时查询（这不是通过报告满足的）时，我要求他们使用 nolock。这样“主要”业务不受影响。

回答by user2041151

I agree with some comments about NOLOCK hint and especially with those saying "use it when it's appropriate". If the application written poorly and is using concurrency inappropriate way – that may cause the lock escalation. Highly transactional table also are getting locked all the time due to their nature. Having good index coverage won't help with retrieving the data, but setting ISOLATION LEVEL to READ UNCOMMITTED does. Also I believe that using NOLOCK hint is safe in many cases when the nature of changes is predictable. For example – in manufacturing when jobs with travellers are going through different processes with lots of inserts of measurements, you can safely execute query against the finished job with NOLOCK hint and this way avoid collision with other sessions that put PROMOTED or EXCLUSIVE locks on the table/page. The data you access in this case is static, but it may reside in a very transactional table with hundreds of millions of records and thousands updates/inserts per minute. Cheers

我同意一些关于 NOLOCK 提示的评论，尤其是那些说“在合适的时候使用它”的评论。如果应用程序编写得不好并且使用了不适当的并发方式——那可能会导致锁升级。由于其性质，高度事务性的表也一直被锁定。拥有良好的索引覆盖率无助于检索数据，但将 ISOLATION LEVEL 设置为 READ UNCOMMITTED 可以。此外，我相信在许多情况下，当更改的性质是可预测的时，使用 NOLOCK 提示是安全的。例如——在制造业中，当有旅行者的工作经历不同的过程并插入大量测量值时，您可以使用 NOLOCK 提示安全地对已完成的工作执行查询，这样可以避免与将 PROMOTED 或 EXCLUSIVE 锁放在表上的其他会话发生冲突/页。在这种情况下，您访问的数据是静态的，但它可能驻留在具有数亿条记录和每分钟数千次更新/插入的事务性很强的表中。干杯

回答by pwy

The better solutions, when possible are:

如果可能，更好的解决方案是：

• Replicate your data (using log-replication) to a reporting database.
• Use SAN snapshots and mount a consistent version of the DB
• Use a database which has a better fundamental transaction isolation level

• 将您的数据（使用日志复制）复制到报告数据库。
• 使用 SAN 快照并挂载一致版本的数据库
• 使用具有更好基本事务隔离级别的数据库

The SNAPSHOT transaction isolation level was created because MS was losing sales to Oracle. Oracle uses undo/redo logs to avoid this problem. Postgres uses MVCC. In the future MS's Heckaton will use MVCC, but that's years away from being production ready.

创建 SNAPSHOT 事务隔离级别是因为 MS 的销售额正在流失给 Oracle。Oracle 使用撤消/重做日志来避免这个问题。Postgres 使用 MVCC。未来 MS 的 Heckaton 将使用 MVCC，但距离生产就绪还需要数年时间。

回答by Andrew Hill

I believe that it is virtually never correct to use nolock.

我相信使用 nolock 实际上永远不会正确。

If you are reading a single row, then the correct index means that you won't need NOLOCK as individual row actions are completed quickly.

如果您正在读取单行，那么正确的索引意味着您不需要 NOLOCK，因为单个行操作可以快速完成。

If you are reading many rows for anything other than temporary display, and care about being able repeat the result, or defend by the number produced, then NOLOCK is not appropriate.

如果您正在为临时显示之外的任何其他内容读取许多行，并且关心能够重复结果，或者通过产生的数字进行防御，那么 NOLOCK 是不合适的。

NOLOCK is a surrogate tag for "i don't care if this answer contains duplicate rows, rows which are deleted, or rows which were never inserted to begin with because of rollback"

NOLOCK 是“我不在乎这个答案是否包含重复的行、被删除的行或由于回滚而从未插入的行”的代理标记

Errors which are possible under NOLOCK:

NOLOCK 下可能出现的错误：

• Rows which match are not returned at all.
• single rows are returned multiple times (including multiple instances of the same primary key)
• Rows which do not match are returned.

• 根本不返回匹配的行。
• 单行被多次返回（包括同一个主键的多个实例）
• 返回不匹配的行。

Any action which can cause a page split while the noLock select is running can cause these things to occur. Almost any action (even a delete) can cause a page split.

在 noLock select 运行时任何可能导致页面拆分的操作都可能导致这些事情发生。几乎任何操作（甚至删除）都可能导致页面拆分。

Therefore: if you "know" that the row won't be changed while you are running, don't use nolock, as an index will allow efficient retrieval.

因此：如果您“知道”在运行时不会更改该行，请不要使用 nolock，因为索引将允许高效检索。

If you suspect the row can change while the query is running, and you care about accuracy, don't use nolock.

如果您怀疑在查询运行时该行可以更改，并且您关心准确性，请不要使用 nolock。

If you are considering NOLOCK because of deadlocks, examine the query plan structure for unexpected table scans, trace the deadlocks and see why they occur. NOLOCK around writes can mean that queries which previously deadlocked will potentially both write the wrong answer.

如果您因为死锁而考虑 NOLOCK，请检查查询计划结构是否有意外的表扫描，跟踪死锁并查看它们发生的原因。写周围的 NOLOCK 可能意味着之前死锁的查询可能会写出错误的答案。