Rajendra Gupta
READPAST in the update statements

Explore the SQL query table hint READPAST

November 14, 2020 by

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.

Now, if you select the records without using any table hint, it returns all rows as shown below.

Sample data

A user starts an explicit transaction to update the [ProductName] for the ID 1 and 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.

It returns a row with the locks’ information using a hyperlink.

Check existing locks for SQL Queries

Click on the hyperlink in the locks column, and it shows the exclusive lock on the page.

Click on the hyperlink

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)

transaction is still open

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.

Dirty read

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 hint ignores locked rows

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.

READPAST in the update statements

Start an explicit transaction to update the [PostCode] for the [AddressID] equals to 1.

We can verify the transaction locks using the sp_whoISactive stored procedure.

View SQL Locks

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.

It updates three records in the [Persons] table as shown in the output.

Update statement

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

skips the first row

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.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views