<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
T-SQL (SS2K5)
»
With Nolock
19 posts, Page 1 of 2
1
2
»»
With Nolock
Rate Topic
Display Mode
Topic Options
Author
Message
AMJ-458987
AMJ-458987
Posted Wednesday, October 21, 2009 7:04 AM
SSC Veteran
Group: General Forum Members
Last Login: Friday, May 17, 2013 2:18 PM
Points: 289,
Visits: 260
Hi All,
I am trying these queries -
SELECT TOP 10 * FROM Table1
select top 10 * from Table1 WITH (NOLOCK)
Both the queries will give different result set.
But when i used order by with them the result set will be the same.
Is this related to Isolation level in SQL Server or anything else?
I am very much confused.
Waiting for reply.
MJ
Post #806456
Garadin
Garadin
Posted Wednesday, October 21, 2009 7:28 AM
SSCommitted
Group: General Forum Members
Last Login: Wednesday, November 07, 2012 4:08 PM
Points: 1,525,
Visits: 4,047
(NOLOCK) allows dirty reads. Basically, there is a chance that if you are reading data out of the table while it is in the process of being updated, you could read the wrong data. You can also read data that has been modified by transactions that have not been committed yet as well as a slew of other problems.
Best practice is not to use NOLOCK unless you are reading from tables that really don't change (such as a table containing states) or from a data warehouse type DB that is not constantly updated.
Seth Phelabaum
Consistency is only a virtue if you're not a screwup.
Links
:
How to Post Sample Data
::
Running Totals
::
Tally Table
::
Cross Tabs/Pivots
::
String Concatenation
Post #806480
GSquared
GSquared
Posted Wednesday, October 21, 2009 7:38 AM
SSCoach
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442,
Visits: 9,571
In the case of data that doesn't change, that's better off in a read-only database or read-only file. SQL Server won't even attempt to take locks on read-only data, and thus avoids the processing overhead involved. The performance boost is minor, but in a busy system it can make a difference.
With(nolock) has some uses very occassionally. Inappropriate use of it is the single most common error I've ever seen in database devs. Most don't understand that it can result in all kinds of junk data being used in important business processes.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #806491
AMJ-458987
AMJ-458987
Posted Wednesday, October 21, 2009 11:13 PM
SSC Veteran
Group: General Forum Members
Last Login: Friday, May 17, 2013 2:18 PM
Points: 289,
Visits: 260
It means what i am getting dirty or uncommitted records from second query (select top 10 * from Table1 With(NOLOCK).
Actually i am confused because of order by clause. When i use order by both the queries will give me same result set.
Reason why i am stuck in this situation is that we are improving the performance of our application using WITH (NOLOCK). During this process i got two different result set from the similar queries .
My question is that will it affect my work or not? If i get two different result sets.
Or it is better not to use WITH (NOLOCK) in select statement.
MJ
Post #806944
Dave Ballantyne
Dave Ballantyne
Posted Thursday, October 22, 2009 1:22 AM
SSCommitted
Group: General Forum Members
Last Login: Today @ 3:42 AM
Points: 1,943,
Visits: 8,228
AMJ-458987 (10/21/2009)
When i use order by both the queries will give me same result set.
Probably the rows in the dirty read wont be in the top 10 of your ordered results.
As you have discovered nolock can be a very dangerous beast.
Take a look at this link
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
Clear Sky SQL
My Blog
Kent user group
Post #806970
GSquared
GSquared
Posted Thursday, October 22, 2009 7:07 AM
SSCoach
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442,
Visits: 9,571
Using With(Nolock) can improve performance, at the cost of reliability and integrity.
The question to ask on that one is: If you were a manager, and had an employee who was really, really fast at his job, but who lied to you, broke things, and sabotaged the work of his co-workers, would you keep him around? If the answer is yes, then with(nolock) is for you.
The thing you're seeing with Top without an Order By is that SQL Server (and any normal database) can give you whatever it wants in that case. What you're doing is telling it, "give me the most convenient 10 rows". Since one query honors locks and the other doesn't, they're going to have different definitions of "most convenient". That's not so much an issue with "with(nolock)" as it is an issue with unordered Top commands. You never know what you're going to get with one of those.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #807116
ranjitrjha
ranjitrjha
Posted Thursday, October 22, 2009 7:15 AM
SSC Rookie
Group: General Forum Members
Last Login: Saturday, May 18, 2013 3:25 PM
Points: 41,
Visits: 35
Mithilesh,
I tried a similar query. I do agree that NOLOCK gives uncommited data but very strangely when I was trying the queries, there was no other user/activity...so i dont expect any thing uncommited. Can anybody tell the reason for different result set when there is no update/lock on table.
Post #807130
GSquared
GSquared
Posted Thursday, October 22, 2009 7:17 AM
SSCoach
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442,
Visits: 9,571
ranjitrjha (10/22/2009)
Mithilesh,
I tried a similar query. I do agree that NOLOCK gives uncommited data but very strangely when I was trying the queries, there was no other user/activity...so i dont expect any thing uncommited. Can anybody tell the reason for different result set when there is no update/lock on table.
Because an unordered Top statement gives you whatever comes up. It doesn't have consistency, because it's not required to.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #807132
ranjitrjha
ranjitrjha
Posted Thursday, October 22, 2009 7:24 AM
SSC Rookie
Group: General Forum Members
Last Login: Saturday, May 18, 2013 3:25 PM
Points: 41,
Visits: 35
GSquared,
Your explanation makes sense but when i am running
select top 10 * from table1
select top 10 * from table1
then for both queries, result set is same.
It's only when I put a nolock hint as in queries below, the result set differs.
select top 10 * from table1
select top 10 * from table1 with (nolock)
Post #807142
GSquared
GSquared
Posted Thursday, October 22, 2009 8:21 AM
SSCoach
Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442,
Visits: 9,571
That's simply due to the fact that the software is essentially lazy. The first query, run multiple times, is likely to pull the same data, because both the plan and the data are cached. Even if you clear the cache, you're likely to get the same data on multiple runs of the same query, because of the physical implementation of the database and the fact that the devs didn't add in random number generators just for the heck of it.
The point is that you are likely to get the same data. You aren't guaranteed to get the same data.
Thus, a minor, seemingly immaterial change in the query, is likely to get different results. Again, it might, or it might not.
The point is that you don't know what you'll get. It might give you the same results 10,000 times in a row, and then on the 10,001st, give you something different. No way to know.
Since most businesses don't want to get that kind of unpredictability out of their data, it's better to force the issue. That means ordering your query if you want the top X rows. It means avoiding "nolock" unless you have a real business reason to allow dirty reads. Has a lot of other ramifications, but those are definitely two of them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #807211
« Prev Topic
|
Next Topic »
19 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
