DIFFERENCE BETWEEN NOLOCK VS WITH(NOLOCK) IN SQL SERVER 2008

Question

DIFFERENCE BETWEEN NOLOCK VS WITH(NOLOCK) IN SQL SERVER 2008

kbhanu15

SSCarpal Tunnel

Points: 4498
More actions
January 9, 2014 at 3:31 am

#396079

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

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Divine Flame SSCoach Points: 15943 More actions · Answer 1

kbhanu15 (1/9/2014)
Hi All,
can any one clarify the difference between NOLOCK and WITH(NOLOCK) in sql server 2008 ?
Thanks
Bhanu

There is no diference between NOLOCK & WITH (NOLOCK). They both execute the query in "Read Uncommited" Isolation level.

which one is the best practice to use ?

No one, as they both may provide the result set with dirty reads.

Sujeet Singh

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

The first is deprecated, the second is not, both can result in incorrect results, duplicate rows and missing rows, so neither is the best to use. Best is to write the SQL properly without hints.

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

kbhanu15 SSCarpal Tunnel Points: 4498 More actions · Answer 3

Thanks for you support.

it is mandatory in my query ti use(NOLOCK or WITH(NOLOCK), so i can prefer to use WITH (NOLOCK).

it is your conclusion.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Why is a hint that will sooner or later result in incorrect results 'mandatory'? Do the people you're writing that query for realise that from time to time their results will be incorrect?

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

NOLOCK is not a good habit at all. In fact query hints in general are a last resort and should only be used when you REALLY understand what they are doing. If you are unsure, avoid them.

Before you claim that NOLOCK is mandatory for your query you need to fully understand what that hint does and the serious issues it can and will bring to your system. Here are 3 very excellent articles describing what happens when you use that hint.

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

homebrew01 SSC Guru Points: 55548 More actions · Answer 6

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 ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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

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

x SSC-Insane Points: 23660 More actions · Answer 8

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 ?

I've been in the situation where nolock use was mandatory. I'm pretty sure that the current supported syntax is WITH (NOLOCK).

Make sure you realise what actually happens and the ramifications of using it. In particular, knowledge of isolation modes is highly recommended when programming databases, I like the wikipedia articles on it. Nolock looks to be equal to read uncommitted.

http://en.wikipedia.org/wiki/Isolation_(database_systems)

homebrew01 SSC Guru Points: 55548 More actions · Answer 9

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 ?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 10

In read-commited isolation level, readers don't block readers.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

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, 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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 12

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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Alan Burstein SSC Guru Points: 61141 More actions · Answer 13

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 cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Sean Lange SSC Guru Points: 286590 More actions · Answer 14

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. :w00t:

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/