0

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?

Danny
  • 5
  • 4
  • I think it depends on whether the new row is inserted into the first page of output. – Barmar Sep 24 '19 at 21:47
  • I tried it didn't. 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? – Danny Sep 24 '19 at 21:50
  • There are also gap locks. – Barmar Sep 24 '19 at 21:53
  • What you mean by the "first page of output"? The new records are only appended. It actually will not insert in-between. – Danny Sep 24 '19 at 21:54
  • I meant if you read ID 1 3 6, and they insert id = 2, it changes where `LIMIT 100, 101` starts from. – Barmar Sep 24 '19 at 21:58
  • I see. In this case, the second transaction would only append. It won't do any insertion in between. – Danny Sep 24 '19 at 22:01

1 Answers1

0

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.

paulsm4
  • 114,292
  • 17
  • 138
  • 190
  • Yes, but the second select isn't the same as the first one, right? The first one selects the first 100, but the subsequent one select the second 100 and so on. So the question is: is the snapshot established by the first select is covering the entire table or just the first 100? – Danny Sep 24 '19 at 21:56
  • Why don't you answer your own question by substituting a "count(*)" (with no "limit") for both "selects" inside the same transaction. Ensure the new "inserts" occur while the transaction is still in progress. Let us know what you find. – paulsm4 Sep 24 '19 at 23:43