Pagination on fast changing database content

Question

I need to read the content of a MS SQL database table using pagination, i.e. fetching the first page of N rows, then the second page of N rows and so on.

If the content of the database is changing significantly during the pagination, a simple pagination query like:

SELECT *
FROM (SELECT a.*,
    ROW_NUMBER() OVER (ORDER BY id) AS rnum
    FROM articles a)
WHERE rnum <= 10
AND   rnum >= 6;

may not work reliably. Inserted rows can be skipped or can cause subsequent rows to be repeated and deleted rows can cause subsequent rows to be skipped.

I could avoid such problems by doing any of the following:

1. Lock the rows against update during the entire pagination - too restrictive
2. Copy the rows to a temporary table before paging - too slow
3. Select by a combination of row number and the sorted value that was displayed at the end of the previous page, resuming at an appropriate place based on the changing table, but still getting only the next N rows

I kind of like the 3rd solution, but I find it difficult to implement when there are duplicate values in the sort column(s).

For example, let's assume I have a list of articles sorted by descending rating. If the rating is the same, they are sorted by ascending ID (the IDs are unique):

ID      RATING
9       34
3       32
6       32
8       32
12      32
1       25
2       23

Now, I want pages of 3 articles, which means the first page will have articles 9, 3 and 6. This is done by querying the top 3 articles form the sorted list.

Now, I want to take the next 3 articles resuming from article 8, using the article ID as marker for where to resume.

If I told the database to take the reputation of article 8 and than take the 3 articles which have reputation lower than that, I would skip article 12.

If I told the database to take the reputation of article 8 and than take the 3 articles which have reputation lower than or equal to that, I would repeat article 3 and 6.

What SQL query (or combination of queries) can I use to resume the pagination from article 8, using the article ID as marker for where to resume?

Answer 1

Converting comment to answer, since it seems to have solved the user's question.

So it seems like you should cache the result for that user (e.g. can you send all the IDs to the app and have it just pull 3 articles at a time on each fetch), but also add a disclaimer that if it takes them half an hour to scroll through the list, the list may no longer be accurate.