What is WITH (NOLOCK) in SQL Server?

WITH (NOLOCK) is query optimizer, it is a good practice using it with SELECT statements.
It ignores lock and read directly from the tables and improves concurrency on a busy system.
Useful in a situation like "when another process could be updating the data at the exact time U are reading it"
Advantage of WITH (NOLOCK) is over performance, the data U are reading will not block UPDATE statements from taking place and UPDATE statements will not block reading of Ur data.
Yes but there is no guarantee that Ur query will retrieve the most recent data.

Multiple SELECT statements are allowed simultaneous access, but other processes are blocked from modifying the data.
UPDATE or SELECT statements, one of them will wait in a line ntill and, unless other requests completed.

Example : Select * from Table with(nolock)

Brain Food : SELECT statements takes shared(Read) locks.


It is most definitely NOT good practice to use NOLOCK. Look here for proof: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx

Post a Comment