Suppose there is a table t with columns id (primary key) and name, where name has an auxiliary index. The table contains three rows: 1 green, 2 red, and 3 black.

Assume I open transaction A and execute SELECT * FROM t WHERE name = ‘red’, then open transaction B. At this point, transaction A is operating on an older version and cannot see B’s changes. Now, transaction B updates name = ‘red’ to name = ‘black’ and commits. Later, transaction A queries name = ‘red’. If the auxiliary index only has the latest data, transaction A will only see ‘black’ and not ‘red’. This clearly violates the non-repeatable read principle. To see ‘red’, transaction A would need to read the version of the data from the undo log in the primary key index. How does the auxiliary index indicate which column in the primary key index to read? Where is this pointer located in the auxiliary index? What is the structure of the auxiliary index? If this pointer does not exist, does it mean that the entire primary key index needs to be scanned to satisfy the non-repeatable read requirement?

I would like an explanation of how InnoDB handles this scenario with auxiliary indexes, specifically how it manages pointers to the primary key index to maintain consistency and avoid full table scans for non-repeatable reads.