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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » IS (with nolock) is useful in (multiple join...

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

Rate Topic

Display Mode

Topic Options

Author

Message

Ivan Mohapatra

Posted Thursday, October 18, 2012 1:19 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 4:59 AM
Points: 104, Visits: 362

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

GSquared

Posted Thursday, October 18, 2012 1:59 PM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

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

Alexander Suprun

Posted Thursday, October 18, 2012 2:19 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 3:15 PM
Points: 160, Visits: 802

Do you mean that it useful when you want to create script with unpredictable result? Sure it is.

Alex Suprun

Post #1374584

Ivan Mohapatra

Posted Thursday, October 18, 2012 2:25 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 4:59 AM
Points: 104, Visits: 362

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

GilaMonster

Posted Thursday, October 18, 2012 3:06 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 37,741, Visits: 30,020

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

mahesh.dasoni

Posted Friday, October 19, 2012 3:14 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 7:26 AM
Points: 81, Visits: 860

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

GSquared

Posted Friday, October 19, 2012 6:54 AM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

Ivan Mohapatra (10/18/2012)

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

223fms

Posted Monday, October 29, 2012 7:37 PM

Forum Newbie

Group: General Forum Members
Last Login: Yesterday @ 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 Smile

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

GilaMonster

Posted Tuesday, October 30, 2012 2:17 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 37,741, Visits: 30,020

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 »

Permissions