MySQL 读取返回后面的行，但不返回前面的行
数据以 100-200 次插入/秒的速度从多个服务器插入到表中。每个服务器按 ID 顺序读取该表中的所有行，并在每隔几秒运行一次的计时器线程中处理数据。最后读入的 id 存储在内存中，并在下一次查询时再次使用。
发生的情况是读取数据的函数未读取某些行；当插入率很高时，它们会被跳过。
这是场景。
读取数据的函数已读取数据直到 id 100。现在它尝试读取 id > 的行。 100. 返回的行是 101,102,104,105。第 103 行被跳过（即使它存在）。
当前所发生情况的工作原理是，由于数据是由多个服务器插入的，因此在读取发生时第 103 行尚未提交，因此不会作为结果集的一部分返回。提交可能发生在这次读取之后。事务隔离级别设置为“REPEATABLE-READ”。
以前有人遇到过这样的事情吗？到底是怎么处理的呢。欢迎任何建议。
编辑：我正在寻找一种方法来按顺序读取所有行，没有任何间隙。
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 scenario.
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
你的猜测几乎肯定是正在发生的事情。
有未提交的插入。一旦其他线程使用该序列，该序列仍然会递增，但在提交之前您看不到该行。
从查询启动时起，您的结果集将保持读取一致，因此即使在返回结果期间提交了该行，您仍然不会在后续查询之前看到它。
您正在寻找建议做什么？您将永远无法读取未提交的行。
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.