SQL SERVER – Simple Example of READPAST Query Hint

Last week, I had an interesting situation where my client of Comprehensive Database Performance Health Check wanted to display all the available seats in their Cinema hall which are not being processed. The required was very unique and we were able to solve the problem with the help of READPAST query hint.

SQL SERVER - Simple Example of READPAST Query Hint readpast1-800x291

History of Seat Booking

The way the cinema hall seat booking worked for my largest European client was that every seat has to go through various stages because they were marked as confirmed. This kept the rows of the seats locked and engaged. However, the issue was that when we were displaying the entire cinema hall’s availability map we needed to show all the available seats at that time for display purpose.

Finally, we decided to use a very simple technique of using query hint READPAST. Let us see how we could do that in the following section.

READPAST Query Hint

When READPAST table hint is used, the database engine does not read rows locked by other transactions and just simply skip them. Please note that this query hint is very different from the nolock query hint. As we have discussed a lot about the nolock hint on this blog we will not discuss that in this blog post.

First, let us create a table with a few rows.

CREATE TABLE t1 (ID INT)
INSERT INTO t1 VALUES (1),(2),(3),(4),(5)
GO

Next, we will create two side by side sessions.

Run the first transaction in the first session. The following session will lock the row where ID is equal to 1, 2, and 5.

BEGIN TRANSACTION
UPDATE t1
SET ID = ID
FROM t1
WHERE ID IN (1,2,5)

Now once this is executed, run the following command in the second session.

SELECT *
FROM t1 (READPAST)

When you run the above statement the query will skip all the lock rows and display the available rows. This is exactly what my end-user wanted.

SQL SERVER - Simple Example of READPAST Query Hint readpast

If we do not have query hint readpast we had two possibilities

  1. Use No Hint – well, in that case, the second statement would have waited a long time and only produced results after the first result was committed or rolled back.
  2. Use NoLock Hint – this was not a good idea as it would have displayed all the rows which were still locked by users.

I guess, in our scenario READPAST hint was a perfect solution as it skipped all the locked rows.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Query Hint, SQL Scripts, SQL Server, SQL TempDB, SQL Transactions
Previous Post
SQL SERVER – Is Stream Aggregate is Same as Gather Streams of Parallelism?
Next Post
SQL SERVER – Sample Long-Running Query for Demonstrations

Related Posts

6 Comments. Leave new

  • I tried READPAST using the query given in the example, but it doesn’t work, It still gives the output as 1,2,3,4,5.

    Reply
  • Hi Pinal,

    I tried in new session but still it is not giving any data running long time but no data.

    Reply
    • I am sorry, I do not know how I will be able to help as I tried it again and it is working. I have asked a few of the other blog readers and it is working for them too!

      Reply
  • Did you use tempdb?

    If READ COMMITTED SNAPSHOT is enabled in the current database, the example won’t work. Any locked data is read from the snapshot of committed data, so… it doesn’t need to READPAST anything.

    It’s not possible to turn that setting on in tempdb. (I just tried).

    Reply
  • Lewdmill Thinkoff (@lewdmill)
    August 19, 2021 2:11 pm

    That’s because SQL Server got smarter and realizes that SET ID = ID doesn’t change anything. Try using SET ID = -ID instead and it will work.

    Reply

Leave a Reply