Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

NOLOCK Hint Corrupts Results from SELECT Expand / Collapse
Author
Message
Posted Wednesday, November 7, 2012 2:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:47 PM
Points: 40, Visits: 451

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.
Post #1382180
Posted Thursday, November 8, 2012 11:27 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:19 PM
Points: 577, Visits: 6,288
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


----------------------------------
My journal of things I'm learning about SQL
Post #1382656
Posted Thursday, November 8, 2012 11:45 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1382666
Posted Thursday, November 8, 2012 4:32 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:48 PM
Points: 1,973, Visits: 2,919
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1382765
Posted Sunday, November 11, 2012 11:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:47 PM
Points: 40, Visits: 451

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.
Post #1383457
Posted Monday, November 12, 2012 12:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:21 AM
Points: 2,837, Visits: 3,954
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
Post #1383529
Posted Monday, November 12, 2012 12:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:47 PM
Points: 40, Visits: 451
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.
Post #1383824
Posted Monday, November 12, 2012 12:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 8:51 AM
Points: 132, Visits: 581
That seems a bit strange...can you check the collation between the two servers?
Post #1383835
Posted Tuesday, November 13, 2012 11:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:47 PM
Points: 40, Visits: 451
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.
Post #1384220
Posted Friday, November 16, 2012 1:33 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 11:45 AM
Points: 317, Visits: 823
Very strange indeed.
Try..
dbcc opentran

To see if you have any open transactions that you may not even be aware of.
Post #1385848
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse