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

接着上一篇笔记&＃xff1a;http://blog.csdn.net/qq_33366229/article/details/78337354
第三级别&＃xff08;重复读&＃xff09;:

第二种情况&＃xff1a;

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

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

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

Innodb行锁分为:

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

创建表&＃xff1a;&＃xff08;切记&＃xff1a;是key(id)&＃xff0c;不是primary key(id)&＃xff09;

(mysql&＃64;localhost) [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
开始实验&＃xff1a;
SESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)SESSION_A>select * from t5;
&＃43;------&＃43;
| id |
&＃43;------&＃43;
| 1 |
| 4 |
| 7 |
| 10 |
&＃43;------&＃43;
4 rows in set (0.00 sec)SESSION_A>select * from t5 where id&＃61;7 for update;
&＃43;------&＃43;
| id |
&＃43;------&＃43;
| 7 |
&＃43;------&＃43;
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 interruptedSESSION_B>insert into t5 values(7); --被阻塞
^CCtrl-C -- sending "KILL QUERY 93" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interruptedSESSION_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;
&＃43;------&＃43;
| id |
&＃43;------&＃43;
| 1 |
| 4 |
| 7 |
| 10 |
&＃43;------&＃43;
4 rows in set (0.00 sec)SESSION_A>commit;
Query OK, 0 rows affected (0.00 sec)SESSION_A>select * from t5;
&＃43;------&＃43;
| id |
&＃43;------&＃43;
| 1 |
| 2 |
| 4 |
| 7 |
| 10 |
| 12 |
&＃43;------&＃43;
6 rows in set (0.00 sec)

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

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

创建表&＃xff08;primary key&＃xff09;
(mysql&＃64;localhost) [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;
&＃43;----&＃43;
| id |
&＃43;----&＃43;
| 1 |
| 4 |
| 7 |
| 10 |
&＃43;----&＃43;
4 rows in set (0.00 sec)SESSION_A>select * from t6 where id&＃61;7 for update;
&＃43;----&＃43;
| id |
&＃43;----&＃43;
| 7 |
&＃43;----&＃43;
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;
&＃43;----&＃43;
| id |
&＃43;----&＃43;
| 1 |
| 4 |
| 7 |
| 10 |
&＃43;----&＃43;
4 rows in set (0.00 sec)SESSION_A>commit;
Query OK, 0 rows affected (0.00 sec)SESSION_A>select * from t6;
&＃43;----&＃43;
| id |
&＃43;----&＃43;
| 1 |
| 4 |
| 5 |
| 7 |
| 8 |
| 10 |
&＃43;----&＃43;
6 rows in set (0.00 sec)


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