SQL Server creates an optimized execution plan based on the available inputs such as statistics, indexes. By default, it chooses a cost-optimized execution plan and executes the query. Sometimes, we use SQL queries table hints to override the default mechanism. Developers popularly use WITH (NOLOCK) query hint in a Select statement to avoid blocking issues.
You can refer to this article, SQL Server table hints – WITH (NOLOCK) best practices for more details.
In this article, we will explore the READPAST query hint and its implications in SQL queries.
READPAST Query Hint
If we specify the READPAST hint in the SQL queries, the database engine ignores the rows locked by other transactions while reading data. Suppose you have a transaction that blocked a few rows in a table for updating the information in those rows. Now, if another user starts a transaction and specifies the READPAST query hint, the query engine ignores these rows and returns the remaining rows satisfying the data requirement of the query. It might return incorrect data as well.
To understand the READPAST SQL queries table hint, let’s create a test table and insert a few records in it.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Use MyData Go Create table Products ( ID int identity(1,1) PRIMARY key clustered, ProductName varchar(20) ) Insert into Products ([Productname]) values ('Laptop') Insert into Products ([Productname]) values('Mouse') Insert into Products ([Productname]) values('Keyboard') Insert into Products ([Productname]) values('LAN Cable') Insert into Products ([Productname]) values('Wireless Router') |
Now, if you select the records without using any table hint, it returns all rows as shown below.
A user starts an explicit transaction to update the [ProductName] for the ID 1 and 2.
|
1 2 3 4 5 |
BEGIN TRANSACTION UPDATE Products SET [ProductName] = 'Mac' FROM Products WHERE ID IN(1,2) |
After executing the above update statement, check the locks using the Microsoft MVP Adam Machanic stored procedure SP_WHOISACTIVE. You can explore this procedure using this article, An overview of the sp_WhoIsActive stored procedure.
|
1 |
Exec sp_WhoIsActive @get_locks = 1 |
It returns a row with the locks’ information using a hyperlink.
Click on the hyperlink in the locks column, and it shows the exclusive lock on the page.
While the above transaction is still open (not committed or rolled back), start a new query window and try to read all rows from the table without any query hint. The Update statement blocks the select statement (Select * from Products)
Kill the select statement session and re-run it with the NOLOCK query hint. It returns all rows from the [Products] table, but it reads the uncommitted data as well. We haven’t performed a commit for the update statement but still using the NOLOCK SQL queries hint, it reads the updated values.
Let’s run another select statement, but this time use READPAST query hint. Look at the output. We have not put a where clause in the select statement to filter the records, but still, it shows only three records in the table.
We already started another transaction to update the [Products] table ID 1 and 2. Due to the locks on these rows, READPAST hint ignores these rows and returns the remaining rows as shown below.
READPAST in the update statements
In the above section, we explored that READPAST ignores the locked rows while reading data from a table. In the article, How to UPDATE from a SELECT statement in SQL Server, we explored to update the records in a table using SQL Joins. It is like a combination of the select and update statements. To understand the issue with the READPAST here, create the [Persons] and [AddressList] table from the link shared above.
We have the following data in the [Persons] and [AddressList] tables.
Start an explicit transaction to update the [PostCode] for the [AddressID] equals to 1.
|
1 2 |
begin tran update AddressList set PostCode=11111 where AddressId=1 |
We can verify the transaction locks using the sp_whoISactive stored procedure.
Now, in a new query window, execute the following update statement. It joins the [Persons] and [AddressList] columns and updates the records in the [Persons] table as per the corresponding matching row in the [AddressList] table.
We specified the SQL queries hint READPAST in the [AddressList] table.
|
1 2 3 4 5 6 7 8 |
UPDATE Per SET Per.PersonCityName=Addr.City, Per.PersonPostCode=Addr.PostCode FROM Persons Per INNER JOIN AddressList Addr (READPAST) ON Per.PersonId = Addr.PersonId |
It updates three records in the [Persons] table as shown in the output.
Let’s view the records in the [Persons] table, and it gives the following information.
- It updated three rows in the [Persons] table as highlighted in the box below
- It skips the first row because this row is locked from the update statement
Why you should be cautious about using READPAST hint
As demonstrated, READPAST does not read the locked rows. It ignores those rows and gives output to the user without any warning or error message. If you are getting inconsistent data (logical, not actual) from your tables, you should review whether you are using the READPAST hint.
Recently, I looked at a scenario in which the developers say SQL Server is not behaving correctly. We have a stored procedure that has an insert and update statements. The insert runs fine but sometimes the update statement does not work. The stored procedure is also executing without any warning message.
It was an exciting scenario. By carefully, looking at the stored procedure, we identified that it uses READPAST query hint for the update statement as we saw in the above update statement.
You should be careful while using the SQL Queries hint READPAST. It might be suitable for a specific scenario where you do not want to read the locked row. It might be like a ticket booking application. If a user has selected a seat and a transaction is in progress, we do not want other users to access the information.
Conclusion
In this article, we looked at the usage of READPAST SQL Queries hint. It might be a cutting-edge sword that might put you on risk if you do not implement it carefully. Do not implement any trace flag in the production servers directly. Always test your query and database behavior after implementing the trace flag as it overrides the default query optimizer mechanism.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023