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.

  • This is one of the reasons why tables are not the best queue system. While you can work around it with Retries and delays, you might be more happy in the future to consider other technologies if order and monotone iteration is important (Kafka for example offers those properties)
    – eckes
    Aug 29, 2018 at 11:37
  • but on rerun the query returns them all If so query unconditionally twice ejecting first result...
    – Akina
    Aug 29, 2018 at 12:29
  • Is Process A multi-threaded? Does the same problem happen on the master?
    – eckes
    Aug 31, 2018 at 17:05
  • @eckes Process A is "multi-threaded" since they originate from different servers. Sep 2, 2018 at 6:55

1 Answer 1


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):

get an AUTO_INCREMENT id -- but not yet visible, nor replicated
do some stuff (including INSERTing the row)
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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.