30 | 加锁的demo探析

原则：

• 原则 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);

demo1 不等号条件里的等值查询

begin;
select * from t where id>9 and id<12 order by id desc for update;

类似： 和上述一样，没有因为索引是普通索引还是唯一索引的问题。

begin;
SELECT * from t where c>9 and c<12  for update;
ROLLBACK	;

加锁过程：
因为有desc 所以加锁过程先排序再开始，注意加锁的过程是一段一段的。

疑问：
15这一行没有被锁住。加锁单位是 next-key lock，都是前开后闭区间，但是这里用到了优化 2，即索引上的等值查询，向右遍历的时候 id=15 不满足条件，所以 next-key lock 退化为了间隙锁 (10, 15)。

详细过程：

1. 首先这个查询语句的语义是 order by id desc，要拿到满足条件的所有行，优化器必须先找到“第一个 id<12 的值”。
2. 这个过程是通过索引树的搜索过程得到的，在引擎内部，其实是要找到 id=12 的这个值，只是最终没找到，但找到了 (10,15) 这个间隙。
3. 然后向左遍历，在遍历过程中，就不是等值查询了，会扫描到 id=5 这一行，所以会加一个 next-key lock (0,5]。

疑问：为啥树的的搜索，要先找id=12的值，而向左找，就找id=5的值。

因为id=15就是第一个不符合的值了，不需要再向右扫描了。

所以：

BEGIN;
SELECT * FROM t WHERE ID = 10 for UPDATE; -- block
SELECT * FROM t WHERE ID = 15 for UPDATE; -- success
INSERT INTO `cbank`.`t`(`id`, `c`, `d`) VALUES (21, 21, 21); -- success
INSERT INTO `cbank`.`t`(`id`, `c`, `d`) VALUES (16, 16, 16); -- success
INSERT INTO `cbank`.`t`(`id`, `c`, `d`) VALUES (4, 4, 4); -- block
INSERT INTO `cbank`.`t`(`id`, `c`, `d`) VALUES (9, 9, 9); -- block
ROLLBACK	;

demo2 等值查询的过程

begin;
select id from t where c in(5,20,10) lock in share mode;

1. 在查找 c=5 的时候，先锁住了 (0,5]。但是因为 c 不是唯一索引，为了确认还有没有别的记录 c=5，就要向右遍历，找到 c=10 才确认没有了，这个过程满足优化 2，所以加了间隙锁 (5,10)。
2. 同样的逻辑，执行c=10的时候，加锁范围是(5,10] 和 (10,15)；执行 c=20 这个逻辑的时候，加锁的范围是 (15,20] 和 (20,25)。
3. 这条语句在索引 c 上加的三个记录锁的顺序是：先加 c=5 的记录锁，再加 c=10 的记录锁，最后加 c=20 的记录锁。

注意：c=15是不加锁的。

demo3 锁等待

begin;
SELECT * from t where id>10 and id<=15  for update;

过程：

1. id=10的时候，不符合，然后所以最左匹配，锁的范围是
   （10,15）
2. id=15的时候，符合，加上行锁
3. 向右匹配到第一个不符合的，即id=20，锁 （15,20），（20,25）

总的锁范围是：（5,25），其中id=10不锁。

所以：

BEGIN;
SELECT * FROM t WHERE ID = 10 for UPDATE; -- success
SELECT * FROM t WHERE ID = 15 for UPDATE; -- block
INSERT INTO `cbank`.`t`(`id`, `c`, `d`) VALUES (21, 21, 21); -- success
INSERT INTO `cbank`.`t`(`id`, `c`, `d`) VALUES (16, 16, 16); -- block
INSERT INTO `cbank`.`t`(`id`, `c`, `d`) VALUES (4, 4, 4); -- success
INSERT INTO `cbank`.`t`(`id`, `c`, `d`) VALUES (9, 9, 9); -- success
INSERT INTO `cbank`.`t`(`id`, `c`, `d`) VALUES (11, 11, 11); -- block
ROLLBACK	;

但是换一种情况：

begin;
SELECT * from t where id>=10 and id<=15  for update;

向左扫描到 c=10 的时候，10是符合的，但是不会锁（5,10）,最右匹配，c=15符合，

但是

BEGIN;
select * from t where c>=10 and c<=15 order by c desc lock in share mode;

会把 （5,10 ] 锁起来。还会把（0,5 ] 锁起来。最右匹配，c=10,符合，c=5，不符合，加间隙锁（0,5], (5,10]

BEGIN;
select * from t where c>10 and c<=15 order by c desc lock in share mode;

会把（5,10]锁起来。

也就是说，所谓“间隙”，其实根本就是由“这个间隙右边的那个记录”定义的。会向右匹配到第一个，不符合的值。

接着以下查询的分析，

begin;
SELECT * from t where id>10 and id<=15  for update;

假如有另外一个事务执行以下：

BEGIN;
DELETE FROM t WHERE id =10; -- success
INSERT into t VALUES(10,10,10) -- block

第一个success没问题，因为id=10没有加行锁，上面分析了。
第二个为什么block，是因为id=10被删除之后，由于 delete 操作把 id=10 这一行删掉了，原来的两个间隙 (5,10)、(10,15）变成了一个 (5,15)。

以上，这个不等值查询，有点难理解。。。而且id和c也有点不一样。

简单点：
1.

begin;
  SELECT * from t where c>=10 and c< 15  for update;

锁（5,15）
2.

begin;
  SELECT * from t where c>=10 and c< =15  for update;

锁（5,20）

以上两个 命中 优化 2：索引上的等值查询，向右遍历（和order by 有关）时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
3.

begin;
SELECT * from t where id>=10 and id<=15  for update;

锁 [10,20] 主键是最右匹配的？ ，不知道为什么（5,10）不锁
原因：命中优化1 索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
4.

begin;
SELECT * from t where id>=10 and id<15  for update;

锁 [10,15]
命中优化1优化2 ，索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。还有命中bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止，即15

慢慢理解吧·~~~