Compare SQL Server NOLOCK and READPAST Table Hints

By:   |   Updated: 2016-09-22   |   Comments (6)   |   Related: > Locking and Blocking


Problem

The SQL Server database engine has all the necessary functionality allowing developers to write fully isolated transactions. Thanks to this, it's possible to write code which will read only the correct data (no dirty reads, no non-repeatable reads and no phantom reads). However, with higher transaction isolation comes wider lock ranges. In other words, if the transaction isolation level is stricter, blocking will increase. Yet, developers are allowed to write code that reads incorrect data. In this tip we will explore and compare two table hints - NOLOCK and READPAST, that are usually used to avoid blocking, but keeping in mind that reading incorrect data is possible.

Solution

Both, the SQL Server NOLOCK and READPAST table hints avoid blocking (by reading incorrect data) for specific statements, but their ways of accomplishing this are very different. Let's start to investigate these two table hints in detail and find out their differences.

SQL Server NOLOCK Hint

The NOLOCK and READUNCOMMITTED table hints are equivalent. When these table hints are used, dirty reads are allowed. It means that current transaction does not issue shared locks and other transactions are able to modify the data which is being read by the current transaction. Also, exclusive locks do not block the current transaction enabling it to read locked data. As a result, we can read uncommitted data.

Let's move on to the example.  First, we are creating a test environment:

USE master
GO

CREATE DATABASE TestDB
GO

USE TestDB
GO

CREATE TABLE TestTable
(
 ID INT NOT NULL PRIMARY KEY,
 Value CHAR(1)
)

INSERT INTO TestTable(ID, Value) 
VALUES (1,'A'),(2,'B'),(3, 'C')

Now let's illustrate dirty reads. In the first query window we will run the following transaction: 

USE TestDB
GO

BEGIN TRANSACTION Transaction1
 
 DELETE FROM TestTable 
 WHERE ID=1

 UPDATE TestTable 
 SET Value = 'D'
 WHERE Value='C'

 INSERT INTO TestTable(ID, Value)
 VALUES(4,'E'),(5, 'F')
 GO

 WAITFOR DELAY '00:00:05'

ROLLBACK

While 'Transaction1' is running and UPDATE and INSERT statements are completed, in another query window we will run the SELECT statement below:

USE TestDB
GO

--Query 2
--Executed before ROLLBACK 
SELECT * FROM TestTable WITH(NOLOCK)

As we can see the SELECT statement in the second query returns uncommitted data:

SELECT statement in the second query returns uncommitted data

After ROLLBACK the result will be different:

USE TestDB
GO

SELECT * FROM TestTable

Retrieve data from the TestTable table

When using the NOLOCK hint, it is not only possible to read modified data, but also to read the incorrect data, which is a result of changes in data's physical location made by other transactions. The typical example is that the transaction can read the same row more than once. It occurs, because after reading the row using the NOLOCK hint, the value of that row can be changed and as a result the row can change its physical location in an index, therefore the same row can be read again in the same SELECT statement. Let's see how it occurs. We will fill the test data in the TestTable table (we insert 100000 rows to facilitate the testing process, however it is suggested to choose an appropriate amount of data to avoid overloading your server).

USE TestDB
GO

DECLARE @i INT=1

TRUNCATE TABLE TestTable

WHILE @i <= 100000  -- we insert 100000 rows to facilitate the testing process, 
         -- however it is suggested to choose appropriate data to avoid overloading your server
BEGIN
 INSERT INTO TestTable(ID, Value)
 VALUES(@i, 'A')

 SET @i=@i+1
END

Now we are going to run the following query: 

USE TestDB
GO

SELECT ID
FROM TestTable WITH (NOLOCK)
ORDER BY ID

Immediately after executing the query above, we will run UPDATE below in the other query window:

USE TestDB
GO

BEGIN TRANSACTION

 UPDATE TestTable
 SET ID = 111111
 WHERE ID = 1

COMMIT

In this case we will see an example of reading the same row twice in the same SELECT statement. The first row (which becomes 111111-st row after the update) appears twice in the result:

example of reading the same row twice in the same SELECT statement

This is a good example of the same row being read more than once when we are using the NOLOCK table hint.  Dirty reads are also possible when transaction isolation level is READ UNCOMMITTED, however it applies to all reads in the transaction. For equal behavior between the READ UNCOMMITTED isolation level and NOLOCK hint, we must apply the NOLOCK hint to all tables in the query. Suppose we have two tables: 

USE TestDB
GO

CREATE TABLE TableA
(
 ID INT NOT NULL PRIMARY KEY,
 Value CHAR(1)
)

INSERT INTO TableA(ID, Value) 
VALUES (1,'A'),(2,'B'),(3, 'C')

CREATE TABLE TableB
(
 ID INT NOT NULL PRIMARY KEY,
 Value CHAR(1)
)

INSERT INTO TableB(ID, Value) 
VALUES (1,'X'),(3,'Y'),(7, 'Z')

We update data in both tables and keep the transaction open for 5 seconds:

USE TestDB
GO

BEGIN TRANSACTION 

 UPDATE TableA
 SET Value='N'
 WHERE ID=1

 UPDATE TableB
 SET Value='N'
 WHERE ID=3
 GO

 WAITFOR DELAY '00:00:05'

ROLLBACK

The transaction below is in READ UNCOMMITTED isolation level and is reading the data from the tables which are being modified in previous transaction:

USE TestDB
GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT a.ID AS A_ID, a.Value AS A_Value, 
       b.ID AS B_ID, b.Value AS B_Value
FROM TableA a 
INNER JOIN TableB b
 ON a.ID=b.ID

As we can see the second transaction does not wait until the first one is completed and returns dirty data:

second transaction does not wait until the first one is completed and returns dirty data

But if we use the following query instead of the second transaction: 

USE TestDB
GO

SELECT a.ID AS A_ID, a.Value AS A_Value, 
       b.ID AS B_ID, b.Value AS B_Value
FROM TableA a (NOLOCK)
INNER JOIN TableB b
 ON a.ID=b.ID

It will be visible that the second transaction waits until the first is completed and then returns the data:

second transaction waits until the first is completed and then returns the data

To get the same behavior as the READ UNCOMMITTED isolation level, we need to use the NOLOCK hint for all tables participating in the query: 

USE TestDB
GO

SELECT a.ID AS A_ID, a.Value AS A_Value, 
       b.ID AS B_ID, b.Value AS B_Value
FROM TableA a (NOLOCK)
INNER JOIN TableB b (NOLOCK)
 ON a.ID=b.ID

The result is the following:

NOLOCK hint for all tables participating in the query

As you have noticed in the last two examples we use NOLOCK hint without WITH keyword. READUNCOMMITTED, READPAST and some other table hints also work with and without the WITH keyword. However, the missing WITH keyword will be depreciated in the future versions of Microsoft SQL Server, so Microsoft recommends using the WITH keyword with table hints in your code  It is important to note that the NOLOCK(READUNCOMMITTED) hint applies only to data locks. When a table schema is being modified by another transaction (a schema modification (Sch-M) lock exists on the table), queries even with the NOLOCK hint are blocked:

USE TestDB
GO

BEGIN TRANSACTION

ALTER TABLE TestTable ADD NewValue CHAR(1)

WAITFOR DELAY '00:00:05'

ROLLBACK

First we will run the query above and while it is running we will start the query below: 

USE TestDB
GO

SELECT * FROM TestTable (NOLOCK)

We will see that the second query waits until the first one is finished and then return the result. In addition, it will be useful to know that for target tables of INSERT, UPDATE, DELETE or MERGE statements NOLOCK(READUNCOMMITTED) table hint cannot be used.

SQL Server READPAST Table Hint

When the SQL Server READPAST table hint is used, the database engine does not read rows locked by other transactions. Unlike the NOLOCK table hint, READPAST does not allow dirty reads (according the definition - Dirty Reads occur when transaction reads modified but not yet committed data by other transactions). However, when using the READPAST hint reading incorrect data is also possible, because locked rows are skipped, so we can miss some data which meet our criteria. Now we will illustrate this situation with an example of how locked rows are skipped. We are going to repeat the first example, but in the second query we will use READPAST hint instead of the NOLOCK hint: 

USE TestDB
GO

TRUNCATE TABLE TestTable

INSERT INTO TestTable(ID, Value) 
VALUES (1,'A'),(2,'B'),(3, 'C')

BEGIN TRANSACTION Transaction1
 
 DELETE FROM TestTable 
 WHERE ID=1

 UPDATE TestTable 
 SET Value = 'D'
 WHERE Value='C'

 INSERT INTO TestTable(ID, Value)
 VALUES(4,'E'),(5, 'F')
 GO

 WAITFOR DELAY '00:00:05'

ROLLBACK

The second query is opened in the separate window and is executed after the last DML (INSERT) statement is completed in 'Transaction1': 

USE TestDB
GO

--Query 2
--Executed before ROLLBACK 
SELECT * FROM TestTable (READPAST)

The result shows that only the row with Value = 'B' is selected.  This is because this is the only row that was not modified by 'Transaction1':

only the row with Value = 'B' is selected

Rows which are locked by 'Transaction1' (i.e. ID = 1, 3) have been skipped by the second query. Unlike the NOLOCK hint, the READPAST hint can be used for any table referenced in an UPDATE or DELETE statement. In the example below we can see this behavior. Let's run the query below:

USE TestDB
GO

BEGIN TRANSACTION

 UPDATE TestTable 
 SET Value='N'
 WHERE ID=2

 WAITFOR DELAY '00:00:05'

 ROLLBACK

Run the query below when the first is still running: 

USE TestDB
GO

DELETE 
FROM TestTable WITH (READPAST)  
WHERE ID IN (2,3)

After these queries are completed we select the data from TestTable: 

USE TestDB
GO

SELECT * FROM TestTable

We will see that the only row with ID=3 is deleted, because the row with ID=2 has been locked by the first transaction, so it was missed:

only row with ID=3 is deleted, because the row with ID=2 has been locked by the first transaction, so it was missed

In addition, we cannot specify READPAST hint for tables in the INTO clause of an INSERT statement. We can use READPAST table hint only in transactions with isolation level READ COMMITTED or REPEATABLE READ (it is also possible to use READPAST in transactions with SNAPSHOT isolation level, but in this case READPAST must be used with other table hints that require locks, for example UPDLOCK and HOLDLOCK).

Conclusion

Generally reading any uncommitted data or missing some data during a SELECT statement can cause problems, however Microsoft SQL Server allows this kind of reads by using the NOLOCK (or using READUNCOMMITTED transaction isolation level) and READPAST table hints. These hints should be used carefully and the developer should understand their meaning and behavior to avoid mistakes in the application.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2016-09-22

Comments For This Article




Monday, September 26, 2016 - 2:23:57 PM - Timothy A Wiseman Back To Top (43423)

This is an excellent article (after the update) that provides a good overview of those table hints.

Personally, I make fequent, though cautious, use of NoLock. Since it permits dirty reads, it is best not to really on the exact accuracy of a query that uses it.  But, on a busy server the performance difference provided by not taking a lock and not being blocked by (certain) locks can be substantial. So, I frequently use it when I simply need sample data and care more about the structure than the accuracy.  I also use it frequently when I am looking for an aggregate an estimate would be sufficient. If I could safely round the results, then using NoLock is generally fine.

Thursday, September 22, 2016 - 11:44:24 AM - Sergey Gigoyan Back To Top (43394)

Jeremy,

Thank you so much for your quick update.

Sergey

 

Thursday, September 22, 2016 - 11:33:58 AM - Sergey Gigoyan Back To Top (43393)

Steve and György,

Thank you for reading and for your correct notes. You are fully right. Sorry for confusion, I have already edited that part of article.

Thanks

Thursday, September 22, 2016 - 11:03:16 AM - Jeremy Kadlec Back To Top (43392)

Thank you both for the feedback on the tip.  Sergey has provided an update.

Thank you,
Jeremy Kadlec
MSSQLTips Community Co-Leader

Thursday, September 22, 2016 - 2:40:57 AM - Steve Back To Top (43385)

 I'm sorry, but I think you're incorrect when you state "As you have noticed in the last two examples we use NOLOCK hint without WITH keyword. READUNCOMMITTED, READPAST and some other table hints also work with and without the WITH keyword. Moreover, the WITH keyword will be depreciated in the future versions of Microsoft SQL Server, so Microsoft recommends not using the WITH keyword in your code.".

 

I've heard in multiple places that it is the (NOLOCK) WITHOUT the with that will be deprecated, not the with word itself. Could you please link your source as to future deprecation?

http://stackoverflow.com/questions/12112855/withnolock-or-nolock-is-there-a-difference

 

 

Thursday, September 22, 2016 - 1:41:52 AM - György Görög Back To Top (43384)

Sergey, thanks for this article. Actually it could save me lots of work by not having to eter NOLOCKs into auite a number of statements instead of setting a READUNCOMMITED once.

I am a bit confised about the WITH. I see at Microsoft (https://msdn.microsoft.com/en-us/library/ms187373.aspx): 

Omitting the WITH keyword is a deprecated feature: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

You say:

Moreover, the WITH keyword will be depreciated in the future versions of Microsoft SQL Server, so Microsoft recommends not using the WITH keyword in your code.

Are these the same?

 














get free sql tips
agree to terms