AnsweredAssumed Answered

SQLServer use of NOLOCK

Question asked by Dan Keeley on Sep 28, 2018
Latest reply on Sep 28, 2018 by Joao Figueiredo

So, it turns out in SQLServer if a row is locked (in a transaction etc) then it also blocks queries by default.

 

To me that's kind of astonishing, as it means queries in PDI and in the front end will appear to hang, pretty much randomly.

 

In fact, i only discovered this because our legacy app servers make extensive use of the

 

WITH (NOLOCK)

 

Syntax - Turns out, this is exactly why.

 

But; I'm not really a fan of putting hints into SQL queries, especially not routinely. So do others do this? or is there a better way?

 

You can read detail about it here:

 

https://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/

 

Thanks,

Dan

 

PS: There's an even more bizarre thing - if you use NOLOCK you can read dirty data, because it sends you the UNCOMITTED data.  That's what the article says anyway, which frankly is just astonishing, so much so i can't help wondering if I've misunderstood it!

Outcomes