mysql45讲学习总结（六）—锁（第二弹）

本篇主要为了记录间隙锁和next-key lock相关概念以及加锁规则，在根据一些例子来理解这些加锁规则。

这里简单回顾一下：

表锁：如果查询条件中不包含索引字段，则是表锁。

行锁：行锁只能锁住行，跟行锁有冲突的是“另外一个行锁”

间隙锁：间隙锁是锁住两个值之间的空隙，跟间隙锁有冲突的是“往这个间隙插入一个记录”这个动作，所以某一个间隙可以被多个线程上间隙锁。

Next-Key Lock是行锁和间隙锁的结合体。

无论任何锁的释放，都不是在执行完事务内的某条语句之后，是整个事务提交之后才会释放锁。

从一个例子开始

CREATE TABLE `USER_INFO` (
  `ID` int(11) NOT NULL,
  `USER_ID` int(11) DEFAULT NULL,
  `USER_NAME` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `IDX_USER_ID` (`USER_ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 事务1
delete from USER_INFO where USER_ID = 10

-- 事务2
INSERT INTO USER_INFO VALUES(15, 20, "marry");

可以看到上面示例中有一张USER_INFO表，在ID上有主键索引，在USER_ID上有非唯一索引。这时有两个事务并发，事务1是删除，事务2是插入。

分析一下上锁过程：

因为USER_ID是有非唯一索引，那么对USER_ID索引上Next-Key Lock。(假设事务1比事务2快一点点到达)

上锁的基本单位是Next-Key Lock，上Next-Key Lock为(前一个USER_ID, 10]，前开后闭。
由于USER_ID是非唯一索引，这时还需要向后扫描到第一个不等值才返回(不等于10)，但是由于目前这张表中的最大USER_ID=10，所以这时会变成mysql预设的最大值(+suprenum)。
所以最后锁的范围为(前一个formId， +suprenum)

在步骤2中，由于间隙锁需要锁住一个空间，但是在USER_ID=10之后没有其他数据，才会导致锁住了USER_ID=10之后的所有空间，这也不能理解，由于是非唯一索引，所以无法保证不会在USER=10之后再插入一条USER_ID=10的数据。

所以这个时候的事务2进来是就需要等待事务1提交事务之后才能进行数据插入。

可以自己通过脚本测试一下，验证结论是否正确。开两个SQL窗口，进行两个线程测试。

线程1：

BEGIN ;
delete from USER_INFO where USER_ID = 10
-- 延迟 提交事务
SELECT sleep(10) FROM USER_INFO limit 1;
COMMIT;

线程2：

1
2
3

BEGIN ;
INSERT INTO USER_INFO VALUES(15, 20, "marry");
COMMIT;

加锁规则

原则1：加锁的基本单位就是Next-Key Lock，Next-Key Lock是前开后闭区间。
原则2：在加锁过程中访问到的对象(记录)才会被锁。
优化1：对唯一索引的等值查询，Next-Key Lock退化为行锁。
优化2：索引上的等值查询，从第一个满足等值条件的索引记录开始向右遍历到第一个不满足等值条件记录，并将第一个不满足等值条件记录上的Next-Key Lock退化为间隙锁

下文通过这个脚本中的表和数据为例，进行加锁规则的一些解释。

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);

这时对于主键索引来说，具有的值为0、5、10、15、20、25，以及7个间隙。

1.等值查询间隙锁

sessionA	sessionB	sessionC
begin; update t set d=d+1 where id=7			T1
	Insert into t values(8, 8, 8) (blocked)		T2
		update set d=d+1 where id=10; (Query OK)	T3

在T1时刻，由于没有id=7这条记录，根据加锁规则，锁住B这个间隙，由于Next-Key Lock是前开后闭，所以锁住空间为(5,10]
若表中存在id=7这条记录，则根据优化1，锁住的仅仅为id=7这条记录。但是由于没有这条记录，故进入优化2的加锁规则。根据优化2，等值查询where id=7向右遍历时，遇到的最后一个不满足等值条件的记录时，Next-Key Lock退化为间隙锁，所以T1时刻后最终加锁的范围为(5,10)

所以T2时刻的SessionB操作被阻塞，T3时刻的SessionC操作没有被阻塞。

2.非唯一索引等值锁

sessionA	sessionB	sessionC
begin; select id form t where c=5 lock in share mode;			T1
	update t set d=d+1 where id=5; (Query OK)		T2
		insert into t values(7, 7,7) (blocked)	T3

根据原则1，加锁单位是Next-Key Lock,所以会给(0,5]上Next-Key Lock锁。
由于c是非唯一索引，当访问到c=5这条记录后不会停下来，会继续向后遍历，当遍历到c=10这条记录后停止，所以这时要对(5,10]上Next-Key Lock，但根据优化2，这时Next-Key Lock退化为间隙锁(5,10)

所以最终，在T1时刻，上锁的范围是(0, 5]的Next-Key Lock和(5,10)的间隙锁，故T3时刻，SessionC插入c=7的数据被阻塞。

这里为什么T2时刻的SessionB执行成功？这是因为加锁规则中的原则2，只有访问到的对象才会加锁，因为这条SQL采用的是索引覆盖，所以并不需要访问主键索引，所以也就没有对主键索引上锁，所以sessionB才会执行成功。

注意:lock in share mode只会锁覆盖索引，但是for update不一样，Mysql这时认为接下来要更新数据，会顺便为主键索引上锁。所以如果你要用lock in share mode来给行添加读锁避免数据被更新，就必须绕过覆盖索引的优化，在查询字段中添加索引中不存在的字段，让它必须进行一次回表。

3.主键范围索引锁

这两条SQL虽然在查询结果是一致的，但是它们的加锁规则不太一样。

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

sessionA	sessionB	sessionC
begin; select * from t where id>=10 and id<11 for update;			T1
	insert into t values(8, 8,8); (Query OK) insert into t values(13, 13,13); (blocked)		T2
		update t set d=d+1 where id=15; (blocked)	T3

开始执行时，从id=10开始查询，这里本来应该是(5,10]的Next-Key Lock，由于id是唯一索引，退化为行锁，这时只加了id=10这一行的行锁。
继续向右遍历，找到id=15这条记录后停止，所以这里加(10, 15]，这里要扫到id=15是正常的，只有扫到了这条记录，才会知道需不需要继续往后找。

注意：sessionA的步骤一首次定位id=10是用等值查询来判断的，步骤二继续向右遍历用的是范围查询判断(范围查询并没有在两个优化中，所以这里的(10, 15]不会退化为间隙锁！！！这个要注意，优化只有在等值查询的时候才会发生，我在第一次看的时候当时也突然卡住，没有想明白为什么没有退化为间隙锁！)

4.非唯一索引范围锁

sessionA	sessionB	sessionC
begin; select * from t where c>=10 and c<11 for update;			T1
	insert into t values(8, 8,8); (blocked)		T2
		update t set d=d+1 where c=15; (blocked)	T3

这个与案例3的区别在于通过字段c来进行查询，字段c是非唯一索引。

根据c=10找到了c=10这条记录，所以添加对(5,10]添加Next-Key Lock，由于是非唯一索引，所以不会退化为间隙锁。
继续向右遍历，找到id=15这条记录后停止，所以这里加(10, 15]

综上：这里对索引c上了(5,10]和(10, 15]两个Next-Key Lock。

5.唯一索引范围锁bug

在开头叙述的两个原则，两个优化的加锁原则之外，还有一个加锁bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

sessionA	sessionB	sessionC
begin; select * from t where id>10 and id<=15 for update;			T1
	update t set d=d+1 where id=20; (blocked)		T2
		insert into t values(16, 16,16); (blocked)	T3

sessionA是一个范围查询，根据原则1，这时为(10,15]上Next-Key Lock，并且id是唯一键，所以判断到id=15这一行就应该停止了，但实际上InnoDB会继续向后遍历到第一个不满足条件的行为止，也就是id=20这条记录，所以(15,20]也会上Next-Key Lock。所以这时SessionB和SessionC也会被锁住。

照理来说，扫描到id=15这行后，就可以不用继续往后查询了，但实际上InnoDB实际上还是这么做了。所以在mysql 45讲的作者认为这是一个bug。

6.非唯一索引上存在等值的情况

开头创建的表结构和数据中，没有存在索引c上有等值的数据，所以这里插入一条等值数据。

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

执行之后，表中有两条c=10的数据。下图为索引c的间隙状态如下(上方是c，下方是主键)

这时有两个c=10的记录，他们的主键id值不同（分别是10和30），但是这两个记录之间也是有间隙的。

sessionA	sessionB	sessionC
begin; delete from t where c=10			T1
	update t set d=d+1 where c=15; (Query OK)		T2
		insert into t values(12, 12,12); (blocked)	T3

sessionA在遍历时先访问第一个c=10的记录，这时先对(c=5,id=5)到(c=10,id=10)这个Next-Key Lock
继续向后遍历，直到碰到(c=15,id=15)这一行，遍历停止，根据优化2，这时一个等值查询，向右遍历第一个不满足条件的的行会退化为(c=10,id=10) 到 (c=15,id=15)的间隙锁。

也就是说这个delete语句在索引c上的加锁范围就是下图中标记的区域，这里不包含两边的两条记录，是开区间，即 (c=5,id=5) 和(c=15,id=15)这两行上都没有锁。同时主键索引上锁(10,10,10) 和 (30,10,30) 两行,是行锁。

7.limit语句加锁

这个是对案例6的一个对照案例。

sessionA	sessionB
begin; delete from t where c=10 limit 2;		T1
	insert into t values(12, 12, 12); (Query OK)	T2

这个例子对比案例6的sessionA，仅在delete语句上加了limit 2，可能你会觉得数据库中c=10的记录只有两条，加不加limit 2删除的效果都是一样的，但是实际上加锁的访问有所不同。

这是因为，案例7的delete语句明确了limit 2的限制，因此在遍历到(c=10, id=30)这一行之后，就已经满足语句了，遍历就结束了。所以索引c的加锁范围就变成了（c=5,id=5)到（c=10,id=30)这个前开后闭区间。所以这时T2时刻的sessionB插入c=12是可以执行成功的。

所以这个例子对我们的实践是有指导意义的，在删除数据的时候尽量加limit，这样不仅可以控制删除的条数，也可以减少加锁的范围。

8.死锁

sessionA	sessionB
begin; select from t where c=10 lock in share mode;		T1
	update t set d=d+1 where c=10; (Query OK)	T2
insert into t values(8, 8, 8)		T3
	ERROR 1213:Deadlock found when trying to get lock; try restarting transaction	T4

session A 启动事务后执行查询语句加 lock in share mode，在索引 c 上加了 next-key lock(5,10] 和间隙锁 (10,15)；
session B 的 update 语句也要在索引 c 上加 next-key lock(5,10] ，进入锁等待；
然后 session A 要再插入 (8,8,8) 这一行，被session B的间隙锁锁住。由于出现了死锁，InnoDB 让 session B 回滚。

实际上步骤2中加Next-Key Lock是两步操作，分成间隙锁和行锁，在步骤2中加间隙锁是不会冲突，但是在加行锁的时候进入了等待，而T3时刻，sessionA插入数据需要等待sessionB释放间隙锁，所以出现了相互等待的情况，从而导致死锁。

虽然在分析Next-Key Lock表示间隙锁和行锁的结合，但是具体执行的时候要分为两段来执行。

9.非唯一索引排序

sessionA	sessionB
begin; select * from t where c>=15 and c<=20 order by c desc lock in share mode;		T1
	insert into t values(6,6,6); (blocked)	T2

由于这里有order by c desc所以首先从c=20开始定位，所以首先加上间隙锁(20, 25)和Next-Key Lock锁(15, 20]。
在索引c向左遍历，需要扫描到c=10这条记录才会停止，所以(5, 10]。所以sessionB会阻塞。
在扫描的过程中，会对c=10，c=15，c=20这三行有数据，且查询字段是select * ，需要进行回表，所以会在主键索引上上三个行锁。

xiaocainiaoya's blog

mysql45讲学习总结（六）---锁（第二弹）