with nolock

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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
 
Sahil,
You are really passionante about nolock. You sounds like a fire and
brimstone pastor.
Thanks for the reply.

Arne.
 
;-), 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
----------------------------------------------------------------------------
---------------

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

Arne.

Sahil Malik said:
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
--------------------------------------------------------------------------
Click to expand...
--
Click to expand...
 
Back
Top