SQL Server table hints – WITH (NOLOCK) best practices

SQL Server table hints are a special type of explicit command that is used to override the default behavior of the SQL Server query optimizer during the T-SQL query execution This is accomplished by enforcing a specific locking method, a specific index or query processing operation, such index seek or table scan, to be used by the SQL Server query optimizer to build the query execution plan. The table hints can be added to the FROM clause of the T-SQL query, affecting the table or the view that is referenced in the FROM clause only.

One of the more heavily used table hints in the SELECT T-SQL statements is the WITH (NOLOCK) hint. 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. In other words, the WITH (NOLOCK) table hint retrieves the rows without waiting for the other queries, that are reading or modifying the same data, to finish its processing. This is similar to the READ UNCOMMITTED transaction isolation level, that allows the query to see the data changes before committing the transaction that is changing it. The transaction isolation level can be set globally at the connection level using the SET TRANSACTION ISOLATION LEVEL T-SQL command, as will see later in this article.

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.

In general, using explicit table hints frequently is considered as a bad practice that you should generally avoid. For the NOLOCK table hint specifically, reading uncommitted data that could be rolled back after you have read it can lead to a Dirty read, which can occur when reading the data that is being modified or deleted during the uncommitted data read, so that the data you read could be different, or never even have existed.

The WITH (NOLOCK) table hint also leads to Nonrepeatable reads; this read occurs when it is required to read the same data multiple times and the data changes during these readings. In this case, you will read multiple versions of the same row.

Phantom reads can be also a result of using the WITH(NOLOCK) table hint, in which you will get more records when the transaction that is inserting new records is rolled back, or fewer records when the transaction that is deleting existing data is rolled back. Another problem that may occur when other transactions move data you have not read yet to a location that you have already scanned, or have added new pages to the location that you already scanned. In this case, you will miss these records and will not see it in the returned result. If another transaction moves the data that you have already scanned to a new location that you have not read yet, you will read the data twice. Also, as the requested data could be moved or deleted during your reading process, the below error could be faced:

Msg 601, Level 12, State 1
Could not continue scan with NOLOCK due to data movement.

The WITH (NOLOCK) table hint is a good idea when the system uses explicit transactions heavily, which blocks the data reading very frequently. The WITH (NOLOCK) table hint is used when working with systems that accept out of sync data, such as the reporting systems.

To understand the usage of the WITH (NOLOCK) table hint practically, let us create a new table using the CREATE TABLE T-SQL statement below:

After creating the table, we will fill it with 100K rows for testing purposes, using ApexSQL Generate, SQL test data generator, as shown in the snapshot below:

Once the table is ready, we will simulate a blocking scenario, in which an update transaction will be executed within a transaction that will begin and not committed or rolled back. The below BEGIN TRAN T-SQL statement will start the transaction that will run the following UPDATE statement on the LockTestDemo table under SQL session number 53, without closing the transaction by committing or rolling it back:

With the table’s data locked by the transaction, we will run another SELECT statement, under SQL session number 54, that retrieves data from the LockTestDemo table, using the SELECT statement below:

You will see that the previous SELECT statement will take a long time without retrieving any records. Checking what is blocking that SELECT query using sp_who2 command with the session number for both the SELECT and the UPDATE statements:

The result will show you that, the previously opened transaction is not performing any action, as the UPDATE statement executed successfully. But due to the fact that the transaction is not committed or rolled back yet, it still blocking other queries that are trying to get data from that table. And the SELECT statement that is running under session 54 is blocked by that transaction that is running under session 53, as shown in the result below:

The previous SELECT statement will keep waiting for the transaction to be killed, committed or rolled back in order to get the requested rows from that table. You can stop the transaction that is running under session 53 from blocking other queries by killing that session using the KILL command below:

Or simply committing or rolling back that transaction, by running the COMMIT or ROLLBACK command under the same session of the transaction, if applicable, as shown below:

Once the locking is released, you will see that the requested rows will be retrieved from the SELECT statement directly as shown in the results below:

The previous solution is not always preferable or applicable, for example, when the transaction that is blocking our queries is critical and not easy to be killed or rolled back, or when you don’t have control over other’s transactions within the database. In this case, the WITH (NOLOCK) table hint is useful here, if you can tolerate the risk of dirty reads or data inconsistency. As mentioned previously, the WITH (NOLOCK) table hint allows you to read the data that has been changed, but not committed to the database yet. If you run the same SELECT statement without killing, committing or rolling back the UPDATE transaction, but this time adding the WITH (NOLOCK) table hint to the table name in the SELECT statement as shown below:

Then checking the SELECT statement status using the sp_who2 command. You will see that the query is running without waiting for the UPDATE transaction to be completed successfully and release the locking on the table, as shown in the snapshot below:

The WITH (NOLOCK) table hint works the same as the READUNCOMMITTED table hint, allowing us to retrieve the data that is changed but not committed yet. The same SELECT statement can be modified to use the READUNCOMMITTED table hint as shown below:

Retrieving the requested data directly, without waiting for the UPDATE statement to release the lock it performed on the table, returning the same result as shown in the result set below:

Take into consideration that, the WITH (NOLOCK) and READUNCOMMITTED table hints can be only used with the SELECT statements. If you try to use the WITH (NOLOCK) table hint in the DELETE statement, you will get an error, showing that it both the WITH (NOLOCK) and READUNCOMMITTED table hints are not allowed with the UPDATE, INSERT, DELETE or MERGE T-SQL statements, as shown below:

Rather than allowing a dirty read at the query level using the WITH (NOLOCK) and READUNCOMMITTED table hints, you can change the transaction isolation level at the connection level to be READ UNCOMMITTED using the SET TRANSACTION ISOLATION LEVEL T-SQL statement below:

This query will also retrieve the same data directly, without using any table hint and without waiting for the UPDATE statement to release the lock it performed on the table, as shown in the result set below:

From the previous results, you may think that this is the perfect solution for such scenarios, where you will get the requested data faster, without waiting for other operations to be committed, taking the risk of having not accurate data. But will the SELECT query that is using the WITH (NOLOCK) table hint negatively affects other processes on the SQL Server? To get the answer, let us first check what type of locks the WITH (NOLOCK) table hint will be granted during its execution. This can be achieved by simply running the sp_lock command with the session number of the running query, while the query is running, as shown below:

You will see from the result that the query that is using the WITH (NOLOCK) table hint will be granted S and Sch-S locking types, as shown in the result below:

From the previous result, you will see that the WITH (NOLOCK) table hint will be granted shared access (S) lock at the database level. The shared access (S) lock is used for reading operation, allowing concurrent transactions to read data under pessimistic concurrency control, preventing other transactions from modifying the locked resource while shared (S) locks exist on that resource, until that locking is released as soon as the read operation completes.

The second kind of locking that is granted to the query using the WITH (NOLOCK) table hint is the schema stability (Sch-S) lock. This lock will not prevent any other transaction from accessing the resources except for the concurrent DDL operations, and concurrent DML operations that acquire schema modification (Sch-M) locks on the same table, that will be blocked while the query is executing. This really makes sense, as you do not need to start reading data from the table then another transaction changes the structure of that table during your data retrieval process. SQL Server Database Engine uses the schema modification (Sch-M) locks while processing the data definition language (DDL) commands, such as adding a new column, dropping an existing column, dropping or rebuilding indexes, to prevent concurrent access to the table, until the lock is released.

My NOLOCK query is blocking!

This means that the NOLOCK naming is not always 100% accurate. Use of the WITH (NOLOCK) table hint, that holds schema stability (Sch_S) lock, can block other queries that attempt to acquire a schema modification (Sch-M) lock on that table. It is a critical issue that you should take into consideration if there are lots of users executing their SELECT queries using the WITH (NOLOCK) table hint, preventing you from making any changes to the table schema or maintenances on the table indexes, being blocked by the schema stability (Sch_S) lock.

Assume that we need to run the below SELECT statement, that is using the WITH (NOLOCK) table hint, under session number 53:

At the same time, we will run the below query, that is dropping an index on the same table and create it again, under session number 58:

Then checking the status of both queries using the sp_who2 command, you will see from the result that, the SELECT statement that is using the WITH (NOLOCK) table hint and running session number 53, is locking the DROP/CREATE INDEX process running under session number 58, as shown clearly below:

If we check the locks that are performed by each query, using the sys.dm_tran_locks system object as in the query below:

You will see that, the DROP/CREATE INDEX process running under session number 58 is waiting to acquire schema modification (Sch-M) lock type. This occurs due to the fact that, the schema modification (Sch-M) lock cannot be acquired while the schema stability (Sch_S) lock that is already granted to the SELECT statement running under session number 53, already exists as shown in the snapshot below:

My NOLOCK query is blocked!

Conversely, since the WITH (NOLOCK) table hint acquires schema stability (Sch-S) lock type, the SELECT statement that is using the WITH (NOLOCK) table hint will be blocked if a schema modification is performed on that table. Assume that we run the below ALTER TABLE T-SQL statement to change the size of the EmpAddress column on the LockTestDemo table, under session number 53:

At the same time, the below SELECT statement that is using the WITH (NOLOCK) table hint will be running under session number 54:

Checking the status of both queries using the sp_who2 commands below:

You will see that the SELECT statement running under session 54 is blocked by the ALTER TABLE statement running under session 54, as shown below:

Then checking the locks that are performed by each query, using the sys.dm_tran_locks system object as in the query below:

It will be clear from the returned result that, the SELECT statement that is using the WITH (NOLOCK) table hint and running under session number 54, will be waiting to acquire schema stability (Sch_S) lock, due to the fact that the schema stability (Sch-S) lock cannot be acquired while the schema modification (Sch_M) lock, that is already granted to the ALTER statement running under session number 53, already exists as shown in the snapshot below:

You can imagine the situation when you are scheduling huge number of reports at night, that are using the WITH (NOLOCK) table hint just to be safe. At the same time, there are maintenance jobs that are also scheduled to rebuild heavily fragmented indexes on the same table!

There are number of best practices and suggestions that you can follow, in order to avoid the problems that you may face when using WITH (NOLOCK) table hint. Such suggestions include:

• Include only the columns that are really required in your SELECT query
• Make sure that your transaction is short, by separating different operations from each other. For example, do not include a huge SELECT statement between two UPDATE operations
• Try to find an alternative to the cursors
• Take care to utilize and benefit from the newly defined WAIT_AT_LOW_PRIORITY option to do an online rebuild for the indexes
• Study reporting vs maintenances schedules well
• Take care to utilize and benefit from the different SQL Server high availability solutions for reporting purposes, such as:
  • Configure the Always On Availability Groups secondary replicas to be readable and use it for reporting
  • Create database snapshots when using the SQL Server Database Mirroring and use it for reporting
  • Use the SQL Server Replication subscriber database for reporting
  • Use the secondary database of the SQL Server Log Shipping for reporting