Repeatable read regarding select * with order by and limit

Question

At the default isolation level of mysql (Repeatable Read), if I issue a select like select * from table_a where column_a = 'a' order by id limit 100, and after a while, I issued another statement within the same transaction like select * from table_a where column_a = 'a' order by id limit 100, 101. Meanwhile, another transaction is appending into this table_a with new records (it won't be insert in-between) and it commits before the first transaction issues the second select. The question is would the second select in the first transaction return the newly inserted results by the second transaction?

Answer 1

Q: "But what puzzles me is that the first select should only create a snapshot of the fist 100 records (the limited 100 records). But why the second select didn't return the newly inserted records?"

A: Because the newly inserted records essentially "don't exist" within the context of your transaction.

https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

REPEATABLE READ

This is the default isolation level for InnoDB. Consistent reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other.