Mysql事务以及四中隔离级别实例2以及InnoDB如何解决当时读的幻读问题

接着上一篇笔记：http://blog.csdn.net/qq_33366229/article/details/78337354
第三级别（重复读）:

第二种情况：

也就是基于上一种情况 事务A在事务B插入数据之前没有进行select 没有建立一个当前时间点的快照，

所以事务A ，在事务B 插入数据并提交后 再进行查询操作就会出现幻读

InnoDB通过Nextkey lock解决了当前读时的幻读问题

Innodb行锁分为:

类型 —————- 说明
Record Lock: 在索引上对单行记录加锁.
Gap Lock: 锁定一个范围的记录,但不包括记录本身.锁加在未使用的空闲空间上,可能是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间.
Next-Key Lock: 行锁与间隙锁组合起来用就叫做Next-Key Lock。锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

创建表：（切记：是key(id)，不是primary key(id)）

([e-mail]) [fandb]> create table t5(id int,key(id));
Query OK, 0 rows affected (0.02 sec)

SESSION_A>insert into t5 values(1),(4),(7),(10);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0


开始实验：
SESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from t5;
+------+
| id   |
+------+
|    1 |
|    4 |
|    7 |
|   10 |
+------+
4 rows in set (0.00 sec)

SESSION_A>select * from t5 where id=7 for update;
+------+
| id   |
+------+
|    7 |
+------+
1 row in set (0.00 sec)


SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_B>insert into t5 values(2);
Query OK, 1 row affected (0.00 sec)

SESSION_B>insert into t5 values(12);
Query OK, 1 row affected (0.00 sec)

SESSION_B>insert into t5 values(5); --被阻塞
^CCtrl-C -- sending "KILL QUERY 93" to server ...
Ctrl-C -- query aborted.
^[[AERROR 1317 (70100): Query execution was interrupted

SESSION_B>insert into t5 values(7); --被阻塞
^CCtrl-C -- sending "KILL QUERY 93" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted

SESSION_B>insert into t5 values(9); --被阻塞
^CCtrl-C -- sending "KILL QUERY 93" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted


SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)


SESSION_A>select * from t5;
+------+
| id   |
+------+
|    1 |
|    4 |
|    7 |
|   10 |
+------+
4 rows in set (0.00 sec)

SESSION_A>commit;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from t5;
+------+
| id   |
+------+
|    1 |
|    2 |
|    4 |
|    7 |
|   10 |
|   12 |
+------+
6 rows in set (0.00 sec)

当以当前读模式select * from t5 where id=7 for update;获取 id=7的数据时,产生了 Next-Key Lock,锁住了4-10范围和 id=7单个record
从而阻塞了 SESSION_B在这个范围内插入数据，而在除此之外的范围内是可以插入数据的。
在倒数第二个查询中,因为 read view 的存在，避免了我们看到 2和12两条数据，避免了幻读
同时因为 Next-Key Lock 的存在,阻塞了其他回话插入数据，因此当前模式读不会产生幻读(select for update 是以当前读模式获取数据)

尽量使用唯一索引,因为唯一索引会把Next-Key Lock降级为Record Lock

创建表（primary key） 
([e-mail]) [fandb]> create table t6(id int primary key); 
 Query OK, 0 rows affected (0.02 sec)

SESSION_A>insert into t6 values(1),(4),(7),(10); 
 Query OK, 4 rows affected (0.00 sec) 
 Records: 4 Duplicates: 0 Warnings: 0

开始实验

SESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from t6;
+----+
| id |
+----+
|  1 |
|  4 |
|  7 |
| 10 |
+----+
4 rows in set (0.00 sec)

SESSION_A>select * from t6 where id=7 for update;
+----+
| id |
+----+
|  7 |
+----+
1 row in set (0.00 sec)


SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_B>insert into t6 values(5); --插入成功没有阻塞
Query OK, 1 row affected (0.00 sec)

SESSION_B>insert into t6 values(8); --插入成功没有阻塞
Query OK, 1 row affected (0.00 sec)

SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from t6;
+----+
| id |
+----+
|  1 |
|  4 |
|  7 |
| 10 |
+----+
4 rows in set (0.00 sec)

SESSION_A>commit;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from t6;
+----+
| id |
+----+
|  1 |
|  4 |
|  5 |
|  7 |
|  8 |
| 10 |
+----+
6 rows in set (0.00 sec)

当 id 列有唯一索引,Next-Key Lock 会降级为 Records Lock