sql server - To NOLOCK or not to NOLOCK? -

i've worked in large organization had use nolock on queries - because data commonly updated via etl processes during day , locking application 40% of working day not option.

out of habit, in next place went on automatically use nolock everywhere. since reading warnings , risks, i've been gradually undoing this, have no table hints specified , let sql server it's thing.

however, i'm still not comfortable i'm doing right thing. in place used nolock, never once saw data doubled up, or corrupt data.. there many years. ever since removing nolock running obvious obstacle of rowlocks slowing / pausing queries gives illusion db slow or flakey. when in actuality it's running lengthy save somewhere (the ability them requirement of application).

i interested hear has experienced data corruption or data duplication nolock in practise, rather people going they've read on internet. appreciative if can provide replication steps see happen. trying gauge how risky it, , risks outweigh obvious benefit of being able run reports parallel updates?

i see you've read lot it, allow me point explanation on dangers of using nolock (that's read uncommitted isolation level): sql server nolock hint & other poor ideas.

apart this, i'll make citations , comments. worst part of nolock this:

it creates “incredibly hard reproduce” bugs.

the problem when read uncommited data, of time commited, alright. randomly fail if transaction not comitted. , doesn't happen. right? nope: first, single error bad thing (your customer don't it). , second, things can worse, lo:

the issue transactions more update row. require index updated or run out of space on data page. may require new pages allocated & existing rows on page moved, called pagesplit. possible select miss number of rows &/or count other rows twice. more info on in linked article

so, means if uncommited transaction you've read committed, can still read bad data. and, happen @ random times. that's ugly, ugly!

what corruption?

as remus rusanu said, it's not "hard" "soft" corruption. , affects specially aggregates, because you're reading shouldn't when updating them. can lead example wrong account balance.

haven't heard of big lob apps have procedures rebuild account balances? why? should correctly updated inside transactions! (that can acceptable if balances rebuilt @ critical moments, example while calcultaing taxes).

what can without corrupting data (and relatively safe)?

let's it's "quite safe" read uncommited data when you're not using update other existing data on db. i.e. if use nolock reporting purposes (without write-back) you're on "quite safe" side. "tiny trouble" report can show wrong data, but, @ least, data in db keep consistent.

to consider safe depends on prupose of you're reading. if it's informational, not going used make decissions, that's quite safe (for example it's not bad have errors on report of best customers, or sold products). if you're getting information make decissions, things can worse (you can make decission on wrong basis!)

a particular experience

i worked on development of 'crowded' application, 1,500 users used nolock reading data, modifying updating on db (a hhrr/tea company). , (apparently) there no problems. trick each employee read "atomic data" (an employee's data) modify it, , impossible 2 people read , modified same data @ same time. besides "atomic data" didn't influence aggregate data. fine. time time there problems on reporting area, read "aggregated data" nolock. so, critical reports had scheduled moments noone working in db. small deviations on non-critical reports overlooked , admittable.

now know it. have no excuses. decide, nolock or not nolock