mysql的加锁续集

前提

继上篇锁文章之后，继续了解锁，事务级别是rr级别，mysql版本是5.7。

首先介绍下需要新认识以下种锁类型：

• 插入意向锁：Insert Intention Locks，属于间隙锁grap类型,插入意向锁只有在insert时会产生，插入意向锁的作用是为了提高并发插入的性能， 多个事务同时写入到同一个索引范围区间内，并不需要等待其他事务完成，不会发生锁等待，也就是说插入意向锁互相不冲突的。
• 临界锁：next KeyLocks，锁住数据的同时也会锁住数据俩边的间隙，是记录锁和间隙锁的组合。
• 记录锁：record locks,顾名思义就是锁数据行的锁，比如x锁，s锁就是记录锁。
• 间隙锁：grap locks,是俩条数据的间隙中插入一个间隙锁，当有间隙锁时不允许Insert插入到该间隙内。

表数据

下面的所有演示都基于此数据，表结构如下

CREATE TABLE `a` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  `e` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`,`b`),
  KEY `aa` (`c`),
  KEY `f` (`d`,`e`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4

表数据如下

id	a	b	c	d	e
1	1	2	5	4	1
10	5	3	2	62	12
12	3	5	4	77	3
15	16	3	2	55	33
20	5	2	2	55	33
22	20	5	12	23	22
23	34	3	10	2	2

临界锁和间隙锁的区别

执行如下语句

select * from a where a=5 for update

由于该sql命中唯一索引啊a,加锁情况如下：

看这个加锁情况需要知道几个知识点：

• mysql中索引使用的b+tree类型，该类型的特点就排序顺序升序,可以看出该唯一索引的每条数据包括3个字段，a、b、id，而每条数据的排列顺序同样是根据a、b、id的ascii🐎来排序的，a字段ascii升序排列，a字段的ascii🐎相同的根据b字段的ascii🐎来排序，b字段的ascii🐎相同的根据id字段的ascii🐎来排序。
• 上篇文章中说唯一索引上不会加gap锁，是针对唯一索引为单个字段的。而这种联合唯一索引仍然是需要加gap锁的，为什么联合唯一索引上需要加gap锁，下边分析。
• gap锁只会加在索引记录上，在主键索引上是不会加的。

上面执行的这条sql可以看出既加了x锁也加了gap锁，这种加锁情况就成为加了临界锁next KeyLocks。

共享锁加锁流程

上篇文章一直提到的都是排他锁加grp锁的情况，这里我们需要讨论下加共享锁的情况下需要加gap锁吗？

首先需要明白加gap锁的目的是防止数据插入进来，造成幻读的情况。知道gap锁的作用之后，分一下几种情况讨论：

• 单字段的唯一索引加锁情况

执行下面的sql,该sql的执行计划索引为primary key。

select * from a where id=12 lock in share mode;

加锁情况如下，可以看出只对id=12这条数据加了s锁，俩边的间隙并没有gap锁,因为id一定是唯一的,肯定是不会在出现id=12的数据的，所以俩边是不需要在加gap锁的。

• 联合唯一索引加锁情况

执行下面的sql,该sql的执行计划索引为a。

select * from a where a=16 lock in share mode

加锁情况如下：可能会有人问了，唯一索引上不是不加gap锁吗？要记住单字段的唯一索引和主键索引上是不需要加gap锁的，因为是可以确定唯一的，而联合唯一索引是确定不了‘唯一的’，这里说的确定不了唯一，不是指唯一索引确立不了唯一，可以取个🌰解释下这个情况，上面sql查的是a=16的数据，如果不加gap锁，别的事务是不是可以新增数据[16,4,40]、[16,2,30]呢,这样如果在本事务中再次查询a=16的时候就出现幻读了呢？mysql肯定是不允许出现幻读的，所以这里肯定是加了gap锁的，也就解答了开篇留下的疑问。

可能这样了又有人问了，那是不是执行如下sql就不需要加gap锁了，因为是可以确定唯一性的,如果你能想到这点，恭喜你，你答对了，如果 命中了联合唯一索引的所有字段，那么是不需要加gap锁的

select * from a where a=16 and b=3 lock in share mode

注意：联合唯一索引加排他x锁的情况和加s锁的情况一致。

• 单字段普通索引加锁情况

执行下面的sql，该sql的执行计划索引为aa。

select * from a where c=5 lock in share mode;

由于不是唯一索引，且如果不加gap锁的话是可以继续插入c=5的数据的，如果真的可以插入，那就造成了幻读,所以单字段普通索引加共享锁时也会加上间隙锁，也就是可以叫做临界锁。

思考下以下俩条可以sql可以插入进去吗？

insert into a(a,b,c,d,e) values(2,6,4,10,4);
insert into a(a,b,c,d,e) values(2,7,10,10,4);

答案是第一条不可以插进去，第二条sql可以插入进去。原因是id是自增的,也就是id肯定比23大，又由于b+tree索引的有序性，导致第一条sql插入的缝隙是[4,12]和[5,1]数据之间,第二条sql插入的sql插入的缝隙是[10,23]和[12,22]的数据之间。

• 联合普通索引加锁情况

该情况和单字段的普通索引一样，都是需要加S锁和gap锁的，也就是临界锁。

• 没有查询到数据的情况

其实这里讨论的是没有查询到数据的时候需不需要加gap锁?针对的是命中联合唯一索引(排除命中所有联合字段)、命中单字段普通索引或者命中联合普通索引的三种情况（其他情况不加gap锁）,其实这3种情况都是一样的，这里只验证联合唯一索引没有查询到数据的情况。

比如执行如下sql

select * from a where a=6 lock in share mode

加锁情况如下，可以看出在[5,3]和[16,3]中间加了gap锁,也就是没有查询到数据的时候也加gap锁。

insert的加锁流程

数据的insert顺序，是先插入聚簇索引还是先插入索引数据?

这个问题其实想想就知道了，索引文件的叶子节点都是存储着主键值，该主键值可能是类似id这样自增的，也可能是inser data中写入的，不管是自增主键还是指定的主键，insert data尝试在索引在索引文件插入，如果可以插入则先插入索引文件接着插入主键文件；如果索引文件间隙有Gap锁则插入失败。

insert的加锁流程

insert data时会先在索引上加一种gap锁,叫做插入意向锁,间隙持有插入意向锁时该间隙仍然可以插入数据，是不冲突，数据插入进去之后同时会给该数据加一个排他锁x锁。

执行下面的sql

insert into a(a,b,c,d,e) values (8,8,6,12,5);

锁情况如下，只画了a、id和aa索引加锁情况，f索引加锁情况类比图中的：

在另一个事务中执行一下sql都将阻塞

select * from a where a=8 for update
select * from a where c=6 for update
select * from a where d=12 for update

insert的主键或者唯一键冲突

当插入的数据造成主键或者唯一键冲突时,分以下俩种情况

• 单字段唯一索引发生冲突

执行如下sql

insert into a(id,a,b,c,d,e) values(12,1111,2,2,2,1);
Duplicate entry '12' for key 'PRIMARY'；

加锁情况如下:可以看到冲突的这条数据加上了s锁,别的任何间隙都没有加gap锁。

• 联合唯一索引发送冲突

执行如下sql

insert into a(a,b,c,d,e) values(3,5,2,2,1)
Duplicate entry '3-5' for key 'a'

加锁情况如下：可以看到此时除了冲突的数据加了个S锁，冲突数据的左边间隙也加gap锁。

• 联合唯一索引发送冲突且on duplicate update key

执行如下sql

insert into a(a,b,c,d,e) values(3,5,2,2,1) on duplicate update key c=c

加锁情况如下:发现冲突的这条数据被加上了x锁而不是s锁，因为on duplicate update key表示的就是无冲突数据直接插入，有冲突则更新冲突数据的相关字段。之所以有update操作,进而导致冲突数据被加上了x锁，且左右缝隙加上了gap锁。

image.png

主键索引一定不会加gap锁吗？

当时索引计划是primary key时且是范围查询，主键间隙也会加gap锁的。

这里也是分俩种情况

• 单字段的主键加锁情况

执行如下sql

select * from a where id>=10 and id <15 for update;

加锁情况如下：可以看到在id为10和12之间、12和15之间加了gap锁,眼尖的同学可能看到1和10之间没有加gap锁,因为primary key是单列的，id就可以确定唯一性，而sql是id>=10,所以id为1和10中间的缝隙只能插入2-9的数据，而不会插入id为10的数据了。

• 联合主键索引加锁情况

这个情况交给同学们自行思考，和上面的联合索引加锁条件是相同，大家可以想想，如果primary key为a字段和b字段时，以下俩条sql的加锁条件

select * from a where a>=3 and a<=8 for update ;
select * from a where a>=3 and b>=3 for update;