NOLOCK(Sql Server 提示)是不好的做法吗? [英] Is the NOLOCK (Sql Server hint) bad practice?

问题描述

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

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

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

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 专家帮助我处理一些随机代码并注意到我的 sql 代码中的所有 NOLOCKS.我被礼貌地责骂，他试图向我解释(为什么这不是一件好事)，我有点迷路了.我觉得他的解释的本质是'这是一个更严重问题的创可贴解决方案......特别是如果你正在经历僵局.因此，解决问题的根源'.

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'.

我最近在谷歌上搜索了一下，发现 这篇文章.

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

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

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

推荐答案

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

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

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

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.

我建议阅读 当快照隔离有帮助时，它何时会受到伤害 - MSDN 建议使用在大多数情况下，阅读提交的快照而不是快照.

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

这篇关于NOLOCK(Sql Server 提示)是不好的做法吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！