MySQL中innodb的行锁算法

众所周知，innodb是默认行锁，当然也支持表锁。如下是对于行锁的算法进行的一些实验。

锁的算法为：我知道是行锁，但是是如何锁的，锁多少数据

行锁算法分类

record lock: 锁一行
gap lock ：锁两个索引之间的区间
next-key lock record lock + gap lock 既锁行，也锁范围
示意图：

图片来自掘金

假如有个索引是：[1，2，3，7]
record lock 锁的是 1，2，3，7
gap lock 锁的是 (- $\infty$ ，1)，（2，3），（3，7），（7，+ $\infty$ ）反正锁的就是区间，不是行
next-key lock锁的是 (- $\infty$ ，1]，[2，3），[3，7），[ 7，+ $\infty$ ）既锁范围也锁行

做实验的表如下：

CREATE TABLE `t2` (
   `id` int(11) NOT NULL,
   `a` int(11) DEFAULT NULL,
   `b` int(11) DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `a` (`a`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Innodb锁算法规则如下：

不加索引的查询，会锁整张表

image.png

image.png
innodb通过索引来锁定数据。
当数据库操作使用了主键索引，则锁主键索引，使用辅助索引，则先锁住辅助索引，再锁住主键索引。
当查询的是唯一索引的时候(注意这里的条件是等值查询，假如是范围查询依然是next-key lock 下文有讲)，next-key lock 会退化为record lock
innodb对于辅助索引有特殊的处理，不仅会锁住辅助索引所在的范围，还会锁住下一键值的gap lock.
innodb使用next-key lock 来解决幻读

innodb到底使用什么锁算法

在可重复读隔离级别下，innodb默认使用的是next-key lock算法，当查询的索引是主键或者唯一索引的情况下，才会退化为record lock，在使用next-key lock算法时，不仅仅会锁住范围，还会给范围最后的一个键值加一个gap lock。

实验

使用主键索引会退化为record lock

image.png

仅仅锁主键ID=1的这一行，不影响其他操作，但是查id=1的则会锁等待

image.png

image.png

其中lockmode中的X锁为左边会话中的锁，因为需要显式的commit之后才会释放锁，第二个S锁，为右边的共享锁，因为主键ID为1的已经被锁住了，所以处于锁等待状态，锁的类型为record lock

使用辅助索引会先锁辅助索引，再锁主键索引，且锁算法为next-key lock 且需要对下一个键值加gap lock

image.png

image.png

使用辅助索引a=8进行操作，这个时候理论应该对主键索引加record lock 则主键ID=8的被锁，然后辅助索引被加next-key lock 则为：
(7,8] 然后对下一个键值加gap锁，则为：（8，11）
所以目前被锁住的记录为：
1.主键为8的被锁
2.辅助索引8的被锁
3.辅助索引8到11之间的被锁，意味着你这个时候往8到11之间写数据会报错

主键查询被阻塞，意味着主键被锁

辅助索引为8的被阻塞

插入辅助索引为10的被锁，意味着gap lock锁住了8到11之间的数据了

但是查询表，此时的lockdata为11.11，这一点不太明白

插入12的值则顺利插入

查询辅助索引a=3的顺利查询，因为a=3的记录，既没有被主键id=8的锁住，也没有被辅助索引a的next-key lock 和gap lock锁住

间隙锁的另一种产生方式：

image.png

image.png

image.png

当使用范围条件进行更新时，此时肯定是需要加X锁的，我是用的也是主键，所以按照理论应该是加的record lock ,但是却加了gap lock，因为插入值为10的阻塞了，查看information 也提示X.GAP
这个有点晕为啥主键变成了next-key lock ，不应该是record lock么？
update20200515
在知乎看到的一个解释：

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）

即，在无论使用主键索引还是非主键索引的时候，请求共享锁或者排他锁，innodb会给范围内的记录加锁，而范围内的间隙也会被加锁，
例如一个表t 的 id为1，2，3，7，10
假如执行如下：
select * from t where id >=3 for update
那么这个时候执行
insert into t(id) values(8) 会被阻塞，因为是在请求排他锁时使用了范围，所以[3,10],甚至10以后的任何数据都无法插入。
执行
select * from t where id >=3 lock in share mode
insert into t(id) values(8) 会被阻塞，因为是在请求共享锁时使用了范围，所以[3,10],甚至10以后的任何数据都无法插入。

next-key lock可以解决幻读问题：

什么是幻读

幻读是同一事务下，连续执行两次同样的sql可能导致不同的结果，第二次返回的数据可能导致以前不存在的行。
同时一般会问它和脏读的区别，脏读为读取到其他事务未提交的数据，但是幻读是读取的其他事务已经提交的数据。

复现幻读：

复现幻读需要切换到读提交的隔离级别

image.png

假如是可重复读隔离级别，则会对[8,+）进行加锁，则10就不会被写进去。

reference：