SQL Server NOLOCK 为授权而运行的查询
在我们的应用程序登录过程中，运行了几个查询，都是围绕验证登录的。 在评估它们时，我注意到其中一个查询是在没有 NOLOCK 提示的情况下运行的。
脏读似乎没有任何特别的危险，因为数据几乎不会改变。
从某人一次又一次尝试登录失败的尝试 DOS 类型攻击的角度来看，我认为缺乏 NOLOCK 会降低我们的失败阈值。
我相信这是 DOS 攻击极不可能的结果（我认为 Web 服务器会首先发生），但添加 NOLOCK 应该会使它从不可能变成不可能。
那么，我到底是太过分了，还是太过分了呢？
During the course of our application login there are several queries ran, all around validating the login. In evaluating them I noticed that one of the queries is run without the NOLOCK hint.
There does not seem to be any particular danger of dirty read because the data would hardly ever change.
Thinking about it from an attempted DOS type attack by somebody attempting failed logins over and over again I am suggesting that the lack of NOLOCK lowers our threshold for failure.
I believe it is an extremely unlikely result of a DOS attack (I think the web server would go first) but adding NOLOCK should make it go from unlikely to impossible.
So, am I being excessive or trivial?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
对于针对服务器的 DoS 尝试，您最不需要担心的是是否具有 NOLOCK。
我不会担心的。
如果，正如你所说，数据很少改变，那么在那里使用 NOLOCK 可能不会有什么坏处。
Having NOLOCKs or not is the least of your worries with a DoS attempt against your server.
I wouldn't sweat it.
If, as you say, the data rarely changes, having the NOLOCKs there probably don't hurt.
是的，你太琐碎了。
如果您遭受 DOS 攻击，那么 SQL 授权调用上的 NOLOCK 是您最不用担心的。 实施一些 DOS 检测、故障跟踪+限制，甚至一些不会影响用户但会减慢攻击速度的计划暂停...
Yes, you are being excessively trival.
If you are exposed to DOS attacks, NOLOCK on the SQL authorization call is the least of your worries. Implement some DOS detection, failure tracking+throttle, even some planned pauses that wouldn't effect the user but would slow down an attack...
如果您频繁调用该查询，尤其是在更广泛的事务中，NOLOCK 可能会提高您的性能。
考虑脏读的性质 - 可能发生脏读的时间窗口是否真正至关重要？ 例如，您可以在授权角色中添加或删除某人。
在添加场景中，脏读尝试会失败。 （访问被拒绝）
在删除场景中，脏读将在该尝试中起作用。 （授予访问权限）
如果数据通过手动操作（例如人机交互）更改 - 它们的“延迟”裕度通常比您的数据库高得多/不确定！
The NOLOCK could potentially improve your performance if you call that query frequently especially in broader transactions.
Consider the nature of the dirty read - would the time window where that can occur be truely critical? e.g. where you add or remove someone from an authorized role.
In the add scenario a dirty read would fail on that attempt. (access denied)
In the remove scenario a dirty read would work on that attempt. (access granted)
If the data changes through manual operation e.g. human interaction - their margin for "latency" is typically much higher/indeterminate than your databases!
这也是一种非常罕见的情况，但仍然是：就在有人停用用户以阻止他们登录的那一刻，NOLOCK 让他们进入。可能是需要立即锁定的流氓用户/黑客/员工？
您必须担心这种特殊情况才能放弃 NOLOCK 的性能优势。
Also a very rare case, but still: Just at the moment somebody deactivates the user to prevent them from logging in, NOLOCK lets them in. Could be a rogue user/hacker/employee who needs to be locked out immediately?
You would have to be concerned about this particular scenario to forgo the performance advantage of NOLOCK.
通过查看表的权限，您可以更好地保护表。 像这样的表不应允许任何直接访问，所有访问都应来自存储过程以及对其设置的权限。
You would protect your table far better by looking at the permissions to it. A table like this should not allow any direct access, all access should be from stored procs and the permissions set on them instead.