Re: where the snapshot for select in transaction stored

[Rick James]

Yes.

Your transaction hangs on to all the rows as they are at the time you started the transaction. (It uses something akin to a 'transaction sequence number'.) Other transactions are meanwhile hanging onto their 'version' of the rows.

This is, I think, kept in the blocks where the rows live. Plus some extra info in a log for later cleanup.

Eventually, when your transaction finishes (COMMIT or ROLLBACK), the saved copy is can be freed.

Data blocks are "cached" in the "buffer_pool". That means that they are kept in memory until the get bumped out by other things. For a small, quick, transaction, the blocks will stay in memory. But, in theory, the could be bumped to disk. (Hence, my confusing initial answer.)