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.
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://blog.sqlauthority.com)
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.
You have to try that in the new session.
I tried in new session but still it is not giving any data running long time but no data.
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!
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).
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.