June 2, 2014 at 11:34 am
I have a question. If running a batch that specifies the isolation levels as "Committed", yet the views its querying hints "NoLock" which wins..??
I'm wondering if were having issues with code thats auto generating "Read Committed" and overwriting the "nolock" hints..
June 2, 2014 at 11:37 am
Why are you allowing the use of NOLCK hints? You are aware of the potential data issues that can occur from using dirty reads, aren't you?
June 2, 2014 at 11:58 am
I don't want to pile on here but a view is the absolute worst possible place you could use NOLOCK. There really is no good place but a view is just terrible. As Lynn eluded to, are you aware that you can and will have duplicate and/or missing data when you use that hint? It is far more inaccurate than simply reading data that isn't yet committed. Take a look at these articles which all discuss this particular hint and just how awful it really is.
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/
June 2, 2014 at 12:15 pm
I've been a Snr DBA for 10+ years and know what the ramifications are, but that wasnt my question. I didnt setup this database/system and did change the Db isolation level based on usage. The previously purchased BI tool defaults to COMMITTED and was locking tables; once discovered views were added then later nolock before I got here, it did drastically reduce locking, and how their db is populated dirty reads arent an issue.
My question was NOT data quality related, it's locking issues and what wins or if there is any conflict at all, or in what order those are applies. In some systems most restrictive wins...
June 2, 2014 at 12:27 pm
digdave7 (6/2/2014)
I've been a Snr DBA for 10+ years and know what the ramifications are, but that wasnt my question. I didnt setup this database/system and did change the Db isolation level based on usage. The previously purchased BI tool defaults to COMMITTED and was locking tables; once discovered views were added then later nolock before I got here, it did drastically reduce locking, and how their db is populated dirty reads arent an issue.My question was NOT data quality related, it's locking issues and what wins or if there is any conflict at all, or in what order those are applies. In some systems most restrictive wins...
Nobody was questioning how long you have been a senior DBA. You would be surprised how many well seasoned people use that hint on a regular basis and have no idea what it actually does or the ramifications behind it. I am going to guess that you don't fully understand it either because it is not limited to how the database is populated. Dirty reads are very often misunderstood. It is far more than just reading in an insert or an update that hasn't yet completed. It also is when page splits occur and data is being moved between pages. This is when it is the most dangerous.
That aside it is pretty simple to recreate the situation and test it. Create a view with NOLOCK. Then begin a transaction and insert to that view/base table. Then in another window set your isolation to READ COMMITTED and select from the view/base table.
_______________________________________________________________
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/
June 2, 2014 at 12:36 pm
Also, since it is looking like you are using SQL Server 2008 or later there are better alternatives to allowing dirty reads. One of the biggest reasons people add the nolock hint to keep writers from blocking readers. Starting with SQL Server 2005 you can now use READ COMMITTED SNAPSHOT. It makes more use of tempdb but allows readers to run unblocked by writers.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply