October 18, 2012 at 1:19 pm
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
October 18, 2012 at 1:59 pm
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
October 18, 2012 at 2:19 pm
Do you mean that it useful when you want to create script with unpredictable result? Sure it is.
October 18, 2012 at 2:25 pm
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 ?
October 18, 2012 at 3:06 pm
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
October 19, 2012 at 3:14 am
Refer below link which will make u understand what happens when u use NOLOCK option
October 19, 2012 at 6:54 am
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
October 29, 2012 at 7:37 pm
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!
October 30, 2012 at 2:17 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply