Keep pagination repeatable if change operations are performed

If one wants to paginate results from a data source that supports pagination we have to go to a process of:

1. defining the page size – that is the number of results to show per page;
2. fetch each page requested by the user using an offset = page number (0 based) * page size
3. show the results of the fetched page.

All this is works just fine not considering the fact that an operation may affect the backend system that screws up the pagination taking place. I am talking about someone inserting data between page fetches or deleting data.

page_size = 10;
get page 0 -> results from 0 to 9;
user inserts a record that due to the query being executed goes to page 0 - the one just shown;
get page 1 -> results from 10 to 19 - the first results on the page is the result on the old page 0.

The described behavior can cause confusion to the viewer. Do you know any practical solution to workaround this problem.

Answer

There are a few schools of thought o this.

1. data gets updated let it be
2. You could implement some sort of caching method that will hold the entire result set (This might not be an option if working with really large Datasets)
3. You could do a comparison on each page operation and notify the user if the total record count changes

.