October 8, 2013 at 10:42 am
Hello,
I have a package that has a script component source and an OLEDB source. Both sources are sorted and the properties are set accordingly. I have Merge Join with script source as the left input and the OLEDB source as the right. I'm doing a full outer join. I'm trying to do some change detection with this package.
In the OLEDB source I had a nolock hint. When I ran the package I noticed that the rows from the OLEDB source seemed to coming to Merge Join "late". The script component would be almost 1,000,000 rows in and the OLEDB source would only be at 9912 rows. Then all of sudden the OLEDB source would catch up. This resulted in rows that should have been seen as being matched were not. When I removed the nolock hint everything worked the way it should.
I have used nolock hints before and never seen this behavior. I am running SQL 2012.
Thanks.
October 8, 2013 at 10:55 am
October 8, 2013 at 11:54 am
Keith Tate (10/8/2013)
If everything is working fine without the NOLOCK hint why do you still want to use it? NOLOCK has the potential to get you "dirty" matches.
NOLOCK has far worse potential than dirty reads. It can ( and will ) return missing and/or duplicate data. There is no way to know when this will happen and it is very difficult to reproduce bugs like this.
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply