May 31, 2012 at 8:23 am
I know that the use of NOLOCK can be a very bad thing in most cases, but I am curious about the how it actually functions versus not using the hint. For example, it makes "logical" sense to me that with the hint the engine does not have to "check" for locks. If it does not have to even check for the locks, shouldn't it be "faster" even on a table that does not have any locks?
Again, I know the issues with using NOLOCK, but I want to use this question to understand how the engine checks for locks and does that in and of itself affect performance?
NOTE: I have just ordered Kalen Delaney's book on 2008 Internals, I just don't know if this will be covered and thought this was a good place to frame up the question.
Jared
CE - Microsoft
May 31, 2012 at 8:41 am
It still has to obey schema modification locks but it doesn't need to take out any other locks. As such, much of the waiting on the lock logic is bypassed. An application I used to work with used nolock liberally. When they partnered with Microsoft to review the code that got flagged and in a lab got removed. Even without concurrency there was a significant performance hit just do to the time needed to process locks.
May 31, 2012 at 9:08 am
It's probably worth mentioning that if you are using NOLOCK then you should be making the switch to using READUNCOMMITTED as this is the replacement for NOLOCK.
The problem with NOLOCK is that you can get strange results back when you attempt to read data that is being updated, in some cases you can see the before and after update returned in the same recordset.
Whether the ability to see "dirty reads" is a problem for you depends on what your application is.
For example I used it with an IF EXISTS(SELECT 1 FROM dbo.MyTable). There were no updates, only inserts and my requirements were to test if the record existed in any shape or form.
May 31, 2012 at 9:09 am
I use the hint liberally in a data warehouse environment used for reporting. Using it bypasses most of the locking checks and most importantly doesnt escalate locks even from SELECT's into schema locks when the query engine decides it would be more efficient. In a reporting environment where I know the data is static, I do not need the locking considerations.
Anything else and I have to keep a jaundiced eye with regards to using it. If you are concerned about dirty reads, then this hint is probably not for you.
May 31, 2012 at 9:12 am
The problem with NOLOCK is that you can get strange results back when you attempt to read data that is being updated, in some cases you can see the before and after update returned in the same recordset.
Whether the ability to see "dirty reads" is a problem for you depends on what your application is.
Thanks for the response. I am familiar with the issues that can be caused in terms of errors and accuracy of the results, this is simply a theory question to understand how locks are checked.
Jared
CE - Microsoft
May 31, 2012 at 9:13 am
The only place I ever used in a production code was loading data warehouse, where full control of data modifications was guaranteed: nothing could possibly make any change in a source tables during the process. We never had problem with duplicates rows...
Also, it can be used in life-reporting (in production) which doesn't require to be 100% accurate.
May 31, 2012 at 9:15 am
Effect of NOLOCK is the same as SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
It just individually applied to the table and query, while setting isolation level makes it for every table in every query for a connection session
May 31, 2012 at 9:19 am
David.Poole (5/31/2012)
It's probably worth mentioning that if you are using NOLOCK then you should be making the switch to using READUNCOMMITTED as this is the replacement for NOLOCK.The problem with NOLOCK is that you can get strange results back when you attempt to read data that is being updated, in some cases you can see the before and after update returned in the same recordset.
Whether the ability to see "dirty reads" is a problem for you depends on what your application is.
For example I used it with an IF EXISTS(SELECT 1 FROM dbo.MyTable). There were no updates, only inserts and my requirements were to test if the record existed in any shape or form.
Per NOLOCK and READUNCOMMITTED are the same. Including the possibility of dupe records because of uncommitted rows.
Again, in a high transaction situation, this really should not be used for obvious reasons.
May 31, 2012 at 9:21 am
I think Itzik Ben-Gan explores what you're asking about here
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply