May 21, 2008 at 6:40 am
Hi,
In my last shop we were instructed to use WITH (NOLOCK) in each SELECT, and was never really given an explaination as to why. Now at a new job, none of my fellow developers use it in their SELECTs and I can't begin to explain to them why they might.
Can anyone explain what the difference between
SELECT * FROM EMPLOYEES
and
SELECT * FROM EMPLOYEES WITH (NOLOCK)
is?
Many thanks
May 21, 2008 at 7:03 am
This runs a query as a READUNCOMMITTED isolation level (http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/2007week16.aspx).
It allows you to read changed data as it doesn't set up locks on the tables, essentially dirty reads. Not necessarily a problem, but you should be aware of this if you have long transactions, or you frequently see transactions rolled back.
May 21, 2008 at 7:33 am
Actually, according to Itzik Ben Gan and some pretty convincing tests (he gave the presentation at the last PASS Summit), NOLOCK can result in duplicate and incorrect data because it will pick up the data as it gets rearranged from page splits and what have you. It's more than just reading uncommitted data.
Your new shop sounds like they've got things going in the right direction.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 21, 2008 at 7:45 am
WITH (NOLOCK) = WITH (Occationally_Inconsistant_Data) 🙂
Nolock is something you use if you don't mind seeing dirty data, potantially missing rows and possibly reading rows twice. It's probably fine for long-running aggregate reports and the like
I find that lot of people (my former DB architect included) think that NOLOCK reads the last committed value (what snapshot actually does)
It's not something that should be applied to every select statement without thinking
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
May 21, 2008 at 8:35 am
The page split thing is an interesting one and it makes sense. You're allowing read of dirty data and page splits, and re indexing, would have uncommitted transactions in terms of data being moved to new locations.
Hopefully you wouldn't get duplicates, but I could see this happening.
May 21, 2008 at 8:45 am
Itzik was doing it on demand. It wasn't hard either. He just had to get the queries to run against similar sets of data and he could force the issue over and over again with very small and quick queries. It was creepy.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 21, 2008 at 9:19 am
Wow you guys are awesome. Thanks.
May 21, 2008 at 10:24 am
Steve Jones - Editor (5/21/2008)
The page split thing is an interesting one and it makes sense. You're allowing read of dirty data and page splits, and re indexing, would have uncommitted transactions in terms of data being moved to new locations.
It had more to do with the allowed scan types from the storage engine.
For any isolation level other than read uncommitted, only a index-order scan is permitted to read the entire table (clustered index scan)
This means that SQL will find the first leaf page in the index (as defined by the lowest value for the clustered index key) and will follow the next page pointers on each page to move through the index.
Since you're following in index key order, there's no chance that a page split will cause you to miss or double read pages. A page currently being read cannot be split. Nolock still results in page latches taken to protect the physical integrity
In read uncommitted, a page-order scan is allowed. In this case, SQL doesn't touch the higher levels of the b-tree, but uses the IAM pages to get the physical location of the pages within the file. This order may have nothing in common with the index order of the pages (especially on a fragmented index)
Since the order of the pages read has nothing to do with the logical order of the index key, it is possible that a page split of a page not yet read can allocate a page earlier in the file (rows skipped) or a page split of a page that has been read can allocate a page much later in the file (duplicate reads)
Way to tell which you're got - check out the properties of the scan in the execution plan. If Ordered=true, it's an index order scan. If order = false, it's a page order scan.
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
May 21, 2008 at 10:55 am
Specifying the use of NOLOCK on every query is a mistake made by a lot of people that simply do not understand what the problems of dirty reads can be.
It is a good sign that your new shop does not use it and you should learn to properly design and query databases to make it completely unnecessary.
May 25, 2008 at 6:03 pm
Michael Earl (5/21/2008)
It is a good sign that your new shop does not use it and you should learn to properly design and query databases to make it completely unnecessary.
NOLOCK can absolutely be useful at times, like when SQL Server's row locking behavior is completely unecessary. I refer now to data warehousing applications in which data is only written in batches at night, and only retrieved during daytime business hours. Because there is a clear delineation between when data is written and when it is read in this example, locking is completely unnecessary during the day. In this situation dirty reads are a non-issue and performance is a major issue.
May 26, 2008 at 12:33 am
The company I work for uses a copy of the production database for end user queries that is updated every morning after the pervious nights batch run. I have requested all users to use NOLOCK as they should not be locking tables and only production support would ever update the database when doing datafixes they need to test before applying this in production. NOLOCK and "disconnect after query executes" option set to True in this type of situation certainly improves performance.
May 26, 2008 at 2:42 am
I was not using with no lock, once i have to face a problem that in multi-user environment if someone begin a transaction on any table like employee is used in any transaction with any user then no one can select anything from that table until that user complete his transaction
some of my friends explain that it bring uncommented data so if you don’t want then you can use with(ReadPast) it will allow you to select locked data but only hide rows that are being changed
for any further queries you can mail me
May 26, 2008 at 2:54 am
A better solution would be to keep transactions short so that fancy locking hints aren't necessary.
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
May 26, 2008 at 8:51 am
I agree... shared locks on a set of reporting tables aren't much more expensive than using a WITH (NOLOCK), and WITH (NOLOCK) will not prevent deadlocks. Don't forget that WITH (NOLOCK) only affects SELECT's. WITH (NOLOCK) should never be used to overcome what bad code does. It's cheaper, in the long run, to fix the bad code. Keep transactions short... and keep Select's out of the transaction if at all possible. Practice good set based programming techniques even if it is for the neccessary RBAR of GUI code inserts and updates.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 26, 2008 at 11:48 am
Sounds like your transactions are holding locks way too long. You should look at shortening the transactions up by moving as much processing as possible outside of the transaction. NOLOCK and READPAST aren't fixes for poorly designed transactions.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply