November 10, 2011 at 7:32 am
Hello Gurus and Ninjas,
I have a question pertaining to the use of WITH(NOLOCK). I had bot really ever seen this until I started working for my current company. It is pretty much mandatory that this be used on all read only queries here. We spoke a bit within our team yesterday about this and I was the only one who seemed concern. Now, it may just be our business rules... However, when I brought up cases from my previous employer trying to demonstrate a problem of a dirty read, I really could not find a practical example of how it could change interpretation of the data. NOTE: We did not use any explicit transactions ,they were all implicit.
Can somebody help illustrate to me from their own experiences, in a practical sense, where a dirty read would "really" affect the outcome?
Thanks,
Jared
Jared
CE - Microsoft
November 10, 2011 at 7:42 am
dirty reads = bad data. It's flat out automatic that it will happen. How often? Your guess is as good as mine.
Moreover there's an inevitable case where the connection will be killed because of data movement, so you need to do a retry logic in your procedure calls.
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Another one from Paul Randal. I'm still searching for something else from him, but in the mean time =>
http://sqlskills.com/BLOGS/PAUL/post/Performance-bug-NOLOCK-scans-involving-off-row-LOB-data.aspx
November 10, 2011 at 7:45 am
In case you need to bust other myths =>
November 10, 2011 at 7:48 am
Or there's also
http://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-iii
or even
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
Unfortunately, many people think that NOLOCK means Magically Return My Data Faster With No Side Effects Whatever - and it can be rether hard to get them to let go of this belief
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
November 10, 2011 at 7:49 am
If they can't be bothered to read the articles - Kendra Little's got a webcast on this very issue on
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
November 10, 2011 at 7:52 am
Consider a Financial Database.
Per Financial Rules, a financial transaction always has 2 effects i.e. Debit (+) & Credit (-). Now if you are repaying your loan (say Auto Loan) amount it should deduct the amount from your Savings A/c and deposit the same in Loan A/c.
With the dirty reading, you may get a report which might indicate deduction from your account BUT no deposits into loan a/c.
If it’s acceptable to your managers, please feel free to implement NOLOCK. 😀
November 10, 2011 at 8:53 am
I think it depend on how often the data does change. If it is updated, let's say, once a day I would see no issue in using With(NoLock).
Regards,
Iulian
November 10, 2011 at 9:00 am
Iulian -207023 (11/10/2011)
I think it depend on how often the data does change. If it is updated, let's say, once a day I would see no issue in using With(NoLock).Regards,
Iulian
Then put the table on readonly filegroup. You'll save all the locking overhead of the server.
I've heard it can make a heck of a difference on busy systems. Mine is too unbusy to be worth testing!
November 10, 2011 at 9:00 am
Iulian -207023 (11/10/2011)
I think it depend on how often the data does change. If it is updated, let's say, once a day I would see no issue in using With(NoLock).
If it's updated once a day, what's the point of using NoLock? It's not a 'go faster' switch, it allows a query to read through exclusive locks. If the table isn't going to be updated often, there won't be exclusive locks.
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 10, 2011 at 9:14 am
What is happening if for example there is one table that updates on a daily basis,
another table that updates every hour and there is a query that joins the two tables.
What scenario make sens for using With(NoLock)?
Regards,
Iulian
November 10, 2011 at 9:17 am
Iulian -207023 (11/10/2011)
What scenario make sens for using With(NoLock)?
When you don't care if the data is inaccurate. Something like a dashboard where the graphs show aprox values only may be fine.
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 10, 2011 at 9:22 am
Iulian -207023 (11/10/2011)
What is happening if for example there is one table that updates on a daily basis,another table that updates every hour and there is a query that joins the two tables.
What scenario make sens for using With(NoLock)?
Regards,
Iulian
I've heard of another.
Imagine the session table of a website. The PK is a GUID and is unique per session (unless being hacked but that's another story).
Since you filter on the PK for every operations on the table, nolock makes sense because it improves concurrence and CAN'T return wrong data.
Obsiouly, read only for that table is impossible.
IIRC, that's the only scenario that makes sense aside for approximate results.
November 10, 2011 at 9:36 am
Iulian -207023 (11/10/2011)
What is happening if for example there is one table that updates on a daily basis,another table that updates every hour and there is a query that joins the two tables.
What scenario make sens for using With(NoLock)?
Regards,
Iulian
In our case, we are reporting on average calls per hour, day, month, etc. We take in about 2 million calls each hour so dirty reads are acceptable. My concern is with the potential of timeouts as mentioned in one of the other threads. Frequently, we get error messages from our sip servers that SQL Server timed out. They use FreeTDS to interact with SQL, which could be the problem in and of itself. However, I am wondering now if some of these are related to the NOLOCK issue.
Jared
Jared
CE - Microsoft
November 10, 2011 at 9:36 am
I'm always amused these discussions as "best practice" says we should always provide committed accurate data but as always it depends. It goes against the grain for many.
You should always provide a solution as needed and accepted by your business. In mine, the data is dynamic and changes on the frequently but deadlocks and locking is unacceptable. So for "metadata" nolock is used frequently. Customer account data never. Accounts based data needs to be accurate and should always read committed data only.
Its a given that with(nolock) will not increase system performance generally but it will generally help to alleviate blocking.
November 10, 2011 at 1:29 pm
Thank you Ninja's_RGR'us and GilaMonster for the very good examples: charts with aproximate values and session tables.
Jared I don't think that NOLOCK can generate the timeouts you mention but to be sure it might worth checking the locks with sp_lock, sp_who and sp_who2.
Regards,
Iulian
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply