接着上一篇笔记: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))
(mysql@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
开始实验:
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)
(mysql@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;
+----+
| 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