The default transaction isolation level in SQL Server is the READ COMMITTED isolation level, in which retrieving the changing data will be blocked until these changes are committed. The WITH (NOLOCK) table hint is used to override the default transaction isolation level of the table or the tables within the view in a specific query, by allowing the user to retrieve the data without being affected by the locks, on the requested data, due to another process that is changing it. 

In this way, the query will consume less memory in holding locks against that data. In addition to that, no deadlock will occur against the queries, that are requesting the same data from that table, allowing a higher level of concurrency due to a lower footprint.

Note:

Although the NOLOCK table hint, similar to all other table hints, can be used without using the WITH keyword, Microsoft announced that omitting the WITH keyword is a deprecated feature and will be removed from future Microsoft SQL Server versions. With that said, it is better to include the WITH keyword when specifying the table hints. One benefit of using the WITH keyword is that you can specify multiple table hints using the WITH keyword against the same table.

What does the SQL Server NOLOCK hint do?

  • The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not being blocked by other processes.
  • This can improve query performance, but also introduces the possibility of dirty reads.
SELECT * FROM Person.Contact WITH (NOLOCK)

Issues with NOLOCK

We mentioned above how you can get dirty reads using the NOLOCK hint. These are also other terms you may encounter for this hint.

  • Dirty Reads – this occurs when updates are done, so the data you select could be different.
  • Nonrepeatable Reads – this occurs when you need to read the data more than once and the data changes during that process
  • Phantom Reads – occurs where data is inserted or deleted and the transaction is rolled back. So for the insert you will get more records and for the delete you will get less records.

Sources:

https://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/

Last modified: December 20, 2019

Author

Comments

Write a Reply or Comment