Missing rows (gaps) on range read from the end of the table On a data processing pipeline, getting gaps in returned data.

Process A - inserts sequentially into a table. Table has an auto-increment primary key id.

Process B - selects max(id) [into X] and then attempts "select * between id X-100 and X".

On occasion the number of rows returned is less than 100, but on rerun the query returns them all.

Tried adding "lock in share mode" to the select. Did not seem to help with getting all the data. [Expected it to have the reader wait until all current writes are finished, and then return all rows.]

Reads are done on a MySQL replica. Table is InnoDB.
You are inserting things into a single table.  That table is acting as a "queue".  Each 'insert' is effectively (even if it is not written this way):

BEGIN;
get an AUTO_INCREMENT id -- but not yet visible, nor replicated
do some stuff (including INSERTing the row)
COMMIT;
send to Slave(s) -- only after COMMIT


Since you have multiple threads doing this, the getting of the ids is not synchronized with the replication, so the ids arrive out of order at the Slave(s).

Instead of depending on AUTO_INCREMENT values to arrive in order, devise some other way to discover "new" items in the queue.  Note that no technique can precisely keep the items in order.

One technique it to use an UPDATE to flag, say, 100 rows as being handled by your Slave 'worker'.  But, beware, if the worker dies, you will need a 'reaper' to figure out what really got processed.

If you would like to discuss this further, please describe the data flow more precisely ("processing pipeline" is too vague).  I encountered your specific problem more than a decade ago, and have since dealt conjured up multiple solutions.

My main solution is "Don't queue it, just do it."  This came from analyzing a system that was running too slow; it turned out that the queuing (especially in a cross-country replication environment) was more overhead than the processing!  Ripping out the queue allowed nearly 10x capacity!