Small instances of MySQL update data between multiple sessions

1: Create an experiment table

mysql> use test;mysql> CREATE TABLE T    -(ID int (one) not NULL default 0,    num int (one) default null,< C2/>-> PRIMARY KEY (ID)),    Engine=innodb DEFAULT CHARSET=GBK; Query OK, 0 rows affected (0.02 sec)

　　

Mysql> INSERT into T values (1,100);mysql> insert into T values (2,200);

Session A	Session B
Mysql> BEGIN;
Mysql> SELECT * FROM t;+----+------+| ID | Num |+----+------+| 1 | | | 2 | |+----+------+2 rows in Set (0.00 sec)
	mysql> use testreading table information for completion of table and column namesyou can turn off this feature to get a Quicker startup With-adatabase changedmysql> INSERT into T VALUES (3,300); Query OK, 1 row affected (0.01 sec)
Mysql> SELECT * FROM t;+----+------+| ID | Num |+----+------+| 1 | | | 2 | |+----+------+2 rows in Set (0.00 sec)
mysql> UPDATE t SET num=1000 WHERE id=3; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0 The above view does not have the id=3 column, here incredibly succeeds!
Mysql> SELECT * FROM t;+----+------+| ID | Num |+----+------+| 1 | | | 2 | | | 3 | |+----+------+3 rows in Set (0.00 sec)

From session A, it appears that it is trying to update a nonexistent record (id=3), the result is updated successfully, and the record can be accessed later. Why is Sessiona's second search still 2 records?    InnoDB Each transaction starts with a transaction ID and a Read_view variable in the transaction object that controls the range of records (MVCC) that the transaction is visible to.    for each record row that is accessed, the record is logically visible based on the Read_view trx_id (transaction ID) compared to the trx_id of the row record.    the record inserted in session B is not visible because session a precedes session B, so the newly inserted data is judged and is not within the visible range. The corresponding source code is in ROW/ROW0SEL.C [4040-4055].

　　

Session A	Session B
Mysql> SELECT * FROM t;+----+------+| ID | Num |+----+------+| 1 | | | 2 | | | 3 | |+----+------+3 rows in Set (0.00 sec)
	Mysql> INSERT into T VALUES (4,400); Query OK, 1 row affected (0.01 sec)
Mysql> SELECT * FROM t;+----+------+| ID | Num |+----+------+| 1 | | | 2 | | | 3 | |+----+------+3 rows in Set (0.00 sec)
Mysql> COMMIT; Query OK, 0 rows Affected (0.00 sec)
Mysql> SELECT * FROM t;+----+------+| ID | Num |+----+------+| 1 | | | 2 | | | 3 | | | 4 | |+----+------+4 rows in Set (0.00 sec) Normally visible after commit

Small instances of MySQL update data between multiple sessions