mysql - 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?

标签： mysqldatabasetransactionstransaction-isolationrepeatable-read

Answer 1

Q：“但是让我困惑的是，第一次选择应该只创建第一个100条记录的快照（有限的100条记录）。但是为什么第二次选择没有返回新插入的记录？”

答：因为新插入的记录在您的事务上下文中基本上“不存在”。

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

可重复阅读

这是 InnoDB 的默认隔离级别。同一事务中的一致性读取读取第一次读取建立的快照。这意味着，如果您在同一事务中发出多个普通（非锁定）SELECT 语句，这些 SELECT 语句也相互一致。