READPAST Table Hint in SQL Server

The `READPAST` hint allows a query to skip over locked rows during data retrieval operations. When a query using `READPAST` encounters a locked row, it simply continues to the next row without waiting for the lock to be released.

While we are using the NOLOCK in that we are reading the dirty data (Uncommitted data), but READPAST does not read uncommitted data. It only skip locked row and read committed data. When we want to avoid the blocking and ok with missing some rows then we can use the READPAST hint.

Let’s see the example

The way the bus seat ticket booking worked for the user 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 bus seats availability map, we needed to show all the available seats at that time for display purpose. 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.

See the bus seat layout

Multiple users are booking the ticket simultaneously, but when the user A select the seat that seat will not show to other users.

Creating below table

CREATE TABLE Bus_Seat (Seat_ID INT primary key not null, status_cd int default(0), userid varchar(20), booking_dt date)   insert into Bus_Seat(Seat_ID) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14), (15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28)

See the data

Here we are using the below status code

0-      Available

1-      Error

2-      Booking in progress

3-      Booked

Let’s see the user A is selected 2 seats (Seat Number 13 & 14) and try to book.

During the booking User B inquire for seat in this case it shows the all seats except 13 & 14 because both rows are locked. We are doing update in the transaction.

User A

User B

See the available seats.

Here we are not getting the seat number 13 & 14.

If we are not using table hints, we will not get the data because User A transaction is not yet committed or rollbacked. See below.

If we use NOLOCK we will get the uncommitted data.

The main difference between nolock and readpast is that readpast is skip the uncommitted data while nolock is read uncommitted data. In above case we can use the READPAST TABLE hint.