Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

IS (with nolock) is useful in (multiple join update) Query!! Expand / Collapse
Author
Message
Posted Thursday, October 18, 2012 1:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 21, 2014 4:26 AM
Points: 121, Visits: 432
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
Post #1374563
Posted Thursday, October 18, 2012 1:59 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #1374578
Posted Thursday, October 18, 2012 2:19 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:29 PM
Points: 185, Visits: 902
Do you mean that it useful when you want to create script with unpredictable result? Sure it is.


Alex Suprun
Post #1374584
Posted Thursday, October 18, 2012 2:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 21, 2014 4:26 AM
Points: 121, Visits: 432
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 ?
Post #1374585
Posted Thursday, October 18, 2012 3:06 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 41,525, Visits: 34,442
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 2008, MVP
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

Post #1374593
Posted Friday, October 19, 2012 3:14 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 14, 2014 12:11 PM
Points: 81, Visits: 882
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
Post #1374699
Posted Friday, October 19, 2012 6:54 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #1374796
Posted Monday, October 29, 2012 7:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 23, 2013 10:02 AM
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!
Post #1378537
Posted Tuesday, October 30, 2012 2:17 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 41,525, Visits: 34,442
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 2008, MVP
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

Post #1378605
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse