MySQL中一些不可思议的加锁情况

最近看了极客时间MySQL45讲，有些情况的加锁场景真的是颠覆我的认知，然后写下这篇文章分享一下，这篇文章的内容都是基于MySQL45讲的总结。首先是数据和表的初始化：

CREATE TABLE `t` (
`id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5), (10,10,10),(15,15,15),(20,20,20),(25,25,25);

我使用的MySQL版本是5.7，可重复读的隔离级别下。先看一个小问题：

session A	session B	session C
begin;<br />select * from t where c>=15 and c<=20 order by c desc for update;
	Insert into t values(11,11,11);
		Insert into t values(6,6,6);

估计你一看会觉得这不是非常明显，我建议自己试一试，先不说答案，看完这篇文章你应该就知道为什么了。

先说一下MySQL加锁规律，总结起来就是下面几条:

1. 加锁的基本单位是next-key lock。next-key lock是前开后闭区间.
2. 查找过程中访问到的对象才会加锁。
3. 索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
4. 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
5. 唯一索引上的范围查询会访问到不满足条件的第一个值为止。

案例一:等值查询间隙锁

session A	session B	session C
begin;<br />update t set d = d+1 where id=7
	insert into t values(8,8,8);<br />(block)
		update t set d = d+1 where id=10 (ok)

我们按照上面总结的规律分析一下，7是在(5,10]这个区间内的，然后加锁区间是(5,10]然后是等值查询 next-key lock 退化间隙锁最终结果是(5,10)。

案例二:非唯一索引等值锁

session A	session B	session C
begin;<br />select id from t where c = 5 lock in share mode;
	update t set d = d+1 where id=5 (ok)
		insert into t values(7,7,7);<br />(block)

看到这个的时候我当时就是一句卧槽，这尼玛MySQL出bug了吧，这不是乱加锁，这。。。。我们按前面的几个规律分析一下。

一开始mysql会给c=5加一行读锁。加锁的单位是next-key lock，所以(0,5]。c是一个普通索引，MySQL在找到这条记录的时候并不能确定还有没其他的行，所以要继续查找，直到找到c=10，所以加锁的范围就是(5,10]，类似于案例一，结果就是(0,5)间隙锁，c=5行锁，(5,10)间隙锁。分析到这里感觉很有成就感，但是为啥update t set d = d+1 where id=5执行成功了？只有查找到了的结果才会加锁，也就说这里的行锁是加了c=5，并不会锁id，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁。如果我们把session A的查询换成select * from t where c = 5 lock in share mode;查询的是所有而不是id，那么session B就会被阻塞，不信自己可以试试看。注意session A lock in share mode;如果换成 for update，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。所以我们经常说的加锁是锁主键是有前提的。

案例三:主键索引范围锁

mysql> select * from t where id=10 for update;
mysql> select * from t where id>=10 and id<11 for update;

这两句sql是等价的吗？执行逻辑肯定是等价的，但是加锁逻辑是不一样的，select * from t where id=10 for update;主键等值查询 next-key lock 退化成行锁，就是加了id=10这条锁。对于 select * from t where id>=10 and id<11 for update; 首先找到id=10这一行加个行锁，11是在(10,15]间隙内的所以加锁逻辑就是(10,15]，id=10的行锁即[10,15]。

案例四:非唯一索引范围锁

select * from t where c>=10 and c<11 for update;这种情况相比于案例三差不多，只是在找到c=10这行时不会退化成行锁，而是一个 next-key lock 所以结果（5，10],然后11在(10,15]区间内加上(10,15]。即（5，15]。

案例五:唯一索引范围锁

对应第五条规律，唯一索引上的范围查询会访问到不满足条件的第一个值为止。唯一索引讲道理找到了那一行应该就不会继续往下扫描的，事实上却不是。

session A	session B	session C
begin;<br />select id from t where id >10 and id<=15 for update;
	update t set d = d+1 where id=20 (block)
		insert into t values(16,16,16);<br />(block)

对于select id from t where id >10 and id<=15 for update; 首先加上(10,15] next-key lock，找到了15之后并不会停还会继续往下扫描，所有又会加上(15,20]，就出现了表格中的情况。select id from t where id >=10 and id<=15 for update;这个加锁是怎么加的呢？答案是(5,10],(10,15],(15,20]。

案例六:非唯一索引上存在"等值"的例子

我们在刚刚的数据上加上

insert into t values(30,10,30);

这时候表里的索引c的数据就变为

c	0	5	10	10	15	20	25
Id	0	5	10	30	15	20	25

此时我们执行一条 select * from t where c=10 for update;加锁情况是怎么样的呢？

image-20200912145714893

(c=5,id=5)和(c=15,id=15)这两行上都没有锁。select * from t where c=10 for update limit 2;如果给这个查询上sql加个limit呢？

image-20200912150756175

就是少了一个间隙。

回到我们最开始那个问题，select * from t where c>=15 and c<=20 order by c desc for update;这条sql是怎么加锁的？首先找到c=15并要找到不满足条件的值也就是c=10加锁，加上一个next-key lock 也就是(5,10]，20也是一样的道理找到c=20的不满足情况的一个值25加上next-key lock (20,25]，最后的结果就是(5,10]，(10,15],(15,20],(20,25]。