April 16, 2012 at 1:32 am
Session A:
Select * from mytable (NoLOck)
What does this query exactly do..
Option 1: If there is some other query running on Session B that has locked rows of myTable, Does the above query still read the uncommited data..??
or
Option 2:Does above query read the rows without locking the data in myTable itself and hence any other query on Session B might make updates to the table
Option 1 or option 2??
April 16, 2012 at 1:44 am
NOLOCK is equivalent of READ UNCOMMITTED.
The very least of your worries is the possibility of reading "dirty" data (data that was inserted or updated then subsequently rolled-back). A much worse problem, though harder to reproduce, is where your query causes a page split due to an index is update or running out of space on a data page. This makes it possible to select a row multiple times or to just skip it completely.
Personally, I've never come across a place where using NOLOCK is a good idea (but I've also not worked with any SQL Server prior to 2005).
April 16, 2012 at 3:11 am
itskanchanhere (4/16/2012)
Option 1 or option 2??
Both are true.
The query reads uncommitted data and any other session is able to update, insert or delete rows that the select query is busy reading.
Nolock is not a good idea in most cases.
See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
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
April 16, 2012 at 3:34 am
Thanks..
April 16, 2012 at 3:39 am
It's probably worth mentioning that locking hints are not the way to solve blocking problems in a database.
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
April 16, 2012 at 4:06 am
Nolock is not a good idea in most cases.
Absolutely, agree. In OLTP system will be hard to find justifications for using this hint.
But this hint does exist and there are some cases where it's fine to use.
Just few come in my mind:
1. When accuracy is not important.
2. ETL to upload datawarehouse, where you know that the source tables do not experience any modifications during the load.
3. And the most common one: reporting based on static/history data.
When you use NOLOCK you need to understand what type of table scan will be performed (allocation vs range) and how to control it, (allocation scans will lead to missing or double counting records during page splits).
YOU SHOULD NEVER USE IT TO "FIX" DEADLOCK OR OTHER LOCKING PROBLEMS!
April 16, 2012 at 4:18 am
Eugene Elutin (4/16/2012)
2. ETL to upload datawarehouse, where you know that the source tables do not experience any modifications during the load.3. And the most common one: reporting based on static/history data.
Sure, that's safe to use nolock on, but it's completely pointless to do so. If there are no changes then there will be no locks and hence there's no point in using nolock as there will be no locks to read through. You don't gain anything.
There's not a huge overhead in taking locks, and if you want the allocation order scan, the tablock hint is just as valid.
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
April 16, 2012 at 4:33 am
... If there are no changes then there will be no locks ...
Does not SELECT puts a Shared Lock on a table?
I agree that placing the lock itself doesn't take a lot of time, however it does still take some...
April 16, 2012 at 4:46 am
Eugene Elutin (4/16/2012)
... If there are no changes then there will be no locks ...
Does not SELECT puts a Shared Lock on a table?
I agree that placing the lock itself doesn't take a lot of time, however it does still take some...
Yes, it does. What I means was if there are no changes there will be no other locks for nolock to ignore.
If nolock has a noticable performance impact solely from not taking locks, then there is something else wrong with your server...
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply