NOLOCK 还是不 NOLOCK? [英] To NOLOCK or not to NOLOCK?

问题描述

我曾在一个非常大的组织工作，他们不得不在大多数查询中使用 NOLOCK - 因为数据通常在白天通过 ETL 过程更新，并锁定应用程序的 40% 工作一天当然不是一个选择.

I've worked in a very large organization where they had to use NOLOCK on most queries - because data was commonly updated via ETL processes during the day and locking the application for 40% of the working day was certainly not an option.

出于习惯，在我的下一个地方，我继续在任何地方自动使用 NOLOCK.但是自从阅读警告和风险后，我一直在逐渐撤消此操作，没有指定表提示并让 SQL Server 来做这件事.

Out of habit, in my next place I went on to automatically use NOLOCK everywhere. But since reading warnings and risks, I've been gradually undoing this, to have no table hints specified and let SQL Server do it's thing.

但是，我仍然不舒服，因为我在做正确的事情.在我们使用 NOLOCK 的地方，我从未见过数据翻倍或损坏的数据.我在那里待了很多年.自从删除 NOLOCK 我遇到了行锁减慢/暂停查询的明显障碍，这给人一种我的数据库很慢或不稳定的错觉.实际上，它只是有人在某处运行长时间的保存(他们这样做的能力是应用程序的要求).

However, I'm still not comfortable I'm doing the right thing. In the place where we used NOLOCK, I never once saw data get doubled up, or corrupt data.. I was there for many years. Ever since removing NOLOCK I am running into the obvious obstacle of rowlocks slowing / pausing queries which gives the illusion that my DB is slow or flakey. When in actuality it's just somebody running a lengthy save somewhere (the ability for them to do so is a requirement of the application).

我很想听听在实践中实际经历过 NOLOCK 的数据损坏或数据重复的任何人，而不是那些根据他们在互联网上阅读的内容的人.如果有人可以提供复制步骤来看到这种情况发生，我将特别感激.我正在尝试衡量它的风险有多大，风险是否超过了能够与更新并行运行报告的明显好处?

I would be interested to hear from anyone who has actually experienced data corruption or data duplication from NOLOCK in practise, rather than people who are going by what they've read about it on the internet. I would be especially appreciative if anybody can provide replication steps to see this happen. I am trying to gauge just how risky is it, and do the risks outweigh the obvious benefit of being able to run reports parallel to updates?

推荐答案

我看到你已经阅读了很多关于它的内容，但请允许我向你指出使用 NOLOCK 的危险的一个很好的解释(就是这样 READ UNCOMMITTED隔离级别):SQL Server NOLOCK 提示&其他糟糕的想法.

I see you've read a lot about it, but allow me to point you to a very good explanation on the dangers of using NOLOCK (that's it READ UNCOMMITTED isolation level): SQL Server NOLOCK Hint & other poor ideas.

除此之外，我会做一些引用和评论.NOLOCK 最糟糕的部分是这样的:

Apart from this, I'll make some citations and comments. The worst part of NOLOCK is this:

它会产生极其难以重现"的错误.

It creates "incredibly hard to reproduce" bugs.

问题是，当你读取未提交的数据时，大部分时间都是提交的，所以一切正常.但是如果交易没有提交，它会随机失败.而这通常不会发生.对?不:首先，单个错误是一件非常糟糕的事情(您的客户不喜欢它).其次，事情可能会变得更糟，LO:

The problem is that when you read uncommited data, most of the time is commited, so everything is alright. But it will randomly fail if the transaction is not comitted. And that doesn't usually happen. Right? Nope: first, a single error is a very bad thing (your customer don't like it). And second, things can get much worse, LO:

问题在于事务不仅仅是更新行.他们通常需要更新索引或数据页上的空间不足.这可能需要分配新页面要移动的页面上的现有行，称为 PageSplit.您的选择可能会完全错过许多行和/或将其他行计数两次.在链接文章中有更多信息

The issue is that transactions do more than just update the row. Often they require an index to be updated OR they run out of space on the data page. This may require new pages to be allocated & existing rows on that page to be moved, called a PageSplit. It is possible for your select to completely miss a number of rows &/or count other rows twice. More info on this in the linked article

所以，这意味着即使您读取的未提交事务已提交，您仍然可以读取错误数据.而且，这会随机发生.好丑，好丑！

So, that means that even if the uncommited transaction you've read is committed, you can still read bad data. And, this will happen at random times. That's ugly, very ugly!

腐败呢?

正如 Remus Rusanu 所说，这不是硬"而是软"腐败.并且它会特别影响聚合，因为在更新它们时您正在阅读不应该阅读的内容.例如，这可能会导致帐户余额错误.

As Remus Rusanu said, it's not "hard" but "soft" corruption. And it affects specially aggregates, because you're reading what you shouldn't when updating them. This can lead for example to a wrong account balance.

您是否听说过具有重建帐户余额程序的大型 LOB 应用程序?为什么?它们应该在交易中正确更新！(如果在关键时刻重建余额是可以接受的，例如在计算税收时).

Haven't you heard of big LOB apps that have procedures to rebuild account balances? Why? They should be correctly updated inside transactions! (That can be acceptable if the balances are rebuilt at critical moments, for example while calcultaing taxes).

在不破坏数据的情况下我可以做什么(因此相对安全)?

假设当您不使用未提交的数据更新数据库上的其他现有数据时，读取未提交的数据是相当安全的".IE.如果您仅将 NOLOCK 用于报告目的(没有回写)，您就处于非常安全"的一面.唯一的小麻烦"是报表可能会显示错误的数据，但至少，数据库中的数据会保持一致.

Let's say it's "quite safe" to read uncommited data when you're not using it to update other existing data on the DB. I.e. if you use NOLOCK only for reporting purposes (without write-back) you're on the "quite safe" side. The only "tiny trouble" is that the report can show the wrong data, but, at least, the data in the DB will keep consistent.

是否安全取决于您阅读的内容.如果它是信息性的，不会用于决策，那是很安全的(例如，在最佳客户或最畅销产品的报告中出现一些错误并不是很糟糕).但是，如果您正在获取这些信息来做出决定，事情可能会更糟(您可能会在错误的基础上做出决定！)

To consider this safe depends on the prupose of what you're reading. If it's something informational, which is not going to be used to make decissions, that's quite safe (for example it's not very bad to have some errors on a report of the best customers, or the most sold products). But if you're getting this information to make decissions, things can be much worse (you can make a decission on a wrong basis!)

特殊体验

我开发了一个拥挤"的应用程序，大约有 1,500 个用户使用 NOLOCK 读取数据，修改它并在数据库(一家 HHRR/TEA 公司)上更新它.而且(显然)没有问题.诀窍是每个员工读取原子数据"(一个员工的数据)来修改它，两个人同时读取和修改相同的数据几乎是不可能的.除了这个原子数据"没有影响任何聚合数据.所以一切都很好.但是报告区时不时出现问题，用NOLOCK读取聚合数据".因此，必须在没有人在数据库中工作的时刻安排关键报告.非关键报告的小偏差被忽视和承认.

I worked on the development of a 'crowded' application, with some 1,500 users which used NOLOCK for reading data, modifying it an updating it on the DB (a HHRR/TEA company). And (apparently) there were no problems. The trick was that each employee read "atomic data" (an employee's data) to modify it, and it was nearly impossible that two people read and modified the same data at the same time. Besides this "atomic data" didn't influence any aggregate data. So everything was fine. But from time to time there were problems on the reporting area, which read "aggregated data" with NOLOCK. So, the critical reports had to be scheduled for moments where noone was working in the DB. The small deviations on non-critical reports was overlooked and admittable.

现在你知道了.你没有任何借口.你决定，NOLOCK或不NOLOCK

这篇关于NOLOCK 还是不 NOLOCK?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！