Search code examples
sql-serversql-server-2008selectdeadlock

Select with (nolock)

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... https://www.itprotoday.com/sql-server/beware-nolock-hint

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