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 ««12

DIFFERENCE BETWEEN NOLOCK VS WITH(NOLOCK) IN SQL SERVER 2008 Expand / Collapse
Author
Message
Posted Friday, January 10, 2014 2:23 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:25 PM
Points: 3,333, Visits: 7,193
In read-commited isolation level, readers don't block readers.


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1529976
Posted Friday, January 10, 2014 2:44 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 @ 3:52 AM
Points: 42,445, Visits: 35,501
homebrew01 (1/10/2014)
GilaMonster (1/10/2014)
No, it's an unnecessary use. If the data is static during the day then there aren't going to be any X locks around to block the reports and so adding nolock is a waste of typing


So am I mis-remembering situations in which long running read-only queries have caused blocking of other read-only queries ?


Read queries take shared locks (unless someone's been messing with locking hints). Shared locks never block other shared locks.



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 #1529982
Posted Friday, January 10, 2014 3:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:55 AM
Points: 6,142, Visits: 7,195
homebrew01 (1/10/2014)
We have some semi-static reporting databases that run long queries. Some of them use nolock to avoid one report blocking another. In this case, the data was refreshed over night from production, and is static during the day. There is not really a concern about users getting up to the minute accurate information. Does this qualify as an acceptable use ?


The only thing you're doing here is reducing locking lag/escalation requirements, but they can't block each other.

TABLOCK would be equivalent, and safer, in a read only environment. Single lock opened, tiny overhead.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1529988
Posted Monday, January 13, 2014 10:19 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 7:34 PM
Points: 555, Visits: 2,580
kbhanu15 (1/9/2014)
Hi All,

can any one clarify the difference between NOLOCK and WITH(NOLOCK) in sql server 2008 ?
which one is the best practice to use ?

Thanks
Bhanu


I think everyone did a good job explaining why you would want to avoid using read-uncommitted (AKA NOLOCK). That said, if NOLOCK is required (and getting the correct results when you run a query is not required) then I recommend always using: WITH (NOLOCK). Some things break when you omit the WITH keyword when using NOLOCK.

For example: this query would fail:
SELECT t1.<column>, t2.<column>
FROM <db>.<schema>.<table> t1
JOIN <linked server>.<db>.<schema>.<table> t2 (NOLOCK)

This would not fail:
SELECT t1.<column>, t2.<column>
FROM <db>.<schema>.<table> t1
JOIN <linked server>.<db>.<schema>.<table> t2 WITH (NOLOCK)



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1530373
Posted Monday, January 13, 2014 12:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 13,069, Visits: 11,908
Alan.B (1/13/2014)
kbhanu15 (1/9/2014)
Hi All,

can any one clarify the difference between NOLOCK and WITH(NOLOCK) in sql server 2008 ?
which one is the best practice to use ?

Thanks
Bhanu


I think everyone did a good job explaining why you would want to avoid using read-uncommitted (AKA NOLOCK). That said, if NOLOCK is required (and getting the correct results when you run a query is not required) then I recommend always using: WITH (NOLOCK). Some things break when you omit the WITH keyword when using NOLOCK.

For example: this query would fail:
SELECT t1.<column>, t2.<column>
FROM <db>.<schema>.<table> t1
JOIN <linked server>.<db>.<schema>.<table> t2 (NOLOCK)

This would not fail:
SELECT t1.<column>, t2.<column>
FROM <db>.<schema>.<table> t1
JOIN <linked server>.<db>.<schema>.<table> t2 WITH (NOLOCK)



I will ignore your comment about suggesting that always using NOLOCK is a good idea when integrity of the results is not important.

The reason omitting the keyword WITH when defining a query hint is because leaving it off has been deprecated. You should ALWAYS use proper syntax in any t-sql statement and omitting WITH for a query hint is not correct syntax.

http://technet.microsoft.com/en-us/library/ms143729.aspx

To see the line in reference just search for "Specifying table hints without using the WITH keyword."


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1530406
Posted Monday, January 13, 2014 12:57 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:25 PM
Points: 3,333, Visits: 7,193
Sean Lange (1/13/2014)


I will ignore your comment about suggesting that always using NOLOCK is a good idea when integrity of the results is not important.

I'm sure he meant that WITH(NOLOCK) should always be used instead of NOLOCK only when the hint is mandatory and not when integrity of the results is not important.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1530425
Posted Monday, January 13, 2014 1:00 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 7:34 PM
Points: 555, Visits: 2,580
I will ignore your comment about suggesting that always using NOLOCK is a good idea when integrity of the results is not important.


To be clear (and to avoid being the guy who suggested someone use always use NOLOCK in any situation) I do want to point out that I included the caveat, "If NOLOCK is required..." I think my point would have been clearer if I said: If there is a requirement to use NOLOCK then use the always use WITH. That said, I can't think of a good reason to use NOLOCK and aver that a requirement that anyone must use NOLOCK is ridiculous.


Edit: Just Louis' response; that is what I meant... Thank you sir.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1530426
Posted Monday, January 13, 2014 2:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 13,069, Visits: 11,908
Alan.B (1/13/2014)
I will ignore your comment about suggesting that always using NOLOCK is a good idea when integrity of the results is not important.


To be clear (and to avoid being the guy who suggested someone use always use NOLOCK in any situation) I do want to point out that I included the caveat, "If NOLOCK is required..." I think my point would have been clearer if I said: If there is a requirement to use NOLOCK then use the always use WITH. That said, I can't think of a good reason to use NOLOCK and aver that a requirement that anyone must use NOLOCK is ridiculous.


Edit: Just Louis' response; that is what I meant... Thank you sir.


Yes 10000% agreed. I must have misread your original post and/or was just being a pissy boy at that moment. My comment was unintentionally (and hopefully uncharacteristically) rude. My apologies if I offended you, I certainly did not mean any.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1530465
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse