April 26, 2013 at 12:40 pm
I've done a lot of reading on using WITH NOLOCK and understand the pitfalls. We have a situation where our nightly UPDATE process will get blocked by somebody. The other day it was a spotfire query that wasn't doing anything - but for some reason had kept a connection open since 12 hours before. A few weeks ago it was a PC SAS query that the user had killed but again for some reason had kept a connection to the database and held a lock which caused our nightly process to wait all night until we killed the transaction the next morning.
We don't allow any updates during the day. So one argument of dirty reads if using WITH NOLOCK won't be an issue. We are not trying to use WITH NOLOCK to fix concurrency problems of transactions not committing either. It's just these random "read" transactions that will get hung for some reason and keep a lock.
Would our idea of coding all VIEW's using the WITH NOLOCK work in preventing these errors? All access to our database is done via views. So anybody reading during the day (with PC SAS, spotfire, etc...) would not be taking any locks. If they got hung for some reason, there would not be any locks to get in the way of our nightly UPDATE process.
Looking for opinions? We don't really have any way of testing because we can never recreate these hung transaction situations.
April 26, 2013 at 1:08 pm
john.p.lantz (4/26/2013)
I've done a lot of reading on using WITH NOLOCK and understand the pitfalls. We have a situation where our nightly UPDATE process will get blocked by somebody. The other day it was a spotfire query that wasn't doing anything - but for some reason had kept a connection open since 12 hours before. A few weeks ago it was a PC SAS query that the user had killed but again for some reason had kept a connection to the database and held a lock which caused our nightly process to wait all night until we killed the transaction the next morning.We don't allow any updates during the day. So one argument of dirty reads if using WITH NOLOCK won't be an issue. We are not trying to use WITH NOLOCK to fix concurrency problems of transactions not committing either. It's just these random "read" transactions that will get hung for some reason and keep a lock.
Would our idea of coding all VIEW's using the WITH NOLOCK work in preventing these errors? All access to our database is done via views. So anybody reading during the day (with PC SAS, spotfire, etc...) would not be taking any locks. If they got hung for some reason, there would not be any locks to get in the way of our nightly UPDATE process.
Looking for opinions? We don't really have any way of testing because we can never recreate these hung transaction situations.
You should run away from this idea as quickly as possible. NOLOCK is a LOT more than dirty reads. It can and will produce duplicates and/or missing data.
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/%5B/url%5D
You should instead look into isolation.
http://msdn.microsoft.com/en-us/library/ms173763.aspx
_______________________________________________________________
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/
April 26, 2013 at 1:11 pm
Short answer is yes, it will fix the immediate concerns. However, I'd recommend using Snapshot Isolation instead as your repair to this concern. It's a powerful tool and should get you equivalent 'repairs' without the dangers of NOLOCK.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 26, 2013 at 6:15 pm
I will look the suggestions. But let me explain our situation a little more. All updates are done to the tables - not the views. So any updating of the database will be using all of the locking capabilities of SQL Server. Only SELECT's will be using the views that have WITH NOLOCK. The only thing we are trying to fix is the read-only transactions taking locks. So it seems like a reasonable solution, but I will certainly take the advice of the experts on this forum.
April 26, 2013 at 6:50 pm
john.p.lantz (4/26/2013)
I will look the suggestions. But let me explain our situation a little more. All updates are done to the tables - not the views. So any updating of the database will be using all of the locking capabilities of SQL Server. Only SELECT's will be using the views that have WITH NOLOCK. The only thing we are trying to fix is the read-only transactions taking locks. So it seems like a reasonable solution, but I will certainly take the advice of the experts on this forum.
Whether you use views or go directly to table, the lock problem is the same. Snapshot Isolation was actually built because all the dangers of NOLOCK caused the gurus to demand something as a better alternative. RCSI (Read Committed Snapshot Isolation) was the answer. It uses more tempdb resources, but you don't risk getting damaged, missing, duplicated data with it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply