December 13, 2006 at 5:26 am
Every company I have been in there has been debate over use use of NOLOCKS. Being more of a web programmer than a SQL programmer could someone tell me or give me a link to explain the benefits of the use of NOLOCKS as a 'Standard/Best Practice/Default' rather than 'Use it when you need it', i.e. Dirty reads?
The debate offend centers on performance and you should use NOLOCKS always because it is quicker, should I?
Regards
Billy
December 13, 2006 at 11:23 am
It depends on how reliable you want the results to be.
Using NOLOCK, you're query can sometimes double-read rows, skip rows, do phantom reads, dirty reads etc.
Yes it is faster, and doesn't block but just be aware of the pitfalls because it will cause problems sometimes.
You're posting to the SQL2005 forum, so I'll assume you're running 2005. Use READ_COMMITTED_SNAPSHOT which gives you the last committed value so you get a consistent result from your query (no multi/missing counts) and you can still avoid any blocking.
SQL guy and Houston Magician
December 13, 2006 at 4:41 pm
If I am not mistaken.. to use READ_COMMITTED_SNAPSHOT you have to enable it first which is server configuration not the db configuration and it has its own pros and cons...
If you want dirty data I don't see any problem using NOLOCK but I will adivise you to use SET TRANSACTION ISOLATION LEVEL READ_UNCOMMITTED because it will effect complete batch where as NOLOCK effects on only one table...
If you want committed data don't use it...
SQL Server 2005 Row Versioning-Based Transaction Isolation
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5snapshotisol.asp
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://www.sql-server-performance.com/lock_contention_tamed_article.asp
MohammedU
Microsoft SQL Server MVP
December 14, 2006 at 11:52 am
NOLOCK is like a chainsaw, it's very useful but also dangerous. I don't think I would put it on any list of standards or best practices if those recommendations would be forced on people who don't understand what they're doing.
I wouldn't say use it if you want dirty data, I would say use it if you are sure there will not be any problems (such as reading a static reference table), or the application will not be significantly affected by possible inaccuracies and will handle any errors. When it is safe to use, NOLOCK can boost performance and concurrency greatly.
December 14, 2006 at 12:07 pm
Last PASS conference Kalen Delaney or some other MVP was mentioning to be very careful (don't use) while using NOLOCK...
Using the NOLOCK decision is totally upto your own decision based on your busness/company needs...
MohammedU
Microsoft SQL Server MVP
December 14, 2006 at 1:13 pm
I'm on the other side of the fence. It is in the standards i have written for developers to use. I require the use of nolock except in cases where we specifically want to avoid dirty reads of data, such as when dealing with financial data.
If I worked for a bank or for the IRS, i would not use it at all. But I don't.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply