MySQL read returns later rows but not earlier ones

Data is inserted into a table from multiple servers @ the rate of 100-200 inserts/second. Each of the servers reads all the rows in that table in id order and processes the data in a timer thread that runs every few seconds. The last id read in is stored in memory and used again during the next query.

What is happening is that some rows are not read by the function reading the data; they are skipped when there is a high rate of inserts.

Here is the sce开发者_Go百科nario.

The function reading the data had read in data till id 100. It now tries to read rows with id's > 100. The rows returned are 101,102,104,105. Row 103 is skipped (even though it exists).

The current working theory of what is happening is that since data is inserted by multiple servers, row 103 has not been commited when the read happens, so is not returned as part of the resultset. The commit likely happens after this read. The transaction isolation level is set to 'REPEATABLE-READ'.

Has anyone faced something like this before? How was it dealt with. Any suggestions are welcome.

EDIT : I'm looking for a way to read all the rows in order without any gaps.

your guess is almost certainly what is happening.

there are uncommitted inserts. the sequence is still incremented as soon as the other thread uses it, but you do not see the row until commit.

your result set will be read-consistent from the time the query is initiated, so even if the row is committed during the returning of the results, you still wont see it until following queries.

what are you looking for a suggestion to do? you will never be able to read an uncommitted row.