SA0108 : Avoid using NOLOCK hint, use isolation levels instead

Introduction

The use of NOLOCK hints can lead to data inconsistency issues.

Description

When a NOLOCK hint is used in SQL queries, it allows reading data without acquiring locks, which means the data can be read simultaneously during updates. While this improves performance by preventing blocking, it risks data accuracy and consistency in SQL Server databases.

For example:

-- Example of using NOLOCK hint
SELECT * FROM TableName WITH (NOLOCK);

This allows reading uncommitted data (dirty reads), which might not reflect the final committed state of the data. Without locks, the data read might be in a transient state, leading to several issues:

Increased risk of retrieving inconsistent data, as the query might read data that is being modified by another transaction.
Potential for reading data that could be rolled back later, leading to incorrect results.
Unpredictable query outcomes, which may affect business logic relying on accurate data.

How to fix

Change the query’s isolation level instead of using the NOLOCK hint to reduce the risk of data inconsistency.

Follow these steps to address the issue:

1.Evaluate the necessity of the NOLOCK hint in your queries. Consider if eventual consistency is acceptable for the given use case.

2.If data consistency is crucial, replace the NOLOCK hint by setting an appropriate transaction isolation level. Use SET TRANSACTION ISOLATION LEVEL for this purpose.

3.Choose an isolation level that balances performance and consistency, such as READ COMMITTED , REPEATABLE READ , or SERIALIZABLE , based on your application’s needs.

4.Modify the query to eliminate the WITH (NOLOCK) hint and set the desired isolation level at the beginning of the transaction.

5.Test the changes to ensure that data consistency issues are resolved and performance remains within acceptable bounds.

For example:

-- Example of setting the isolation level to READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT * FROM TableName;
COMMIT TRANSACTION;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

20 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

--- Table Hints ---
SELECT StartDate, ComponentID FROM Production.BillOfMaterials
    WITH( INDEX (FIBillOfMaterialsWithComponentID), NOLOCK /*IGNORE:SA0108*/ )
    WHERE ComponentID in (533, 324, 753, 855, 924);

SELECT * FROM Sales.SalesOrderHeader (NOLOCK) AS h

SELECT * FROM Sales.SalesOrderHeader AS h (NOLOCK)

Analysis Results

	Message	Line	Column
1	SA0108 : Avoid using NOLOCK hint, use isolation levels instead.	6	38
2	SA0108 : Avoid using NOLOCK hint, use isolation levels instead.	8	43

Analysis Rules