with nolock

[Guest]

Is 'with (nolock)' necessary in this case?

PROCEDURE GetShoeSize
AS
select shoesize from people with (nolock) where personid=1

Dim cmd As New SqlCommand
cmd.CommandText = "GetShoeSize"
cmd.CommandType = CommandType.StoredProcedure
shooesize = cmd.ExecuteNonQuery

 

[W.G. Ryan MVP]

It depends on how you define 'necessary' but based just on this information,
no it's not. Nolock is just another query hint and in most instances, it's
probably best to avoid it b/c it allows dirty reads. That could cause a lot
of problems for you so unless you have a very specific reason to use it, I'd
avoid it.

FYI:
From(http://www.sql-server-performance.com/rd_table_hints.asp)
NOLOCK (equivalent to READUNCOMMITTED) permits dirty reads. Dirty reads will
not issue shared locks and will ignore exclusive locks placed by other
processes. It is possible to receive error messages if the read takes place
on an uncommitted transaction or a set of pages being rolled back.

 

[Sahil Malik [MVP]]

I believe this question was asked not too long back.

Here is what I have to say about it ----

"nolock and readuncommitted in general is the spawn of satan in the
transactional and data sanctity world, try not to use it in ANYTHING except
admin level data monitoring".

Is it necessary? In a correctly designed system it is not only unnecessary,
it should be "avoided".

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Guest]

Sahil,
You are really passionante about nolock. You sounds like a fire and
brimstone pastor.
Thanks for the reply.

Arne.

 

[Sahil Malik [MVP]]

;-), just making sure that the message went across .. thats all

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------
---------------

Sahil,
You are really passionante about nolock. You sounds like a fire and
brimstone pastor.
Thanks for the reply.

Arne.

I believe this question was asked not too long back.

Here is what I have to say about it ----

"nolock and readuncommitted in general is the spawn of satan in the
transactional and data sanctity world, try not to use it in ANYTHING except
admin level data monitoring".

Is it necessary? In a correctly designed system it is not only unnecessary,
it should be "avoided".

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
--------------------------------------------------------------------------

--