Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


NOLOCK Hint Corrupts Results from SELECT


NOLOCK Hint Corrupts Results from SELECT

Author
Message
Langston Montgomery
Langston Montgomery
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 508
Good day fellow SQL Server Enthusiasts,

I'm having an issue with the NOLOCK query hint causing erroneous joins. In a nutshell, I'm running a select statement on a table that joins to a couple other tables (one table has about 9M+ rows, the other two are small). I can guarantee that no other process is running on the server, and the tables are already fully committed. When I add the NOLOCK hint, the query literally joins rows that are not equal, and returns the wrong results.

If I run the same query with the NOLOCK hint, the result set is fine.

I know there's lots of talk around not using the NOLOCK hint, but I thought it was okay if none of the tables being used in the SELECT statement are affected by other DML statements. Indeed, I realize that if no other process is running then there's no need to consider NOLOCK in the first place, but I stumbled upon this quandary recently, and was wondering if anyone had any insight as to why this happens.
Andrew Kernodle
Andrew Kernodle
SSChasing Mays
SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)

Group: General Forum Members
Points: 614 Visits: 8135
This article sounds like it has insights on the issue you're experiencing:

http://www.sqlservercentral.com/blogs/stratesql/2012/06/18/the-side-effect-of-nolock/

If my understanding of the dirty reads process is correct, even if all transactions at a given time have been committed, page splits may still occur for a short time afterwards. If you query a table using NOLOCK during that time period and a page split occurs, the data will be shuffled around throughout the table to accomodate the split, and as such, you may end up with data returned from the NOLOCK operation that corresponds to a location in memory that doesn't hold the data you originally wanted. My understanding might be a little off, but the article should be much more succint :-)

- :-D
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14385 Visits: 9729
Is there never any activity in any of the tables (other than Selects), or is the "window of no activity" short? Like a table loaded by a nightly ETL job, and you running queries on it hours after the job is done. Versus "I checked sp_who2 and there weren't any other active SPIDs while I was running my query", or something comparable to that.

You could easily be running into an issue with data that hasn't hit a checkpoint yet, if I understand the interactions between checkpoints and NoLock correctly.

Regardless, simply assume that if you use NoLock, or Read Uncommitted Isolation, that you are courting dirty reads, and assume they will happen regardless of what else you think may or may not be going on in the database when you're running a particular query. It's like speeding when you can't see any cops around: You have to assume you could get caught, but you decide to take the risk anyway because you think it's probably safe "right this moment". Same kind of risk as NoLock.

- 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
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3954 Visits: 6691
Langston Montgomery (11/7/2012)
When I add the NOLOCK hint, the query literally joins rows that are not equal, and returns the wrong results.


Something else is going on there. If you specified an equi-join, SQL will not suddenly start joining genuinely unequal rows whether NOLOCK is specified or not.

Is it possible that the join column(s) are being modified? (?)

Otherwise, please walk back thru the actual columns values and verify this. If you find it is continuing, please post the specific data.

I'm assuming that you're joining on exact data types and that you're considered things like upper and lower case, collation, etc..

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Langston Montgomery
Langston Montgomery
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 508
Hi Everyone,

Thank you all for the replies. I can always count on this site for assistance.

I'm embarrassed to say that it turns out to be a version issue. We ran the same code on a different server with 2008 R2 SP1, and it ran fine. The initial server is still on the RTM version of 2008 R2.
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2930 Visits: 4076
Langston Montgomery (11/11/2012)
I'm embarrassed to say that it turns out to be a version issue. We ran the same code on a different server with 2008 R2 SP1, and it ran fine. The initial server is still on the RTM version of 2008 R2.
but still ?? how that different version gives diferent/incorrect data ? or you want to say that you have different code/data on both servers

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Langston Montgomery
Langston Montgomery
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 508
Bhuvnesh (11/12/2012)
Langston Montgomery (11/11/2012)
I'm embarrassed to say that it turns out to be a version issue. We ran the same code on a different server with 2008 R2 SP1, and it ran fine. The initial server is still on the RTM version of 2008 R2.
but still ?? how that different version gives diferent/incorrect data ? or you want to say that you have different code/data on both servers


The code and the data is exactly the same between both servers. One server is production and the other is UAT. We refresh UAT from production every week. On the RTM version of '08 R2, when using the NOLOCK hint, it returns bad rows with values that don't match between fields of an equi-join. After removing the NOLOCK hint, it works fine.

On the SP1 version of '08 R2, the query returns the correct results regardless of NOLOCK.

I'm just going to chalk it up as an issue that is corrected with the right version of SQL.
richykong
richykong
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 621
That seems a bit strange...can you check the collation between the two servers?
Langston Montgomery
Langston Montgomery
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 508
richykong (11/12/2012)
That seems a bit strange...can you check the collation between the two servers?


I agree, Richy. It's very strange. Here are the collations for each server:

Production: SQL_Latin1_General_CP1_CI_AS
UAT: SQL_Latin1_General_CP1_CI_AS

Database (production): SQL_Latin1_General_CP1_CI_AS
Database (UAT): SQL_Latin1_General_CP1_CI_AS

The servers are basically cloned as we deploy everything from UAT to production after final sign off from users. The other thing worthy of note is that the fields of the equi-join that are returning rows even though the values don't match are integers (bigint).

I had to run this issue by several folks in I.T. We're all stunned and baffled. It works fine on SP1, though.
MMartin1
MMartin1
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1124 Visits: 2015
Very strange indeed.
Try..
dbcc opentran


To see if you have any open transactions that you may not even be aware of.

----------------------------------------------------
How to post forum questions to get the best help
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