NOLOCK

What’s NOLOCK?

NOLOCK is a so called query hint that allows you to override or change the default SQL Server behavior for your query. NOLOCK is a hint that overrides the isolation level for the corresponding query. This works for read-only queries, so you cannot use the NOLOCK hint in UPDATE, DELETE or INSERT statements. The NOLOCK hint is synonymous with the READUNCOMMITED hint, so it changes the isolation level of your query to allow dirty reads, so the query can return data that will not end up in the table at all when a ROLLBACK occurs.

Use of NOLOCK

There are some myths surrounding NOLOCK, but at the same time it is probably the most commonly used query hint in the SQL Server universe. The following queries explain the effects and use of NOLOCK in more detail. In order to understand these queries, you will usually have to work with two sessions simultaneously, so you will need several open query tabs in your Azure Data Studio or SSMS or other query tool of your choice. In the first tab you start a transaction and in this transaction you make an UPDATE on a table:

BEGIN TRANSACTION

UPDATE [Warehouse].[StockItemTransactions]
SET [TransactionTypeID] = 13 WHERE [TransactionTypeID] = 10

Note that the transaction is not terminated. This means that if you now query the table in a second query, you will not get a result back. So open a second query and execute the following query:

SELECT [StockItemTransactionID]
    ,[StockItemID]
    ,[TransactionTypeID]
    ,[CustomerID]
    ,[InvoiceID]
    ,[SupplierID]
    ,[PurchaseOrderID]
FROM [Warehouse].[StockItemTransactions] 
WHERE [TransactionTypeID] = 10

Here you can wait a long time, but you will not see any results. This is of course because in SQL Server READ COMMITTED is the default isolation level and you will have to wait on the table with your read operation for an open transaction. You can see this for example in the results of the following query:

DECLARE @who TABLE(
        SPID INT,
        Status VARCHAR(MAX),
        LOGIN VARCHAR(MAX),
        HostName VARCHAR(MAX),
        BlkBy VARCHAR(MAX),
        DBName VARCHAR(MAX),
        Command VARCHAR(MAX),
        CPUTime INT,
        DiskIO INT,
        LastBatch VARCHAR(MAX),
        ProgramName VARCHAR(MAX),
        SPID_1 INT,
        REQUESTID INT
)

INSERT INTO @who EXEC sp_who2

SELECT [SPID]
    ,[Status]
    ,[LOGIN]
    ,[BlkBy]
    ,[DBName]
    ,[Command] 
FROM @who 
WHERE DBName = 'WideWorldImporters'

Here you can see 2 transactions, one with the status ‘sleeping’ with the command AWAITING COMMAND, which is the still open transaction in which the UPDATE has already run, and one with the status SUSPENDED with the command SELECT, where the column BlkBy contains the SPID of the first transaction, which is blocked by it. If you now cancel the SELECT query and add the NOLOCK hint after the table name, you will get results immediately (or in this case no results, since you changed the transaction from type 10 to 13):

SELECT [StockItemTransactionID]
    ,[StockItemID]
    ,[TransactionTypeID]
    ,[CustomerID]
    ,[InvoiceID]
    ,[SupplierID]
    ,[PurchaseOrderID]
FROM [Warehouse].[StockItemTransactions] (NOLOCK)
WHERE [TransactionTypeID] = 10

If you now execute a ROLLBACK in the first window and then execute the query on the table again, you will get results, since the transactions with type 10 have now been reset. Here you can see the first effect of NOLOCK: you have read an UNCOMMITTED transaction and thus data that was never persisted in the database. Nevertheless, NOLOCK is by far the most used query hint in the T-SQL universe. This is because some myths have been created about it:

1. queries with NOLOCK cannot be blocked by other operations. This is wrong. To see this, the above NOLOCK query executes and while it is running sp_lock in another tab simultaneously. You will see entries for a schema lock on the queried table and for a shared lock on the database. This means that an operation that changes the schema of the table (for example, an index creation or an ALTER TABLE command) can lock a NOLOCK table.
2. queries with NOLOCK do not block other operations. This is definitely not true. Because of the schema lock on the table shown above, a NOLOCK query can of course also block an INDEX creation.
3. if all queries run with NOLOCK, everything will be faster. This is not quite true. Of course, queries that run with NOLOCK do not need to put data into the transaction log and therefore require less memory and are faster. However, there is little point in changing the isolation level for each query at the query level, because on the one hand you will get results like `UNCOMMITED READS’ that are not provided by the DBA (otherwise the database would have a different global isolation level).
4. just do NOLOCK, then everything is fine. If you are overriding the default SQL Server behavior, you should be aware of what you are doing and why. If you use NOLOCK by default, be aware of why and what the side effects might be.

References

• Official Documentation from Microsoft
• Explanation on mssqltips