SQL Server NOLOCK Hint
Where locking causes problems with queries it can be useful to execute those queries with the NOLOCK hint. This specifies that a query should ignore existing locks and create no data locks. However, the hint introduces the potential of data corruption.
A table hint is a Transact-SQL (T-SQL) clause that can be attached to the names of tables in a statement or query. Such a hint aims to modify the execution of a command, perhaps changing how data is accessed, the locks that are used or the calculation of an execution plan.
In most cases hints in general, and table hints in particular, are not required; SQL Server optimises queries and statements sufficiently well. Using hints can add a performance penalty and hints that improved performance in earlier editions of SQL Server may not do so in newer versions. However, there are situations where a hint can improve performance or allow queries to execute that would otherwise be blocked.
In this article we'll look at the NOLOCK hint. This hint can be applied to one or more tables in a query. It changes the data retrieval from those tables to use a read uncommitted isolation level, regardless of the isolation level that is currently in use elsewhere.
The hint is used when you need to read data from a table where locking is a problem. If your query is often blocked, or is part of a transaction that is commonly deadlocked, the NOLOCK hint can prevent this. This is because existing locks on the hinted tables and their indexes are ignored. In addition, the query will not create data locks of its own on the affected tables.
With the modified behaviour, queries that often cause deadlocks will run. Queries that do not have deadlocking issues but that can be blocked can run more quickly. Even queries that do not have locking issues can have improved performance.
The benefits of the NOLOCK hint are useful but come at a cost that means you should be very careful before using it. You should exhaust every other possibility before introducing NOLOCK, or before creating transactions that run with a read uncommitted isolation level. Both introduce the possibility of dirty reads, non-repeatable reads and phantom reads
By design, when you use NOLOCK, all locks that would otherwise block the query are ignored. These locks include those placed by other, uncommitted transactions. When ignored, your query will return data that has been created or updated on those uncommitted transactions and will not return rows that have been deleted. If the other transactions are then rolled back, the retrieved data will not represent the true state of the database.
In some cases this might not be too much of a problem. For example, you might be aggregating data where an error in the overall result is acceptable. However, in many situations retrieving incorrect data is either inconvenient or dangerous. A common mistake with NOLOCK is reading data and using it for the basis of later inserts, updates or deletes. If a transaction that should have affected a query's results was rolled back, this can lead to corruption and data integrity problems.
A less common but equally damaging problem occurs when a concurrently executing transaction induces an index page split. This causes index data to move between database pages. Depending upon the way the information is relocated, a query using NOLOCK may read rows twice, duplicating results, or miss rows altogether. As with the other issues, this type of problem can introduce bugs and data corruption that can be intermittent and difficult to rectify.
In the remainder of the article we'll demonstrate the use of NOLOCK with a simple table. In a test database, run the following script to create a table and populate it with data.
CREATE TABLE People
INSERT INTO People VALUES ('Bob')
INSERT INTO People VALUES ('Jim')
INSERT INTO People VALUES ('Sam')
To demonstrate blocking we need to run two scripts at the same time. Using SQL Server Management Studio, create two query windows for the test database. In the first, run the following. This starts a new transaction before capitalising all of the names in the People table. As the transaction isn't committed or rolled back, this script holds locks on the updated data.
SET Name = UPPER(Name)
In the second query window, run the following. This query attempts to read the data that is locked by the first statement so is blocked, waiting for the other transaction to complete.
Switch back to the first window and roll back the transaction. This will reverse the changes to the data, release the locks and allow the SELECT statement to complete. The query will return the data as it appeared before the UPDATE, as you would expect. This locking process ensures the integrity of the data returned by the query.
To show the difference made by the NOLOCK hint, start the first script again to begin a new transaction and capitalise the names. Again, leave the transaction open.
In the second query window run the query shown below. This time the People table is modified with the NOLOCK hint, using the syntax "WITH (NOLOCK)".
SELECT * FROM People WITH (NOLOCK)
This time the data is returned immediately, as the locks held by the updating transaction are ignored. The data returned includes the uncommitted changes, so each name is in upper case:
If you now roll back the initial transaction you have generated a potential problem. Any code that uses the results of the query, particularly if it performs updates, is using information that is incorrect.
16 April 2013