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:
After ROLLBACK the result will be different:
USE TestDB GO SELECT * FROM TestTable
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:
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:
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:
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:
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’:
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:
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
- Check out this related information:
