NOLOCK usage

If we have a select statment using the NOLOCK hint ...
will it prevent other processes from
UPDATING/INSERTING/DELETING any of the table(s) it is
using within it query? Would really like to hear the
answer from an MS tech for validity purposes. Thanks and
have a great day.Jay,
From the BOL on FROM:
NOLOCK is equal to READUNCOMMITTED
READUNCOMMITTED
Specifies that dirty reads are allowed. This means that no shared locks are
issued and no exclusive locks are honored. Allowing dirty reads can result
in higher concurrency, but at the cost of lower consistency. If
READUNCOMMITTED is specified, it is possible to read an uncommitted
transaction or to read a set of pages rolled back in the middle of the read;
therefore, error messages may result. For more information about isolation
levels, see SET TRANSACTION ISOLATION LEVEL.
So, it absolutely will not prevent other processes from doing whatever they
want to the tables. One interesting error that you can get using NOLOCK is
Error 601 "could not continue scan due to data movement."
Russell Fields (not from Microsoft)
"Jay Kusch" <Jay.Kusch@.mm-games.com> wrote in message
news:014001c3927d$c453a810$a401280a@.phx.gbl...
> If we have a select statment using the NOLOCK hint ...
> will it prevent other processes from
> UPDATING/INSERTING/DELETING any of the table(s) it is
> using within it query? Would really like to hear the
> answer from an MS tech for validity purposes. Thanks and
> have a great day.

NOLOCK optimizer hint on iterator

An interesting discussion yesterday. One of the programmers asked about the use of the NOLOCK optimizer hint with an iterator table aka table of numbers. His comment was that this optimizer hint was not efficient. Rather than give a knee-jerk response I thought it would be better to ask. The main circumstance is that the iterator table is completely static with a fill factor of 100%. My purpose is to eliminate lock contention if I can.

Are there reasons to not use the NOLOCK hint in this case to potentially improve performance?

Dave

Dave,

I am a big fan of using NOLOCK appropriately. Generally, the NOLOCK reads dirty, ignoring locks like an UPDATE lock and would not wait for the lock to release before reading the data (helps against blocking).

However, if the table is static and there are no modification locks on the table, NOLOCK will not likely be of much help to you on this table. It is possible (MS please verify) that the NOLOCK would also skip reading the lock table so that could be something that would help if your SELECT (NOLOCK) is in a large repeating loop or process.

|||

>>His comment was that this optimizer hint was not efficient. <<

Not efficient? While I am not 100% sure that you would get noticable performance improvements by using NOLOCK except in very large query situations, it will save time by not checking or leaving locks. You won't save any contention per ce because you should never leave any exclusive locks that cause contention.

So I don't see that it will ever hurt anything to do this to a read-only table, but it could help, if just a tiny amount.

|||

>> His comment was that this optimizer hint was not efficient.

Did he say how it's not efficient? That statement doesn't make much sense. Your reply should have been: "You keep using that word. I do not think it means what you think it means."

But then, I always welcome an opportunity to quote Inigo Montoya!!!

|||

The reasoning had something to do with "excessive reading of the transaction logs." I've used the NOLOCK hint heavily since the 90's and have never noticed this supposed problem. Moreover, the hint has often been the solution to contention between reports and transaction processes. Back in the 90s I heard a similar objection from a collegue but discounted it. Since This was something I had "heard" before I felt it was better to re-verify.

Dave

NOLOCK hint on views?

Hi all

If i have a view:

CREATE VIEW vw_Users

AS

SELECT * FROM Users WITH(NOLOCK)

Is it suggested to use nolock in views?

And if i needed to use this view in stored procs is it then suggested to apply the nolock hint?

CREATE PROC [dbo] .[usp_GetCompanyUsers]

AS

SELECT * FROM Companies WITH(NOLOCK) JOIN

vw_Users WITH(NOLOCK) --<< --is this suggested?

If you using NOLOCK hint while creating view, you don't need to use the NOLOCK again when accessing the view. The locking is always done on the table and not on the view.

NOLOCK hint causes process blocking?

We recently added NOLOCK hints to our less-important queries to cut down on
deadlocks. It does decrease the deadlocks but it seemed to increase other
process blocking... the type that nearly hangs the sql server. Is that
possible? Has anyone else noticed? I am starting to think NOLOCK is a
NO-NO, and that deadlocks are better.
-Dan
What's probably happeneing is that without NOLOCK the queries were being
slowed down. Now with NOLOCK they run full out and are hitting resource
shortages elsewhere. Deadlocks are NEVER better.
How is your server nearly hanging? If you're trying to resolve deadlocks by
adding NOLOCK then it may be that your queries/updates have problems.
Are you accessing resourcing in the same sequence?
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Dan English" <dan_english2@.cox.net> wrote in message
news:OC8PKDE0FHA.3408@.TK2MSFTNGP09.phx.gbl...
> We recently added NOLOCK hints to our less-important queries to cut down
> on deadlocks. It does decrease the deadlocks but it seemed to increase
> other process blocking... the type that nearly hangs the sql server. Is
> that possible? Has anyone else noticed? I am starting to think NOLOCK is
> a NO-NO, and that deadlocks are better.
> -Dan
>
|||Thanks for the response. I've posted the offending stored proc in a new
post.
"Nik Marshall-Blank" <Nik@.here.com> wrote in message
news:PlJ3f.136806$vt2.119755@.fe08.news.easynews.co m...
> What's probably happeneing is that without NOLOCK the queries were being
> slowed down. Now with NOLOCK they run full out and are hitting resource
> shortages elsewhere. Deadlocks are NEVER better.
> How is your server nearly hanging? If you're trying to resolve deadlocks
> by adding NOLOCK then it may be that your queries/updates have problems.
> Are you accessing resourcing in the same sequence?
> --
> Nik Marshall-Blank MCSD/MCDBA
> Linz, Austria

NOLOCK hint causes process blocking?

We recently added NOLOCK hints to our less-important queries to cut down on
deadlocks. It does decrease the deadlocks but it seemed to increase other
process blocking... the type that nearly hangs the sql server. Is that
possible? Has anyone else noticed? I am starting to think NOLOCK is a
NO-NO, and that deadlocks are better.
-DanWhat's probably happeneing is that without NOLOCK the queries were being
slowed down. Now with NOLOCK they run full out and are hitting resource
shortages elsewhere. Deadlocks are NEVER better.
How is your server nearly hanging? If you're trying to resolve deadlocks by
adding NOLOCK then it may be that your queries/updates have problems.
Are you accessing resourcing in the same sequence?
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Dan English" <dan_english2@.cox.net> wrote in message
news:OC8PKDE0FHA.3408@.TK2MSFTNGP09.phx.gbl...
> We recently added NOLOCK hints to our less-important queries to cut down
> on deadlocks. It does decrease the deadlocks but it seemed to increase
> other process blocking... the type that nearly hangs the sql server. Is
> that possible? Has anyone else noticed? I am starting to think NOLOCK is
> a NO-NO, and that deadlocks are better.
> -Dan
>|||Thanks for the response. I've posted the offending stored proc in a new
post.
"Nik Marshall-Blank" <Nik@.here.com> wrote in message
news:PlJ3f.136806$vt2.119755@.fe08.news.easynews.com...
> What's probably happeneing is that without NOLOCK the queries were being
> slowed down. Now with NOLOCK they run full out and are hitting resource
> shortages elsewhere. Deadlocks are NEVER better.
> How is your server nearly hanging? If you're trying to resolve deadlocks
> by adding NOLOCK then it may be that your queries/updates have problems.
> Are you accessing resourcing in the same sequence?
> --
> Nik Marshall-Blank MCSD/MCDBA
> Linz, Austria

NOLOCK hint causes process blocking?

We recently added NOLOCK hints to our less-important queries to cut down on
deadlocks. It does decrease the deadlocks but it seemed to increase other
process blocking... the type that nearly hangs the sql server. Is that
possible? Has anyone else noticed? I am starting to think NOLOCK is a
NO-NO, and that deadlocks are better.
-DanWhat's probably happeneing is that without NOLOCK the queries were being
slowed down. Now with NOLOCK they run full out and are hitting resource
shortages elsewhere. Deadlocks are NEVER better.
How is your server nearly hanging? If you're trying to resolve deadlocks by
adding NOLOCK then it may be that your queries/updates have problems.
Are you accessing resourcing in the same sequence?
--
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Dan English" <dan_english2@.cox.net> wrote in message
news:OC8PKDE0FHA.3408@.TK2MSFTNGP09.phx.gbl...
> We recently added NOLOCK hints to our less-important queries to cut down
> on deadlocks. It does decrease the deadlocks but it seemed to increase
> other process blocking... the type that nearly hangs the sql server. Is
> that possible? Has anyone else noticed? I am starting to think NOLOCK is
> a NO-NO, and that deadlocks are better.
> -Dan
>|||Thanks for the response. I've posted the offending stored proc in a new
post.
"Nik Marshall-Blank" <Nik@.here.com> wrote in message
news:PlJ3f.136806$vt2.119755@.fe08.news.easynews.com...
> What's probably happeneing is that without NOLOCK the queries were being
> slowed down. Now with NOLOCK they run full out and are hitting resource
> shortages elsewhere. Deadlocks are NEVER better.
> How is your server nearly hanging? If you're trying to resolve deadlocks
> by adding NOLOCK then it may be that your queries/updates have problems.
> Are you accessing resourcing in the same sequence?
> --
> Nik Marshall-Blank MCSD/MCDBA
> Linz, Austria