文章目录
引言
在隔离级别为RR时,MySQL已经可以避免脏读和重复读,但还是无法避免幻读,MySQL采用next-key锁与MVCC(多版本并发控制)来避免幻读.
next-key锁
next-key 锁是索引 record 上的 record 锁和 index record 之前的间隙上的间隙锁的组合。
InnoDB以这样的方式执行 row-level 锁定：当它搜索或扫描 table 索引时，它会在它遇到的索引记录上设置共享锁或独占锁。因此，row-level 锁实际上是 index-record 锁。索引 record 上的 next-key 锁也会影响索引 record 之前的“间隙”。也就是说，next-key 锁是 index-record 锁加上在 index record 之前的间隙上的间隙锁。如果一个 session 在索引中的 record R上有共享或独占锁定，则另一个 session 不能在_le之前的R之前的间隙中插入新的索引 record。
以上是文档中对于next_key的解释,相信第一遍看的时候一定是有点懵逼的,什么意思呢?
假设我们现在有一个表,数据是这样的[(1,‘a’),(3,‘c’),(5,‘e’),(7,‘g’),(9,‘i’),(11,‘k’)],文档的意思是当我们在会话A中查询(9,‘i’)的时候,(7,9]是被锁定的,文档的意思是之前,但是经过测试其实前后两个区间,即(7,9],(9,11]都是锁定的,那么当我们锁定11的时候呢?(9,11](11,positive infinity]都是锁定的,下面是对这个例子的小实验.
执行下述语句
//会话1
mysql root@(none):test_mysql1> create table test_next_key
-> (id int primary key auto_increment,
-> name char(5) not NULL,
-> KEY `index_name` (name));
Query OK, 0 rows affected
Time: 0.030s
mysql root@(none):test_mysql1> insert into test_next_key values(1,'a'),(3,'c'),(5,'e'),(7,'g'),(9,'i'),(11,'k');
Query OK, 6 rows affected
Time: 0.005s
mysql root@(none):test_mysql1> start transaction;
//会话2
mysql root@(none):test_mysql1> start transaction;
mysql root@(none):test_mysql1> insert into test_next_key(id,name) values(2,'b');
Query OK, 1 row affected
Time: 0.001s
mysql root@(none):test_mysql1> insert into test_next_key(id,name) values(4,'d');
Query OK, 1 row affected
Time: 0.001s
mysql root@(none):test_mysql1> insert into test_next_key(id,name) values(6,'f');
Query OK, 1 row affected
Time: 0.001s
mysql root@(none):test_mysql1> insert into test_next_key(id,name) values(8,'h');
^Ccancelled query //Ctrl+C
mysql root@(none):test_mysql1> insert into test_next_key(id,name) values(10,'j');
^Ccancelled query
mysql root@(none):test_mysql1> insert into test_next_key(id,name) values(12,'l');
Query OK, 1 row affected
Time: 0.003s
mysql root@(none):test_mysql1> rollback ;
Query OK, 0 rows affected
Time: 0.000s
mysql root@(none):test_mysql1> select * from test_next_key
+----+------+
| id | name |
+----+------+
| 1 | a |
| 3 | c |
| 5 | e |
| 7 | g |
| 9 | i |
| 11 | k |
| 12 | l |(为什么回滚了还是进入数据库里了?)
+----+------+
根据会话2我们可以看到id为8和10时是没有办法插入的,这也和上面所说的一致,因为(7,9],(9,11]这两个区间是被next_key锁定的,当然当我们查看的是12的时候,此时锁定的就是后面的的区间了.示例如下
//会话1
mysql root@(none):test_mysql1> start transaction;
Query OK, 0 rows affected
Time: 0.000s
mysql root@(none):test_mysql1> select * from test_next_key where name='k' for update;
+----+------+
| id | name |
+----+------+
| 11 | k |
+----+------+
1 row in set
Time: 0.004s
mysql root@(none):test_mysql1>
//会话2
mysql root@(none):test_mysql1> start transaction;
Query OK, 0 rows affected
Time: 0.000s
mysql root@(none):test_mysql1> insert into test_next_key(id,name) values(12,'l');
(1205, 'Lock wait timeout exceeded; try restarting transaction')
那么next-key锁是如何避免幻读的呢,我们不妨先想想什么时候可能出现幻读?
即插入到我们查询范围内的空隙中的时候.举个例子,假设我们有一查询语句SELECT * FROM child WHERE id > 100,此时如果另一个事务向100之后插入一条记录的话,此事务再select就会出现幻读了.再比如SELECT * FROM child WHERE id BETWEEN 80 AND 100,此时如果向[80,100]之间插入数据就会出现幻读.
To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking. InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.
为了防止幻像，InnoDB使用一种名为 next-key 锁定的算法，该算法将 index-row 锁定与间隙锁定相结合。 InnoDB以这样的方式执行 row-level 锁定：当它搜索或扫描 table 索引时，它会在它遇到的索引记录上设置共享锁或独占锁。因此，row-level 锁实际上是 index-record 锁。另外，索引 record 上的 next-key 锁也会影响索引 record 之前的“间隙”。也就是说，next-key 锁是一个 index-record 锁加上在 index record 之前的间隙上的间隙锁。如果一个 session 在索引中的 record R上有共享或独占锁定，则另一个 session 不能在R之前的R之前的间隙中插入新的索引 record。
文档中不但解释清楚了如何避免幻读,还对next_key下了一个定义,即next-key 锁是一个 index-record 锁加上在 index record 之前的间隙上的间隙锁.
这里出现幻读的条件无非有三种,next_key锁都可以很好的解决幻读
where id > n; 会在实际存在的最后一个值上加(N_max, positive infinity)这个gap锁 其他的值加next_key锁BETWEEN lhs AND rhs; 在扫描每一个值时对其加index-record 锁,然后在对这个index的前面的间隙加gap锁where id < n; 会在实际存在的最小值上加 (negative infinity, N_min],其他的值加next_key锁
多版本并发控制(MVCC)
其实MVCC本来的用途是解决写时加锁不能读的问题,也就是增加读的速度,可以代替行锁.说实话,这玩意确实很像乐观锁,它们的关系我现在还太才疏学浅,没办法理清楚,可参考这篇博客.但其确实可以解决幻读,MVCC逻辑比较简单,基本内容可参考这篇博客.
两者的区别
既然都可以避免幻读,那么它们有什么区别呢?
答案就是,在快照读时使用MVCC,在当前读时使用next_key锁.
那么什么是快照读,什么又是当前读呢?
-
快照读：读取的是记录的可见版本（有可能是历史版本），不加锁。
场景：select -
当前读：读取的是记录的最新版本，并且当前读返回的记录会加锁，保证其他事务不会再并发修改这条记录。
场景：update、insert、delete
其实也很好想,当我们select的时候,因为MVCC的特性使得我们根本不需要锁,因为MVCC所加的记录删除时间的列会帮我们筛选掉幻读的行,从而在不加锁的情况下避免幻读.但是此时数据仍然是可以加入表的.但是当我们需要对表进行修改的时候就不一样了,此时MVCC显然无法满足要求,我们需要保证在一个区间插入的时候其他会话不在此区间进行插入,所采取的策略就是next_key锁,关于next_key锁,上面讲的很清楚了.
总结
一个小小的知识点却能牵引出这么多东西,平时忽略的小知识点不知道让我们遗漏了多少本该掌握的东西,还是要始终保持着一颗好奇与敬畏之心才可.
参考
https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html#innodb-next-key-locks
https://dev.mysql.com/doc/refman/5.6/en/innodb-next-key-locking.html
https://www.docs4dev.com/docs/zh/mysql/5.7/reference/innodb-next-key-locking.html
MySQL之Innodb锁机制：Next-Key Lock 浅谈 ps:详尽的测试