SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


NOLOCK Discussion


NOLOCK Discussion

Author
Message
bcronce
bcronce
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 517
It is my understanding that nolock only helps when you have a write lock on a row/table. C# has something simular for accessing objects in a multithreaded environment. You can have as many 'read locks' as you want, but as soon as a 'write lock' comes in, your read locks wait until everything is commited. By putting in the nolock on a read, you by-pass the 'wait for clean data' and just read as-is.

If I'm wrong, let me know, because I thought this is how it worked.

edit: does a query have to check every row to see if it's locked or does nolock only speed up a fixed check then any waiting on writes?

eg. hypothetically, would a table that will never get written to see a scaling performance increase or a fixed; is a lock a one time check or does it have to be checked per row, every single time?
parackson
parackson
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 112
That's what I thought as well... So except for where exact reporting is required, if you do your job right on the SQL syntax and DB design a NOLOCK statement can only help in CRM environments...

Thats where my experience is, I have not dealt with extremely high transaction enviroments.
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12083 Visits: 18567
bcronce (9/4/2008)
It is my understanding that nolock only helps when you have a write lock on a row/table. C# has something simular for accessing objects in a multithreaded environment. You can have as many 'read locks' as you want, but as soon as a 'write lock' comes in, your read locks wait until everything is commited. By putting in the nolock on a read, you by-pass the 'wait for clean data' and just read as-is.

If I'm wrong, let me know, because I thought this is how it worked.

edit: does a query have to check every row to see if it's locked or does nolock only speed up a fixed check then any waiting on writes?

eg. hypothetically, would a table that will never get written to see a scaling performance increase or a fixed; is a lock a one time check or does it have to be checked per row, every single time?


I think Grant hit on the point pretty well. If it DOES help that much, then there's some other underlying issue you're glossing over.

To me NOLOCK is the equivalent of a medic treating someone, saying:"Now I'm going to ignore the gunshot wound you have, and just fix everything ELSE, okay?"

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86028 Visits: 45226
Honestly, in my opinion, if you do your job right on the SQL syntax, DB design and indexing a NOLOCK statement shouldn't be necessary in any environment.

If you're encountering severe locking problems in SQL 2005 or higher, use SNAPSHOT isolation. That way reads never take locks, but don't read inconsistent data. It's the way Oracle works (more or less). The downside is that TempDB usage increases.

Yes, there will be the occasional case where nolock is necessary. The problem is that it is vastly overused with people adding it to every query without the slightest knowledge of what it actually means or the possible problems they could be causing for themselves.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


noeld
noeld
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9486 Visits: 2048
NOLOCK does have its uses! The main problem is that many tend to abuse it or use it where is not really appropriate.


* Noel
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23217 Visits: 9730
I might see Nolock or Readpast as moderately useful in a Where Exists statement in a trivial issue. Even there, I'd be concerned about it, since it's hard to tell if something "trivial" will turn into something seriously non-trivial somewhere down the road.

I have used index hints a few times, mainly in testing. Add five or six indexes to a table, force certain queries to use each one, test IO and CPU time and such, decide which one is best and then get rid of the other indexes and the hints.

A very few times, I've found that index and join hints have helped, when one proc might return a small number of rows some of the time, and a huge number other times, and code maintenance precludes separate procs with separate execution plans. I'd rather run an unnecessary hash join on a small data set in order to make the large data set run that one instead of a nested loop.

I have one proc, for example, that runs 5-10 rows half the time, and over 10-million rows the other half of the time, up to 53-million rows about 10% of the time. I tested recompile vs forcing a hash join and found the hash join hint ended up with the best average performance. Also had to force certain indexes, also based on average performance testing. Pushed the small data sets up to about 1 second total processing time, up from half a second, but moved the large data sets down to 18 minutes from 3+ hours, and the huge data sets down to 1-2 hours from up to 3 days. In that case, after extensive testing, I'm using the hints I came up with. But that's an extreme case. (This is an automated process on a critical workflow. There isn't a user sitting there waiting for a web page to finish loading or anything like that.) And there isn't a single Nolock in that process, since it absolutely cannot have dirty reads for business reasons.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
noeld
noeld
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9486 Visits: 2048
Just for an example I had to find some "bad" dates (.... "24:00:00") that crippled a some random rows in a large table (500+ million rows) and locking the table was simply not an option, lock escalation was prohibitive and creating an index was not possible... "Nolock" to the rescue!


* Noel
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38961 Visits: 32616
I remain where I was. Any of the hints can be useful in a given situation. My primary issue is when people see it as a general fix or best practice for lots of places or even everywhere. I've seen it done with various hints, NOLOCK, FAST N, KEEP FIXED PLAN... Every one one of them appeared to "fix" the underlying problem, bad code, bad indexing, bad table design, in the short term. Every one of them, when applied universally, proved to be extremely costly in the long run. NOLOCK starts returning bad data. Duplicate rows, in any system, are an issue. As are missing rows. Both these can be cased by NOLOCK. It's not just a question of getting 'NY' instead of 'New York' because an update is in progress.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Mark Stacey
Mark Stacey
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 160
The one place where I frequently use with (nolock) is in our datawarehousing ETLs - I KNOW that nothing else is hitting my staging database while my ETL is loading - I'm writing, then I'm reading.
If anything else is hitting the database, I have bigger problems.

Also MAXDOP - theoretically SQL knows best, but I've come across many instances where the parallelisation that it performs is nowhere near optimal.
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38961 Visits: 32616
Yeah, MAXDOP has been useful situationally. Although if I see a lot of queries suffering from parallelism, I'll raise the threshold.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search