SQL SERVER – Simple Example of READPAST Query Hint

August 31, 2019

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.

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

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.
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://darkslategrey-bat-805937.hostingersite.com)