Select with (nolock)

Question

My boss keeps on forcing me to write SELECT queries with with (nolock) to prevent deadlocks. But AFAIK, Select statements by default does not have locks, so selecting with with (nolock) and selecting without doesn't make any difference. Please correct me if I am wrong.

The two queries:

SELECT * from EMP with (nolock)

SELECT * from EMP 

Isn't both are same. If I don't put nolock will it be prone to deadlocks? Please tell me what should I use.

Solution

Nolocks should be used with extreme caution. The most common understanding of nolock (read uncommitted) hint is that it reads data that has not been committed yet. However, there are other side effects that can be very dangerous. (search for "nolock" and "page splits")

There's a really good write up here... http://sqlmag.com/sql-server/beware-nolock-hint

In short, "nolocking"ing everything is not always a good idea... if ever.

Comments

[Bharadwaj]

stackoverflow.com/questions/686724/…

[Mitch Wheat]

It depends. In a high insertion situation, using with(nolock) could read incorrect data (not just stale data). brentozar.com/archive/2011/11/… FIX the actual problem, not the symptoms. If all you are doing is running too wide (select *), too large (no WHERE clause) queries in SSMS to inspect data (as your example above), then YES, use with(nolock)

[Alex]

Here is another good question on the subject stackoverflow.com/questions/1452996/…

[M.Ali]

It is incorrect to say SELECT query doesnt do any locking, Under default transaction isolation level i.e READ COMMITTED Select queries does obtain Shared Locks on the resources. when used NOLOCK query hint it would not take any locks at all. And as Mitch has mentioned when No locks are being obtained your query is open for dirty reads (Uncommitted data).

[Janne Matikainen]

I recommend reading this article about locks and deadlocks in sql server aboutsqlserver.com/lockingblocking