問題描述

我曾在一個非常大的組織工作，他們不得不在大多數查詢中使用 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?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！