1：创建一个实验表
mysql> use test;
mysql> CREATE TABLE t
-> (id int(11) NOT NULL DEFAULT 0,
-> num int(11) DEFAULT NULL,
-> 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 | 100 |
| 2 | 200 |
+----+------+
2 rows in set (0.00 sec)
|
|
| |
mysql> USE test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> INSERT INTO t VALUES(3,300);
Query OK, 1 row affected (0.01 sec)
|
mysql> SELECT * FROM t;
+----+------+
| id | num |
+----+------+
| 1 | 100 |
| 2 | 200 |
+----+------+
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
上述查看并没有id=3的列，这里居然成功了！
|
|
mysql> SELECT * FROM t;
+----+------+
| id | num |
+----+------+
| 1 | 100 |
| 2 | 200 |
| 3 | 1000 |
+----+------+
3 rows in set (0.00 sec)
|
|
从Session A整个过程看来，它试图更新一个不存在的记录(id=3)，结果更新成功，并且之后这个记录可以访问。
为什么SessionA第二次检索仍然是2条记录呢？
Innodb内部每个事务开始时，都会有一个事务id，同时事务对象中还有一个read_view变量，用于控制该事务可见的记录范围(MVCC)。
对于每个访问到的记录行，会根据read_view的trx_id（事务id）与行记录的trx_id比较，判断记录是否逻辑上可见。
Session B中插入的记录不可见，原因即为Session A先于session B，因此新插入的数据经过判断，不在可见范围内。对应的源码在row/row0sel.c [4040-4055].
| Session A |
Session B |
mysql> SELECT * FROM t;
+----+------+
| id | num |
+----+------+
| 1 | 100 |
| 2 | 200 |
| 3 | 1000 |
+----+------+
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 | 100 |
| 2 | 200 |
| 3 | 1000 |
+----+------+
3 rows in set (0.00 sec)
|
|
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
|
|
mysql> SELECT * FROM t;
+----+------+
| id | num |
+----+------+
| 1 | 100 |
| 2 | 200 |
| 3 | 1000 |
| 4 | 400 |
+----+------+
4 rows in set (0.00 sec)
提交之后正常可见
|
|
