First we do a simulation and execute the following SQL with the following data:

time Session 1 Session 2 Session 3
1 begin;

select * from t_student where id = 2
2 update t_student set age = age + 1 where id =2;;
3 The begin.

update t_student set age = age + 1 where id =2;;
4 select * from t_student where id =2;

COMMIT
5 select * from t_student where id = 2;
6 COMMIT;

select * from t_student where id = 2;

I show the execution results in the following table:

time Session 1 Session 2 Session 3
1 15
2
3
4 17
5 15
6 17

Analysis: In session 1, the data changed in session 2 can be checked only after the transaction of session 1 is committed. In session 2, the data is updated after the transaction is started, and then the query finds that the data is 17.

In fact, InnoDB uses MVCC (Multi-version concurrency Control), which has its own transaction ID and a maximum transaction ID for each data. For each modification of data in a transaction, a different version is generated.

1) Assume that the data with id = 2 starts with txID = 1000; 2) When session 1 starts, the txID becomes 1001; when session 2 starts, the TXID becomes 1002; similarly, session 3 becomes 1003. At this point, snapshots of different versions are generated. 3) When session 1 reads data in transaction, it adopts snapshot read mode, that is, when session 1 gets a transaction ID of 1001, it only reads data less than or equal to its own version. Therefore, session 1 can only get data with value of 17 in transaction. 4) When updating data, session 2 adopts the current read mode, that is, adding X lock to data, obtaining the latest transaction ID, and reading the latest version data. So before we update it, we read age is 16, and then we add 1 to get 17.

To sum up:

Snapshot reads solve the problem of phantom reads, that is, multiple reads of inconsistent data.

Update, INSERT, and DELETE all perform current reads to prevent concurrent updates from causing data errors or adding X locks.