9 Replies - 1532 Views - Last Post: 21 June 2018 - 06:42 AM

#1 tokei   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 93
  • Joined: 15-December 11

NoLock in SQL Server

Posted 18 June 2018 - 09:02 PM

Dear team,

I would like to ask you, is it any difference with SELECT statement with NoLock and without mention NoLock ?

For example the SQL Script is following below :

USE [AdventureWorks2014]
GO

SELECT p.City FROM Person.Address  p WITH (NOLOCK)

SELECT p.City FROM Person.Address  p




Thank you.
Is This A Good Question/Topic? 0
  • +

Replies To: NoLock in SQL Server

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15427
  • View blog
  • Posts: 61,796
  • Joined: 12-June 08

Re: NoLock in SQL Server

Posted 18 June 2018 - 09:04 PM

It allows the query to execute without blocking the data from other transactions, and also ignores any locks that would block it from other transactions.

Typically I find it to be a good bit of extra insurance to use.

https://www.mssqltip...er-nolock-hint/
Was This Post Helpful? 0
  • +
  • -

#3 tokei   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 93
  • Joined: 15-December 11

Re: NoLock in SQL Server

Posted 19 June 2018 - 12:03 AM

View Postmodi123_1, on 19 June 2018 - 12:04 PM, said:

It allows the query to execute without blocking the data from other transactions, and also ignores any locks that would block it from other transactions.

Typically I find it to be a good bit of extra insurance to use.

https://www.mssqltip...er-nolock-hint/


Which one is better , point No.1 or No.2 ?

1) SELECT p.City FROM Person.Address p WITH (NOLOCK)

2) SELECT p.City FROM Person.Address p

Thanks
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6829
  • View blog
  • Posts: 28,311
  • Joined: 12-December 12

Re: NoLock in SQL Server

Posted 19 June 2018 - 04:25 AM

Did you read the link provided?

The answer isn't black and white. Reading a little around the subject, some advise that it is unnecessary and perhaps should be avoided, pointing out potential hazards of using it.

A quick search reveals What is “with (nolock)” in SQL Server?

If you are still undecided then explain the circumstances and what is concerning you.
Was This Post Helpful? 1
  • +
  • -

#5 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 3038
  • View blog
  • Posts: 11,669
  • Joined: 03-December 12

Re: NoLock in SQL Server

Posted 19 June 2018 - 06:36 AM

As Andrew pointed out, there is no 'better'. It is which do you need to suit your purpose.

In all likelihood, you do not need it. You probably don't have enough data in the tables, users hitting the system, or critical operations going on for you to need a nolock operation.
Was This Post Helpful? 0
  • +
  • -

#6 tokei   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 93
  • Joined: 15-December 11

Re: NoLock in SQL Server

Posted 21 June 2018 - 12:50 AM

View Postandrewsw, on 19 June 2018 - 07:25 PM, said:

Did you read the link provided?


I have read this page before I subscribe the topic here.

I quote the statement from that page :

Quote

-- run in query window 1
BEGIN TRAN
UPDATE Person.Contact SET Suffix = 'B' WHERE ContactID < 20
-- ROLLBACK or COMMIT

If I run the same query from above again you will notice that it never completes, because the UPDATE has not yet been committed.



I dont understand what he means the update not comitted. When I run the update statement, the value in the column was successfully changes.

When I run this query following below :

SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20 


Then the value likes no changes and still the same.

Thanks
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6829
  • View blog
  • Posts: 28,311
  • Joined: 12-December 12

Re: NoLock in SQL Server

Posted 21 June 2018 - 02:27 AM

Quote

Then the value likes no changes and still the same.

That is a very unclear sentence, please try to restate your concern more clearly.

Glancing through that page, I think it describes what is going on very clearly. The examples seem to me that they should work to demonstrate the points but, even without these fully working, the description is enough for you to follow and understand any concerns.

If you understand potential issues then you can proceed. If you don't understand the issues, and cannot provide justification for using NOLOCK, then avoid it.
Was This Post Helpful? 0
  • +
  • -

#8 tokei   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 93
  • Joined: 15-December 11

Re: NoLock in SQL Server

Posted 21 June 2018 - 03:13 AM

Actually I want to learn it because our previous developer using NOLOCK in store procedure. I just want to know the purpose. However with practice learning, I can't see any concern or purpose to use it.

Thank you.

This post has been edited by andrewsw: 21 June 2018 - 03:47 AM
Reason for edit:: removed previous quote, just use the Reply button

Was This Post Helpful? 0
  • +
  • -

#9 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6829
  • View blog
  • Posts: 28,311
  • Joined: 12-December 12

Re: NoLock in SQL Server

Posted 21 June 2018 - 03:53 AM

The accepted answer provided at stackoverflow that I linked to seems perfect to me. If you are unfamiliar with some of its terms used then you could investigate these individually until you more clearly understand the answer. Other answers and comments on that page also provide v. useful information.

[There is also the possibility that your previous developer either didn't fully consider its use and justification each time, or perhaps even fell into the habit of using it. I'm not suggesting that this is the case, merely encouraging the notion that perfection is a mythh (sic).]
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15427
  • View blog
  • Posts: 61,796
  • Joined: 12-June 08

Re: NoLock in SQL Server

Posted 21 June 2018 - 06:42 AM

See post #2.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1