5

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.

Jainendra
  • 24,713
  • 30
  • 122
  • 169
  • http://stackoverflow.com/questions/686724/sql-when-should-you-use-with-nolock – Bharadwaj May 05 '14 at 07:43
  • 1
    It depends. In a high insertion situation, using with(nolock) could read incorrect data (not just stale data). http://www.brentozar.com/archive/2011/11/theres-something-about-nolock-webcast-video/ 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) – Mitch Wheat May 05 '14 at 07:43
  • Here is another good question on the subject http://stackoverflow.com/questions/1452996/is-the-nolock-sql-server-hint-bad-practice?rq=1 – Alex May 05 '14 at 07:43
  • 1
    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). – M.Ali May 05 '14 at 07:48
  • I recommend reading this article about locks and deadlocks in sql server http://aboutsqlserver.com/lockingblocking/ – Janne Matikainen May 05 '14 at 08:07

4 Answers4

3

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.

jobo3208
  • 792
  • 8
  • 16
sam yi
  • 4,806
  • 1
  • 29
  • 40
  • In a table with auto incrementing id used as clustered index do you think page split can be any problem? Yes the page split problem will occur if the row is modified such that the data doesn't fit on the page. But otherwise no problem right? What do you think on this please? – variable Jul 08 '22 at 16:14
0

Assuming we have default Transaction Isolation Level READ COMMITTED ,there is a chance for a dead lock even in a very simple SELECT statement , Imagine a scenario where User1 is only reading data and User2 trys to Update some data and there a non-clustered index on that table, it is possible.

  1. User1 is reading Some Data and obtains a shared lock on the non-clustered index in order to perform a lookup, and then tries to obtain a shared lock on the page contianing the data in order to return the data itself.

  2. User2 who is writing/Updating first obtains an exlusive lock on the database page containing the data, and then attempts to obtain an exclusive lock on the index in order to update the index.

M.Ali
  • 67,945
  • 13
  • 101
  • 127
0

SELECT statements do indeed apply locks unless there is a statement at the top of the query SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

By all means use WITH (NOLOCK) in SELECT statement on tables that have a clustered index, but it would be wiser to only do so if there's a need to.

Hint: The easiest way to add a clustered index to a table is to add an Id Primary Key column.

The result set can contain rows that have not yet been committed, that are often later rolled back.

If WITH(NOLOCK) is applied to a table that has a non-clustered index then row-indexes can be changed by other transactions as the row data is being streamed into the result-table. This means that the result-set can be missing rows or display the same row multiple times.

READ COMMITTED adds an additional issue where data is corrupted within a single column where multiple users change the same cell simultaneously.

Bearing in mind the issues WITH(NOLOCK) causes will help you tune your database.

As for your boss, just think of them as a challenge.

WonderWorker
  • 8,539
  • 4
  • 63
  • 74
0

One benefit of using a select statement with no deadlock is that it excludes data that is currently locked. This can result in a faster query and help prevent your application from crashing in the event of a lock in the database.

Jack Damasco
  • 11
  • 1