CODEWITHSUNDEEP
sql-server
Sajad Khoshnoudi
Sajad Khoshnoudi

Reputation: 145

Should I still use With(NoLock) in SQL Server queries?

I'm trying to write some queries to fetch data from database. I used to use With(NoLock) to prevent SQL Server from placing shared locks on database. And recently a friend of mine told me that the default behavior now is that it doesn't put any lock on reading data.

However, I can't find a clean explanation for this. Should I still use With(NoLock) in SQL Server?

Upvotes: 7

Views: 13655

Answers (3)

Nitesh Kumar
Nitesh Kumar

Reputation: 1774

I found in Microsoft documentation the following statement which warns to use NOLOCK and READUNCOMMITTED hints in query

Note: Support for use of the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement will be removed in a future version of SQL Server. Avoid using these hints in this context in new development work, and plan to modify applications that currently use them.

You can check the documentation from the link https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table for more information. There is no mention of being it the default behaviour.

Upvotes: 2

Pred
Pred

Reputation: 9042

WITH (NOLOCK) behaves the same as setting the transaction isolation level to READ UNCOMMITTED, just its scope is different.

However, keep in mind that SQL Server is allowed to use a different isolation level depending on the type of query you are running. (INSERT, UPDATE, DELETE and other write operations have to run under at least READ COMMITTED).

Your friend is not right, SQL Server will acquire at least a schema lock during read operations on the table to prevent changes in the table's structure. This lock will be applied to the table even if you use READ UNCOMMITTED isolation level or the WITH (NOLOCK) hint.

In general, I would avoid using the WITH (NOLOCK) hint. You have less control when using the table hint. Use READ UNCOMMITTED isolation level for the connection if dirty reads are acceptable.

You can always change the isolation level of the connection, but you cannot dynamically remove the WITH (NOLOCK) hint. This is especially true when you use WITH (NOLOCK) on each and every table.

Upvotes: 9

Jakeuj
Jakeuj

Reputation: 37

Deprecated Database Engine Features in SQL Server 2017

Deprecated feature

Specifying NOLOCK or READUNCOMMITTED in the FROM clause of an UPDATE or DELETE statement.

Replacement

Remove the NOLOCK or READUNCOMMITTED table hints from the FROM clause.

Feature name

NOLOCK or READUNCOMMITTED in UPDATE or DELETE

Upvotes: 2

Related Questions