A transaction will see the same data that is read by the first statement even though another transaction has committed the data. All consistent reads within the same transaction read the snapshot established by the first read. An exception is a transaction that can read the data changed within the same transaction.
When a transaction starts and executes its first read, a read view will be created and stay open until the end of the transaction. In order to provide the same result set until the end of the transaction, InnoDB uses row versioning and UNDO information. Suppose Transaction 1 has selected a few rows and another transaction has deleted those rows and committed the data. If Transaction 1 is open, it should be able ...
