November 9, 2015 at 2:06 pm
Hi,
We have a database with isolation level 'Read committed Snapshot on'....and there are views in it having some lock issues occassionally....my question is if I use 'WITH NOLOCK' for tables in views ....will it override the isolation level 'Read committed snapshot on' ...am guessing it will override..correct???
if the answer is yes , what will be the risk of using 'WITH NOLOCK'?
Thanks for your time.
November 9, 2015 at 2:12 pm
We use NOLOCK in all of our TSQL that doesn't require an INSERT, UPDATE or DELETE. But we use it mostly for reporting, so generally it doesn't matter if a transaction is in process at the time.
As I understand it, if you need to take the snapshot at the end of a day/month/year etc... for critical reporting (financials for instance) at exactly 12midnight, and need to know what your balance is or something - you would still want to lock the tables.
If it isn't -that- critical it has up to the second data, or your updates aren't happening multiple times a second, using NOLOCK will speed up access for everyone.
Somebody else correct me if I'm incorrect, I'm new to this stuff 🙂
November 9, 2015 at 2:14 pm
Yes, the hint will override the isolation level.
Since read committed snapshot already takes no read locks, using nolock gets you all of the disadvantages of that isolation level (dirty reads, duplicate reads, missing rows) and none of the benefits. Remove it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 9, 2015 at 2:17 pm
GilaMonster (11/9/2015)
Yes, the hint will override the isolation level.Since read committed snapshot already takes no read locks, using nolock gets you all of the disadvantages of that isolation level (dirty reads, duplicate reads, missing rows) and none of the benefits. Remove it.
So if we aren't using snapshot isolation, the nolock is still beneficial? Or is it overall just a bad thing to do?
November 9, 2015 at 2:18 pm
andrew.leef (11/9/2015)
But we use it mostly for reporting, so generally it doesn't matter if a transaction is in process at the time.
So your users are perfectly happy getting reports that have duplicate data or missing data? Have you asked them? Have you looked and seen how many bugs get filed for 'incorrect report', usually closed as 'can't reproduce' or similar?
If it isn't -that- critical it has up to the second data, or your updates aren't happening multiple times a second, using NOLOCK will speed up access for everyone.
No, it won't. Nolock is NOT a performance tuning feature. It's telling SQL Server that you want to trade off correctness for concurrency. Potentially more concurrency (though reads don't block reads) and less correctness.
In a test I did recently, with concurrent updates, my select queries (which summed columns that were *unaffected* by the updates) returned incorrect results 59% of the time.
Think your users would be happy if you told them that their reports should be correct 40% of the time?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 9, 2015 at 2:30 pm
GilaMonster (11/9/2015)
andrew.leef (11/9/2015)
But we use it mostly for reporting, so generally it doesn't matter if a transaction is in process at the time.So your users are perfectly happy getting reports that have duplicate data or missing data? Have you asked them? Have you looked and seen how many bugs get filed for 'incorrect report', usually closed as 'can't reproduce' or similar?
If it isn't -that- critical it has up to the second data, or your updates aren't happening multiple times a second, using NOLOCK will speed up access for everyone.
No, it won't. Nolock is NOT a performance tuning feature. It's telling SQL Server that you want to trade off correctness for concurrency. Potentially more concurrency (though reads don't block reads) and less correctness.
In a test I did recently, with concurrent updates, my select queries (which summed columns that were *unaffected* by the updates) returned incorrect results 59% of the time.
Think your users would be happy if you told them that their reports should be correct 40% of the time?
We don't have any problems with duplicate data in our reports - and we have the highest level of accuracy we have ever had, because we are using stored procedures to pull the dataset (using NOLOCK). However, those reports don't need to be exactly up to date. If someone is currently changing a purchase order from "inbound" to "received", we don't need to know instantly about that one record - it is more general "we have 37 inbound POs" With that being said, we are still fixing a lot of code from previous developers, and we have never (still don't) have a full time DBA.
I'm just starting to learn, and not in charge of writing reports. I'm a Business Analyst starting to learn SQL Server because I need to project manage an ERP upgrade w/ SQL server migration... NOLOCK is prevalent here - because they think it helps reduce deadlocks and long running reports, and I thought it was a good thing because they use it. Guess not! Thanks for the clarification. Looks like I'll be doing a lot more reading. (edit: and stopping posting help unless I really understand something)
November 9, 2015 at 2:38 pm
Thank you for your time..Gail & Andrew!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply