MySQL REPEATABLE READ: Outer transaction sees inner committed data, but only if its first SELECT is after inner commit

I'm observing an interesting behavior with MySQL's REPEATABLE READ isolation level when dealing with an outer transaction and an inner (simulated PropagationRequiresNew) transaction.

My MySQL version is 8.0.28, and REPEATABLE READ is the confirmed active isolation level for the session.

Scenario 1: Unexpected Behavior

1. Outer transaction starts.
2. Outer transaction executes INSERT statements.
3. A new, inner transaction starts, executes INSERT statements, and then COMMITs.
4. Outer transaction then executes its first non-locking SELECT statement for the relevant data.

Observation: This SELECT in the outer transaction sees both its own INSERTed data and the data COMMITted by the inner transaction.

Scenario 2: Expected Behavior

1. Outer transaction starts.
2. Outer transaction executes INSERT statements.
3. Outer transaction executes a non-locking SELECT statement for the relevant data.
4. At this point, the SELECT correctly sees only the data INSERTed by the outer transaction.
5. A new, inner transaction starts, executes INSERT statements, and then COMMITs. Outer transaction executes another non-locking SELECT statement for the relevant data.

Observation: This SELECT (and subsequent ones) in the outer transaction correctly continues to see only its own INSERTed data and does not see the data COMMITted by the inner transaction.

SQL logs confirm the transaction flows, COMMITs, and no explicit isolation level changes.

Questions: Why, in Scenario 1, does the outer transaction's first non-locking SELECT (which occurs after its own INSERTs and after the inner transaction has committed) see the data committed by the inner transaction, despite running in REPEATABLE READ isolation?

How does performing a non-locking SELECT in the outer transaction before the inner transaction starts (as in Scenario 2) alter the visibility outcome for subsequent SELECTs within that same outer transaction, ensuring it adheres to the expected REPEATABLE READ behavior of not seeing later-committed data from other transactions?

What are the precise rules or mechanics in MySQL's REPEATABLE READ that govern how a transaction's read view is established or affected when its first non-locking SELECT is preceded by its own DML (e.g., INSERT) operations and concurrent committed transactions?

Any insights into MySQL's internal behavior regarding snapshot creation in REPEATABLE READ under these specific conditions would be very helpful.

Answer

MySQL has some unusual transaction isolation principles:

1. Operations with locks (like UPDATE, INSERT, DELETE, SELECT ... FOR UPDATE) always use the latest state (in the table itself).

2. The usual SELECT uses a so-called readview (aka Snapshot in Postgres) - it reads data according to MVCC versions (if a row is updated, its old version is stored in the Undo storage so that it could be read by parallel transactions). So if a readview started at point A, then all the changes made to the table after that point are invisible.

From what you say, it's apparent that the readview is taken at the first MVCC operation (SELECT) in a transaction. Thus all the changes made before the readview was taken are visible.

It'll probably be even more clear if you compare MySQL vs Postgres architecture.

PS: I assume there's no such thing in your code as "inner" and "outer" tx - these are just 2 parallel transactions. And the "inner" and "outer" is just a logical perspective.