SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


IS (with nolock) is useful in (multiple join update) Query!!


IS (with nolock) is useful in (multiple join update) Query!!

Author
Message
Ivan Mohapatra
Ivan Mohapatra
Old Hand
Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)

Group: General Forum Members
Points: 379 Visits: 495
hi all,

IS (with nolock) useful in (multiple join update) Query!!
below is an raw example of the update statements.
UPDATE xx
set x=e.x
y=e.y,
z=e.Z,
A=e.A,
B=e.B,
F=e.F
OUTPUT inserted.F,inserted.A,inserted.Z,inserted.Y INTO XXZ
FROM XX
JOIN ##EEE e ON e.A = XX.A
JOIN YY Y with (nolock) ON e.Y =Y.Y
JOIN ZZ Z with (nolock) ON e.Z =Z.Z
JOIN BB B with (nolock) ON e.F =B.F
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23849 Visits: 9730
Beyond the fact that NoLock is almost always a really bad idea, here's the data on it: http://msdn.microsoft.com/en-us/library/ms187373.aspx

Microsoft specifically states that NoLock (and Read Uncommitted) are ignored for the target of Update/Insert statements, and that the use of these hints in From clauses on those actions will be removed in a future edition of SQL Server and don't use them.

Most often, Read Committed Snapshot Isolation is a better solution than NoLock.

- 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
Alexander Suprun
Alexander Suprun
Mr or Mrs. 500
Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)

Group: General Forum Members
Points: 517 Visits: 1516
Do you mean that it useful when you want to create script with unpredictable result? Sure it is.


Alex Suprun
Ivan Mohapatra
Ivan Mohapatra
Old Hand
Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)

Group: General Forum Members
Points: 379 Visits: 495
no the script is just an example the query has different column and table name.

well i want to know is that will that Nolock will be helpful or not for that particular join update query!!

if it is not useful then why ?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88567 Visits: 45284
Ivan Mohapatra (10/18/2012)
if it is not useful then why ?


1) Because it is ignored for the target of update statements (changes always have to take exclusive locks)
2) Because it allows unpredictable, inconsistent results (missed rows, duplicated rows)
3) Because using one of the snapshot isolation levels is almost always a better idea

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


mahesh.dasoni
mahesh.dasoni
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 1025
Refer below link which will make u understand what happens when u use NOLOCK option

http://beyondrelational.com/modules/2/blogs/28/posts/10465/sql-server-transaction-isolation-level-read-uncommitted.aspx
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23849 Visits: 9730
Ivan Mohapatra (10/18/2012)
no the script is just an example the query has different column and table name.

well i want to know is that will that Nolock will be helpful or not for that particular join update query!!

if it is not useful then why ?


It depends on what you mean by "useful".

The purpose of locks on data is to make sure you get the correct data. Thus, NoLock makes it possible to get incorrect data. So, if you want wrong data slightly faster, then NoLock might be "useful". Make sure to inform the managers of your company that you are making that decision, and to let them know that there are other ways to make queries run faster, which don't result in wrong data, but that you have chosen not to use them because NoLock is easier for you.

That's really what NoLock is for: To make less work for the developer, while making data wrong. There are other solutions that will get you the speed that NoLock does, which don't result in corrupted data, so speed isn't what it's actually for.

If you and the company managers are happy with wrong data and less work for you, then NoLock is "useful".

- 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
223fms
223fms
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 7
GSquared (10/19/2012)
Ivan Mohapatra (10/18/2012)
well i want to know is that will that Nolock will be helpful or not for that particular join update query!!
if it is not useful then why ?


The purpose of locks on data is to make sure you get the correct data. Thus, NoLock makes it possible to get incorrect data. So, if you want wrong data slightly faster, then NoLock might be "useful". Make sure to inform the managers of your company that you are making that decision, and to let them know that there are other ways to make queries run faster, which don't result in wrong data, but that you have chosen not to use them because NoLock is easier for you.

That's really what NoLock is for: To make less work for the developer, while making data wrong. There are other solutions that will get you the speed that NoLock does, which don't result in corrupted data, so speed isn't what it's actually for.

If you and the company managers are happy with wrong data and less work for you, then NoLock is "useful".


Could you elaborate on these other speed increases?

I am working on some (about 25 total) queries for reporting and while I would not want to use NoLock in production or for a final result, it seems like a good idea (for QA purposes, while I'm working out the kinks) if it can cut my execution time for the steps that don't require any data at all, let alone accuracy -- just successful execution (joining, etc), or not.
This is especially the case as there may be outstanding (open) row- or table-level locks held by other processes/developers for a period of minutes(!) at a time. Thus, any change to a (read-only) query that I want to test currently usually takes in excess of 3 minutes, even for a result that will return zero rows.
(I don't wish to discuss the ramifications or desirability of this fact in this forum :-) it's non-optimal, but I don't have standing to address it at this time, and I do have things that need to get done, so I'm doing what I can to make sure stuff is at least ready to run when it needs to).

That said, if there are other ways (as is suggested above) that would speed up the final product & future runs of the reportset, WITHOUT impacting accuracy (not even a little bit), then I'm more than willing to put in the up-front time now to not have to deal with server sloth later, even if NoLock remains a useful tool for just getting queries up & running, albeit one that I would never use for data that will be seen outside of my results grid.

Thanks!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88567 Visits: 45284
Good indexes, efficiently written queries, good database design.

If there's locking problems, tune queries, tune indexes, consider one of the snapshot isolation levels.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search