January 30, 2012 at 1:16 am
Dears, I knew the problem of dirty-reads with 'WITH (NOLOCK)', but I heard from few people
saying putting NOLOCK is not a good practice sometimes.
As i was not convinced, asking you people..
Appreciating your Help Always..
January 30, 2012 at 1:41 am
DBA Rafi (1/30/2012)
Dears, I knew the problem of dirty-reads with 'WITH (NOLOCK)', but I heard from few peoplesaying putting NOLOCK is not a good practice sometimes.
As i was not convinced, asking you people..
NOLOCK gives you a best-effort quick view of the data, but it doesn't come with many consistency guarantees. For example, you might read the same data row more than once, miss some rows completely, or even read corrupted column values (if the data is split across pages, for example large data types like XML, text, ntext, image, and the MAX types).
Nine times out of ten you are better off with standard READ_COMMITTED, or a row-versioning isolation level like READ_COMMITTED_SNAPSHOT, which also does not usually take shared locks to read data, but does guarantee a consistent view of committed data.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 30, 2012 at 5:11 am
Plus the absolute entertainment of queries occasionally failing with error 601
No, using nolock on every single query 'just because' is not good practice. There are places it's fine and if it's used appropriately after proper consideration there's nothing wrong. If it's used everywhere because someone said so, probably someone who doesn't even know what it does, it's not fine.
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
January 30, 2012 at 5:26 am
GilaMonster (1/30/2012)
Plus the absolute entertainment of queries occasionally failing with error 601No, using nolock on every single query 'just because' is not good practice. There are places it's fine and if it's used appropriately after proper consideration there's nothing wrong. If it's used everywhere because someone said so, probably someone who doesn't even know what it does, it's not fine.
I absolutely agree with this; and would add that I have rarely (if ever) seen the NOLOCK table hint used properly. It is pretty rare to genuinely need to override the isolation level for just one table in a query. If READ UNCOMMITTED is the required isolation level, it should probably be set using a SET TRANSACTION ISOLATION LEVEL command rather than using table hints. Anyone who has had to go through legacy code removing NOLOCKs one by one will attest to this.
Code that has NOLOCK on every single table in every single query is an infallible indicator of poor coding, in my experience.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 30, 2012 at 7:32 am
SQL Kiwi (1/30/2012)
GilaMonster (1/30/2012)
Plus the absolute entertainment of queries occasionally failing with error 601No, using nolock on every single query 'just because' is not good practice. There are places it's fine and if it's used appropriately after proper consideration there's nothing wrong. If it's used everywhere because someone said so, probably someone who doesn't even know what it does, it's not fine.
I absolutely agree with this; and would add that I have rarely (if ever) seen the NOLOCK table hint used properly. It is pretty rare to genuinely need to override the isolation level for just one table in a query. If READ UNCOMMITTED is the required isolation level, it should probably be set using a SET TRANSACTION ISOLATION LEVEL command rather than using table hints. Anyone who has had to go through legacy code removing NOLOCKs one by one will attest to this.
Code that has NOLOCK on every single table in every single query is an infallible indicator of poor coding, in my experience.
I'll say "ditto" on this one. Read Uncommitted isolation is better than NoLock except in a very few cases, and both should be avoided except where it's been positively proven that the impact is minimized.
Amusingly enough, I've actually see the NoLock hint used on table variables used in a cursor definition, with the justification that "NoLock will make the cursor faster". The cursor was global, updatable, et al.
The point being, many/most of the people using NoLock all over the place are doing Cargo Cult programming. They don't understand why NoLock can sometimes be used to speed something up, so they build airports out of palm fronds ... I mean they use NoLock all over the place with no understanding of it.
- 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
January 30, 2012 at 8:24 am
Kindly,
1. I knew the problem of dirty read. So, if i am OK with the regards. I shall very well go for this. Correct?
Because, i have started an exercise to put NOLOCK in all the APPLICABLE (,where all the dirty read is OK) places in project.
2. I did not use the ISOLATION LEVELs because, as the APPLICABILITY on Tables differs within a single SP.
3. "error 601", looks one among an issue, what i feared other than dirty read. Is there anything else like this?
January 30, 2012 at 8:27 am
What problem are you trying to solve here?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 30, 2012 at 8:36 am
DBA Rafi (1/30/2012)
Because, i have started an exercise to put NOLOCK in all the APPLICABLE (,where all the dirty read is OK) places in project.
Why oh why oh why?????
Are your users really happy if their reports return different data on different executions? If their reports show duplicate data for supposedly unique columns? Really?
Why are you looking to do this? I can't personally think of any good reason to take this kind of step. If locks are a problem and some basic performance tuning doesn't help (it often does), then there are two optimistic concurrency models that don't take locks and also won't have the kind of data anomalies that read uncommitted allows.
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
January 30, 2012 at 8:40 am
SQL Kiwi (1/30/2012)
...Code that has NOLOCK on every single table in every single query is an infallible indicator of poor coding, in my experience.
I mostly agree with this, except that it is also often an indicator of poor physical database design leading to massive blocking and/or deadlocking.
January 30, 2012 at 8:49 am
SQL Kiwi (1/30/2012)
What problem are you trying to solve here?
oh c'mon I like the previous version of this post better.
January 30, 2012 at 8:59 am
patrickmcginnis59 (1/30/2012)
oh c'mon I like the previous version of this post better.
Yes but the humour might not have translated too well, so I thought better of it.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 30, 2012 at 10:31 am
What are the implications of setting "READ_COMMITTED_SNAPSHOT" to ON rather than OFF (the default)?
I seem to recall looking into this before (ages ago) because it sounded attractive but I discovered some some drawbacks...
January 30, 2012 at 10:49 am
Rob-350472 (1/30/2012)
What are the implications of setting "READ_COMMITTED_SNAPSHOT" to ON rather than OFF (the default)?I seem to recall looking into this before (ages ago) because it sounded attractive but I discovered some some drawbacks...
I would go with READ_COMMITTED_SNAPSHOT before going with using NOLOCK.
It does make more use of tempdb for the row version store, but I have never personally seen much problem with that.
January 30, 2012 at 10:52 am
Rob-350472 (1/30/2012)
What are the implications of setting "READ_COMMITTED_SNAPSHOT" to ON rather than OFF (the default)?I seem to recall looking into this before (ages ago) because it sounded attractive but I discovered some some drawbacks...
The main drawback is heavier use of tempdb. How heavy depends on the frequency and size of update and delete operations.
- 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
January 30, 2012 at 10:56 am
As another potential drawback for NoLock, I've actually seen it crash a server, requiring database repair.
A shared connection (from a web server, of course) ran into two concurrent missing data page errors, tried to close the same connection twice, ran into a problem on that, and the database server cratered and the database ended up having to be restored from backups.
But I've never heard of anyone else running into this, so it's probably rare enough to ignore as a real risk. After all, your server room and your DR co-location could be simultaneously hit by meteors, but the risk is low enough to ignore. Same probably applies here.
- 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
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply