The default behavior in SQL Server is for every query to acquire its own shared lock prior to reading data from a given table. This behavior ensures that you are only reading committed data. However, the NOLOCK table hint allows you to instruct the query optimizer to read a given table without obtaining an exclusive or shared lock. The benefits of querying data using the NOLOCK table hint is that it requires less memory and prevents deadlocks from occurring with any other queries that may be reading similar data. The only drawback is that using the NOLOCK table hint may accidentally result into reading uncommitted “dirty” data.
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.
Note:
Support for hints without the WITH keyword will soon be deprecated
Sources:
https://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/
Comments