September 24, 2008 at 2:59 am
Using WITH(NOLOCK) with select statement...what are the pros and cons of it ? Does it have any effect on performance ? when we should use it and when not ?
September 24, 2008 at 3:26 am
When SQL Server reads a row from a table, it applies a SHARED lock on the row (under default transaction isolation level: READ COMMITTED). If the row is already locked by another transaction, your query will wait till the row is unlocked.
By applying NOLOCK, you are instructing SQL Server not to apply locks while reading the rows. The advantage is that, you get better performance. The negative side is that this can cause a DIRTY READ (You might be reading data which is not yet committed by other transactions. Those transactions may rollback and your data will become invalid).
I would suggest reading this article: http://www.sqlserverandxml.com/2008/08/sql-server-transaction-isolation-level_29.html
.
September 24, 2008 at 7:48 am
anjali.vishwakarma (9/24/2008)
Using WITH(NOLOCK) with select statement...what are the pros and cons of it ? Does it have any effect on performance ? when we should use it and when not ?
Jacob gave a pretty good answer. The pat answer as to when you should use it is never, but that's probably unrealistic. In reality it probably should not be anything you use by default, but when you have long running query that is causing other issues due to the locking you can apply it, but, as Jacob states, you need to be sure that Dirty Reads are acceptable.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 24, 2008 at 12:46 pm
"Dirty Reads" is also a bit misleading. It is possible to miss data and read the same data more than once.
For example, if you query a list of people on an index ordered by name, if someone updates "Abby" to "Warren" during your query, it is possible that you will read the record as "Abby" and also as "Warren" if the record is moved in the index during your query. The opposite is also possible. This gets increasing complicated if your query reads a non-clustered index and then does a bookmark lookup on a clustered index since the record could possibly be in several different places during your query execution.
When using that query hint, you really need to consider what the data could be doing during your query or you can get really misleading results.
I tend to recommend snapshot isolation as a solution to blocking problems as it will always give you consistent data. You just have to manage the additional overhead.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy