Clarification of NoLock

Question

Clarification of NoLock

joshua.a.anderson

SSC Journeyman

Points: 82
More actions
September 19, 2008 at 1:49 pm

#191056

I want to be sure I understand the purpose of nolock in select statements.
For example
select * from table with(nolock)
When I use nolock it is running the query but not locking the data in case other queries,views,stored procedures, write,delete whatever uses it?
If I do not use nolock and I query a table with 80,000 records does that mean my query prevents other views, stored procedures, write, delete whatever from happening at the time I run the query?

Viewing 6 posts - 1 through 5 (of 5 total)

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

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

If you run a select under the default isolation level (read committed), the lock taken will prevent any changes to the table while the select is running (insert, update, delete), but will allow other queries to read.

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

U J SSCrazy Points: 2423 More actions · Answer 2

There is a possibility that you may get dirty reads(uncommited rows) if you use a nolock hint.

krayknot SSCertifiable Points: 7998 More actions · Answer 3

It is not advisable to use NOLOCK as it returns the uncommited rows that may affects the final output

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 4

krayknot (10/20/2008)
It is not advisable to use NOLOCK as it returns the uncommited rows that may affects the final output

That is quite a generalization. If you are reporting off of transactional data, it is a good idea to use NOLOCK because you don't want to lock up the tables.

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

ggraber (10/20/2008)
That is quite a generalization. If you are reporting off of transactional data, it is a good idea to use NOLOCK because you don't want to lock up the tables.

It is not a good idea to use nolock when reporting off transactional data, unless you don't mind your reports been wrong from time to time.

Nolock is something that should be applied selectively and carefully, with consideration of the possible problems caused by it.

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