mysql语句执行中的锁情况

注意：内容来自于mysql实战45讲所做的笔记

一、两个原则，两个优化，一个bug

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

二、几种情况分析

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),(30,30,30),(35,35,35),(40,40,40),(45,45,45),(50,50,55),(55,55,55),(60,60,60);

分析前先准备一下数据，以下分析都会根据数据要进行分析，以下的不同会话需要打开不同的会话才能执行成功，事物的执行要记得每次实验结束后都要ROLLBACK以下结束一下事物才不会影响第二次实验

1.等值唯一索引加锁情况

-- sessionA,开启事物，并上锁
BEGIN;
SELECT id FROM t WHERE id = 5 LOCK IN SHARE MODE;
-- sessionB
INSERT INTO t VALUES(7,7,7)
-- sessionC,开启事物，update是自动上锁
BEGIN;
UPDATE t SET d = 5 WHERE d =5;
-- sessionD
UPDATE t SET c = 10 WHERE c =10;
-- 实验结束时请删除id = 7的数据

结果：
sessionB（Query OK）
sessionC（Blocked）
sessionD（Blocked）

sessionA：加锁范围( 0, 5 ]，由于是唯一索引，根据优化2退化为行锁，加锁范围为5
sessionB：id不等于5，执行成功
sessionC：没有用到索引，进行全表扫描。由于全表扫描实际上是全表扫描主键id,获取主键索引后面的d，然后再进行判断d是否符合（有兴趣可以了解一下innodb的数据存储结构），所以加的是表锁，因为id=5被加了行锁。所以执行阻塞。
sessionD：虽然不在sessionA的加锁范围内，但是，因为sessionB持有了表锁，表锁并没有被释放，因此sessionD也阻塞

2.等值非唯一索引的查找加锁情况

-- sessionA,开启事物，并上锁
BEGIN;
SELECT id FROM t WHERE c = 5 LOCK IN SHARE MODE;
-- sessionB
UPDATE t SET d = 5 WHERE id =5;
-- sessionC
UPDATE t SET d = 5 WHERE c =5;
-- sessionD
INSERT INTO t VALUES(7,7,7)

结果：
sessionB（Query OK）
sessionC（Blocked）
sessionD（Blocked）

sessionA的加锁过程是这样子的：
首先我们要知道的一点是next-key lock加锁是加在索引上的，所以根据原则1和2，sessionA的加锁范围是索引c的( 0, 5 ] ，我们知道非唯一索引是不止有一个数的，所以查找到5的时候并不会停下来，而是会继续对比到下一个数值，因此它的加锁范围其实是( 0, 10 ]。
接下来根据优化2，10不等于5，next-key lock退化为间隙锁，加锁范围为( 0, 10 )
所以结论是sessionA对索引c加的锁的范围是( 0, 10 )
sessionB：索引id没有被加锁，执行成功
sessionC：c=5在这个范围内，执行阻塞
sessionD：c=7在这个范围内，执行阻塞

3.等值非唯一索引的更新加锁情况

-- sessionA,开启事物，并上锁
BEGIN;
SELECT id FROM t WHERE c = 5 FOR UPDATE;
-- 上面的或者写成UPDATE t SET d = 5 WHERE c =5;也一样
-- sessionB
UPDATE t SET d = 5 WHERE id =5;
-- sessionC
UPDATE t SET d = 5 WHERE c =5;
-- sessionD
INSERT INTO t VALUES(7,7,7)

结果：
sessionB（Blocked）
sessionC（Blocked）
sessionD（Blocked）

sessionA的加锁过程同上加锁范围是( 0, 10 )，但是，由于是更新语句，于是它会找到加锁范围内的所有主键：5也给加上next-key lock锁，但是根据优化2我们知道它会退化为行锁，所以只对5加锁
所以结论就是对索引c加的锁的范围是( 0, 10 )， 对主键索引id加锁的范围是：5
sessionB：主键id=5被锁，执行阻塞
sessionC：c=5被锁，执行阻塞
sessionD：c=7被锁，执行阻塞

4.范围唯一索引加锁情况1

-- sessionA,开启会话，并上锁
BEGIN;
SELECT * FROM t WHERE id > 5 AND id < 6 FOR UPDATE;
-- sessionB
INSERT INTO t VALUES(4,4,4)
-- sessionC
UPDATE t SET d = 10 WHERE id =10;
-- sessionD
UPDATE t SET d = 5 WHERE id =5;
-- 实验结束请删除id = 4的数据

结果：
sessionB（Query OK）
sessionC（Blocked）
sessionD（Query OK）

sessionA由于是范围索引，所以加锁范围是( 5, 10 ]
sessionB：不在范围内执行成功
sessionC：在范围内执行失败
sessionD：不在范围内执行成功

5.范围唯一索引加锁情况2

-- sessionA,开启会话，并上锁
BEGIN;
SELECT * FROM t WHERE id >= 5 AND id < 6 FOR UPDATE;
-- sessionB
UPDATE t SET d = 10 WHERE id =10;

结果：
sessionB（Blocked）

sessionA的加锁可以看成等于5和大于5小于6，优化1得出行锁，范围索引( 5, 10 ]
所以结论就是session加锁范围是行锁5和next-key lock锁( 5, 10 ]
sessionB：在范围内执行失败

6.范围唯一索引加锁情况3

-- sessionA,开启会话，并上锁
BEGIN;
SELECT * FROM t WHERE id >= 5 AND id <= 6 FOR UPDATE;
-- sessionB
UPDATE t SET d = 10 WHERE id =10;

结果：
sessionB（Blocked）

sessionA的加锁可以看成等于5、大于5小于6、和等于6，优化1得出行锁5，范围索引( 5, 10 ]
所以结论就是session加锁范围是行锁5和next-key lock锁( 5, 10 ]
sessionB：在范围内执行失败

7.范围唯一索引加锁情况4(两个原则两个优化一个bug中的bug)

-- sessionA,开启会话，并上锁
BEGIN;
SELECT * FROM t WHERE id > 5 AND id <= 10 FOR UPDATE;
-- sessionB
UPDATE t SET d = 15 WHERE id =15;

结果：
sessionB（Blocked）

sessionA的加锁可以看成等于5、大于5小于10、和等于10，优化1得出行锁5，范围索引( 5, 10 ]，但是根据开篇讲到的bug，会继续往下走，因此( 10， 15 ]也加上锁
所以结论就是session加锁范围是行锁5和next-key lock锁( 5, 15 ]
sessionB：在范围内执行失败

8.范围普通索引加锁情况1

-- sessionA,开启会话，并上锁
BEGIN;
SELECT * FROM t WHERE c > 5 AND c < 6 FOR UPDATE;
-- sessionB
UPDATE t SET d = 5 WHERE c = 5;
-- sessionD
UPDATE t SET d = 10 WHERE c = 10;

结果：
sessionB（Query OK）
sessionC（Blocked）

sessionA由于是范围索引，所以加锁范围是( 5, 10 ]
sessionB：不在范围内执行成功
sessionC：在范围内执行失败

9.范围普通索引加锁情况2

-- sessionA,开启会话，并上锁
BEGIN;
SELECT * FROM t WHERE c >= 5 AND c < 6 FOR UPDATE;
-- sessionB
UPDATE t SET d = 5 WHERE c = 5;
-- sessionD
UPDATE t SET d = 10 WHERE c = 10;

结果：
sessionB（Blocked）
sessionC（Blocked）

sessionA的加锁可以看成等于5和大于5小于6，所以是( 0, 5 ]( 5, 10 ]
所以结果是所以c( 0, 10 ]上锁，中间涉及到的主键索引：5，10也上锁
sessionB：在范围内执行失败
sessionC：在范围内执行失败

9.limit 语句加锁

-- sessionA,开启会话，并上锁。
BEGIN;
SELECT * FROM t WHERE c = 5 LIMIT 1 FOR UPDATE;
-- sessionB
INSERT INTO t VALUES(7,7,7)
-- 结束实验请删除id = 7

结果：
sessionB（Query OK）

sessionA加锁情况加上limit就比较特殊了，首先加锁范围是( 0, 5 ]然后再根据limit值判断是否要继续往后移动。上面的sql为1，那么就会直接停下来。所以最终的加锁范围是( 0, 5 ]
但是如果limit是2，而c = 5只有一条数据的话，那么加锁范围是( 0, 10 )；如果limit是2，而c = 5有两条数据的话，那么加锁范围仍然为是( 0, 5 ]，以此类推。因为满足不了limit的条件就会继续往下查找范围，知道查找到不符合的条件
sessionB：我们的c = 5只有一条数据，加锁范围是( 0, 5 ]，因此执行成功

9.order by语句加锁

-- sessionA,开启事物，并上锁
BEGIN;
SELECT id FROM t WHERE c = 5 ORDER BY c DESC LOCK IN SHARE MODE;
-- sessionB
INSERT INTO t VALUES(7,7,7)

结果：
sessionB（Blocked）

sessionA由于order by，遍历的时候是从后开始遍历，因此加锁的范围是( 10, 5 ]和退化后的间隙锁( 5, 0 )，所以加锁范围是( 10, 0 )
sessionB：在锁范围内，执行阻塞