MySQL的锁与锁机制----InnoDB加锁规则

InnoDB加锁规则

如下的加锁规则是参照林晓斌在极客时间的专栏《MySQL实战45讲》，更多细节请参照他的专栏，非常值得推荐。加锁的规则包括2个原则，2个优化与一个bug。
2个原则是：
1、加锁的基本单元是next-key lock，其是前开后闭的区间；
2、查找过程中访问到的对象才会加锁。
2个优化是：
1、唯一索引上的等值查询，next-key lock会退化成行锁（需要存在那个行）；
2、普通索引上的等值查询，向右遍历时且最后一个值不满足等值条件时，next-key lock会退化成间隙锁。
一个bug：
1、唯一索引的范围查询，会遍历到第一个不满足条件为止。
需要注意的是如上的加锁规则是针对于可重复读级别的；对于读提交，去掉上面的间隙锁的部分，以及语句执行完成后，只有满足条件的行，才会加行锁，其他的都释放掉了。这就是为什么说读提交的性能更高了，因为其加锁的范围更小。
间隙锁指的是，在两个元素之间的间隙加上一个锁，防止在间隙中插入元素。比如对于表：

CREATE TABLE t
(
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`n` INT UNSIGNED NOT NULL,
`m` INT UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
KEY `n` (`n`)
)ENGINE=InnoDB;
INSERT INTO t(`id`,`n`,`m`) VALUES(10,10,10),(20,20,20),(30,30,30),(40,40,40);

那么对于如下的SQL语句

BEGIN;
SELECT * FROM t WHERE n=5 FOR UPDATE;
COMMIT; 

那么在可重复读下，为了避免幻读问题，InnoDB会在(0,10)这个间隙之间添加一个间隙锁。我想通过如上的例子，你现在已经知道了什么是间隙锁了，但是什么是next-key lock呢？还是通过举一个例子来说明：

BEGIN;
SELECT * FROM t WHERE n=10 FOR UPDATE;
COMMIT; 

那么InnoDB会在(0,10)之间添加一个间隙锁，在10这一行添加行锁，这个间隙锁与行锁就组成了next-key lock (0,10]。为此next-key lock指的是间隙锁加上间隙锁的右边界的行锁。
那么间隙锁与间隙锁，间隙锁与行锁之间是什么关系呢？从上面的定义中可以看出间隙锁其实堵塞的是往这个间隙中插入元素的行为。为此间隙锁与间隙锁之间没有并发冲突，间隙锁与行锁也没有并发冲突，但是间隙锁会堵塞住往这个间隙中插入元素。
下面我们通过一些例子来加深对上面这个加锁规则的理解，需要注意的是如下的例子还是在可重复读级别下的。

1、唯一索引的等值查询，且行不存在

事务1告诉MySQL需要查询id=5的行且锁住，但是InnoDB只在主键索引树上找到了(0,10)的间隙，于是就在主键索引树的这个间隙上加上(5,10]的next-key lock，而这个是一个等值查询，根据优化二退化成(0,10)的间隙锁，为此事务二被间隙锁锁住了，事务三可以正常运行。

1、唯一索引上的等值查询，且行存在

根据加锁原则，事务1找到了id=10的行，会加10的行锁与(0,10]的next-keylock，但是根据优化一，事务1只会在主键索引树上加id=10的行锁，为此事务2可以正常执行，事务3会堵塞住。

2、唯一索引的范围查询

根据加锁原则与一个bug，事务1会在主键索引树加(0,10)的间隙锁、id=10行锁、(10,20]的next-key lock。为此总的加锁范围是(0,20]，从而把事务2、事务3与事务4都锁住了，事务5可以正常运行。

3、但是如果事务1加上limit 1的限制

由于事务1找到id=10的行后，就停止了搜索。为此总的加锁范围是主键索引树上的(0,10]了，事务4可以执行成功。为此如果知道SQL语句确切返回的行数，可以添加limit限制，减少锁的范围。

4、覆盖索引的范围查询

根据加锁原则，事务1会在n索引树加(0,10)的间隙锁、n=10行锁、(10,20]的next-key lock，为此总的加锁范围是(0,20]，而事务2修改的是主键索引上的id=10的行上m的值，为此可以正常运行。而事务3，修改的是主键索引上的id=10的行上m与n的值，为此要在n索引树上，删除n=10的行且添加n=11与id=10的行，为此会被堵塞。但是事务1如果把“LOCK IN SHARE MODE”改成“FOR UPDATE”或者把“SELECT id”改成”SELECT *”，那么事务2也会把锁住，因为他们会把主键索引树上的id=10的行加锁行锁。这个例子说明了锁是加在索引树上的，且访问到的行才会加锁。

总结：如上的例子中，如果是在读提交上，那么加锁的范围需要去掉间隙锁，同时语句执行完后，只加锁了匹配到的行的行锁。读提交下的加锁范围比可重复读小很多，这也是为什么现在读提交用的越来越广泛了。但是在某些场景下还是要用到可重复读，比如金融领域的对账系统。

INNODB死锁分析

根据加锁原则，事务1与事务2都会在主键索引树上加间隙锁(0,10)。那么事务1与事务2都被对方的间隙锁堵住。那么我们如何查看死锁信息呢？可以通过如下的语句查看：

SHOW ENGINE Innodb STATUS;

输出的文本中有如下的内容

 LATEST DETECTED DEADLOCK
 ------------------------
 2020-09-28 20:43:30 7fc4459b7700
 *** (1) TRANSACTION:
 TRANSACTION 6872141811, ACTIVE 7 sec inserting
 mysql tables in use 1, locked 1
 LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s)
 MySQL thread id 36767219, OS thread handle 0x7fc42d7b6700, query id 400555196 172.18.102.216 root update
 INSERT INTO t(`id`,`n`,`m`) VALUES(6,6,6)
 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 9795 page no 3 n bits 80 index `PRIMARY` of table `institution_rms`.`t` trx table locks 2 total table locks 3  trx id 6872141811 lock_mode X locks gap before rec insert intention waiting lock hold time 7 wait time before grant 0 
 *** (2) TRANSACTION:
 TRANSACTION 6872141739, ACTIVE 8 sec inserting
 mysql tables in use 1, locked 1
 3 lock struct(s), heap size 1184, 2 row lock(s)
 MySQL thread id 36766484, OS thread handle 0x7fc4459b7700, query id 400556851 172.18.102.216 root update
 INSERT INTO t(`id`,`n`,`m`) VALUES(5,5,5)
 *** (2) HOLDS THE LOCK(S):
 RECORD LOCKS space id 9795 page no 3 n bits 80 index `PRIMARY` of table `institution_rms`.`t` trx table locks 1 total table locks 3  trx id 6872141739 lock_mode X locks gap before rec lock hold time 8 wait time before grant 0 
 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 9795 page no 3 n bits 80 index `PRIMARY` of table `institution_rms`.`t` trx table locks 1 total table locks 3  trx id 6872141739 lock_mode X locks gap before rec insert intention waiting lock hold time 0 wait time before grant 0 
 *** WE ROLL BACK TRANSACTION (2)

如下的日志说明事务1在等待间隙锁

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 9795 page no 3 n bits 80 index `PRIMARY` of table `institution_rms`.`t` trx table locks 2 total table locks 3  trx id 6872141811 lock_mode X locks gap before rec insert intention waiting lock hold time 7 wait time before grant 0 

如下的日志说明事务2在持有间隙锁

*** (2) HOLDS THE LOCK(S):
 RECORD LOCKS space id 9795 page no 3 n bits 80 index `PRIMARY` of table `institution_rms`.`t` trx table locks 1 total table locks 3  trx id 6872141739 lock_mode X locks gap before rec lock hold time 8 wait time before grant 0 

如下的日式说明事务2在等待间隙锁

 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 9795 page no 3 n bits 80 index `PRIMARY` of table `institution_rms`.`t` trx table locks 1 total table locks 3  trx id 6872141739 lock_mode X locks gap before rec insert intention waiting lock hold time 0 wait time before grant 0 

如下的日志说明MySQL回滚了事务2

 *** WE ROLL BACK TRANSACTION (2)