本文为《MySQL归纳学习》专栏的第十九篇文章，同时也是关于《MySQL锁机制》知识点的第四篇文章。
相关文章：
锁的三重奏：MySQL全局锁、表级锁与行锁详解
从幻读看MySQL：读取方式解读与解决方案
透过实例看行锁：深入探讨加锁原则与优化（上）
在MySQL数据库运维和开发过程中，理解加锁原则并灵活运用是非常重要的。本文作为系列文章的下篇，将更深入地探讨加锁的实践应用。我们将结合实际案例，重点解析无索引修改间隙锁、非唯一索引上存在"等值"的情况，还有令人头疼的死锁问题。此外，本文也将讲述如何查看锁等待，以及根据update语句学习加锁顺序。最后，我们将从next-key lock的角度来分析加锁规则。让我们一起深入探究，理解MySQL的行锁和间隙锁的运作机制，掌握如何在复杂场景中灵活使用这些工具，优化我们的数据库运维和开发工作。
无索引修改间隙锁
情景一：
如下表结构：
CREATE TABLE t5 (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t5 VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
事务A执行如下语句：
begin;
update t5 set b=5 where b=3;
事务B执行：
update t5 set b=4 where b=2;
可以发现，事务B会被阻塞，最终报错 Lock wait timeout exceeded; try restarting transaction。
情景二
同样还是该表结构，我们改变一下数据插入的顺序，如下所示：
CREATE TABLE t6 (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t6 VALUES (1,2),(3,2),(5,2),(2,3),(4,3);
重复上述操作，发现事务B正常执行。
那么为什么呢？
首先我们需要知道，表上没有索引，所以对于记录锁会用到隐藏主键（row_id）。
情景一用下图进行分析：
事务A需要加的锁为 row_id=1和3 的记录锁，以及(1,3)间隙锁，所以事务B执行时，因为获取不到 row_id=2 的记录锁，最终阻塞。如果将事务B改为：update t5 set a=11 where a=2;照样阻塞。
情景二按照上述方法进行分析，可以发现 row_id 不存在交叉部分，所以不会阻塞。
非唯一索引上存在"等值"
插入记录
mysql> insert into t values(30,10,30);
新插入的这一行 c=10，也就是说现在表里有两个 c=10 的行。那么，这时候索引 c 上的间隙是什么状态了呢？你要知道，由于非唯一索引上包含主键的值，所以是不可能存在“相同”的两行的。
这次我们用 delete 语句来验证。注意，delete 语句加锁的逻辑，其实跟 select ... for update 是类似的，也就是文章开始总结的两个“原则”、两个“优化”和一个“bug”。
-- session A
delete from t where c=10;
-- session B、C、D三者
-- session B. block
update t set d=11 where c=10;
-- session C. block
update t set d=12 where id=10;
-- session D. block
update t set d=31 where id=30;
这时，session A 在遍历的时候，先访问第一个 c=10 的记录。同样地，根据原则 1，这里加的是 (c=5,id=5) 到 (c=10,id=10) 这个 next-key lock(5,10]。
然后，session A 向右查找，直到碰到 (c=15,id=15) 这一行，循环才结束。根据优化 2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成 (c=10,id=10) 到 (c=15,id=15) 的间隙锁(10,15)。
最终 SessionA 的加锁范围为 next-key lock(5,10] 和间隙锁(10,15)，还有两个行锁 id=10 和 id=30，因为 delete 语句会根据主键索引，将扫描到的行记录加上行锁。（索引c=10和索引id=10、30的锁都是行锁）
也就是说，这个 delete 语句在索引 c 上的加锁范围，就是下图中蓝色区域覆盖的部分。
这个蓝色区域左右两边都是虚线，表示开区间，即 (c=5,id=5) 和 (c=15,id=15) 这两行上都没有锁。（ 如果 session b 插入(4,5,50)，不会被锁，如果插入（6,5,50） 会被锁住，因为 6,5,50 根据主键来排列的话 是在 5,5,5 后面的 ）
limit 语句加锁
对比上述案例，看如下内容：
这个例子里，session A 的 delete 语句加了 limit 2。你知道表 t 里 c=10 的记录其实只有两条，因此加不加 limit 2，删除的效果都是一样的，但是加锁的效果却不同。
在遍历到 (c=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了。
因此，索引 c 上的加锁范围就变成了从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间，如下图所示：
在删除数据的时候尽量加 limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。 同理，在我们执行查询语句时，如果只是查询单条记录，尽量也加上 limit。
一个死锁的例子
-- session A
begin;
select id from t where c=10 lock in share mode;
insert into t values(8,8,8);
-- success
insert into t values(12,12,12);
-- session B
update t set d=d+1 where c=10;
简单来说就是：sessionA 等 sessionB 的间隙锁, sessionB 等 sessionA 的行锁，最终死锁。
session B 的“加 next-key lock(5,10] ”操作，实际上分成了两步，先是加 (5,10) 的间隙锁，加锁成功；然后加 c=10 的行锁，这时候才被锁住的。
我们在分析加锁规则的时候可以用 next-key lock 来分析。但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的。
怎么看死锁？
在出现死锁后，执行 show engine innodb status 命令得到的部分输出。这个命令会输出很多信息，有一节 LATEST DETECTED DEADLOCK，就是记录的最后一次死锁信息。
我们来看看这图中的几个关键信息。
1、这个结果分成三部分：
(1) TRANSACTION 2644529，是第一个事务的信息,对应 session B
(2) TRANSACTION 2644530，是第二个事务的信息，对应 session A
WE ROLL BACK TRANSACTION (1)，是最终的处理结果，表示回滚了第一个事务。
2、第一个事务的信息中：
WAITING FOR THIS LOCK TO BE GRANTED，表示的是这个事务在等待的锁信息；
lock_mode X waiting,表示自己加了一个next-key 锁，当前状态是等待中
0: len 4; hex 8000000a; asc ;;是第一个字段，也就是 c。值是十六进制 a，也就是 10；
1: len 4; hex 8000000a; asc ;;是第二个字段，也就是主键 id，值也是 10；
3、第二个事务显示的信息要多一些：
“ HOLDS THE LOCK(S)”用来显示这个事务持有哪些锁；
lock mode S 表示自己持有读锁
lock_mode X locks gap before rec insert intention waiting 表示准备插入意向锁，尝试获取间隙锁
从上面这些信息中，我们就知道：
- Session A持有 next-key lock(5,10] 和间隙锁 (10,15)
- Session B 持有间隙锁(5,10)，在等 session A中 c=10 的记录锁，
- Session A 的 insert 语句被 session B 的间隙锁锁住，最终进入死锁局面。
这里，我们可以得到两个结论：
- 由于锁是一个个加的，要避免死锁，对同一组资源，要按照尽量相同的顺序访问；
- 在发生死锁的时刻，for update 这条语句占有的资源更多，回滚成本更大，所以 InnoDB 选择了回滚成本更小的 lock in share mode 语句，来回滚。
怎么看锁等待？
begin;
select * from t where id>10 and id5 lock in share mode;
update t set c=3 where c=5;
-- blocked
update t set c=5 where c=3;
-- success
select * from t where c=0 for update;
session A 的加锁范围是索引 c 上的 (5,10]、(10,15]、(15,20]、(20,25]和 (25,supremum]，简化为(5,supremum]。
之后 session B 的第一个 update 语句，要把 c=5 改成 c=1，你可以理解为两步：
- 插入 (c=1, id=5) 这个记录，间隙锁(1,5)不会阻塞该操作
- 删除 (c=5, id=5) 这个记录，session A 加锁范围为 (1,supremum]
session A 的加锁范围变成下图这样：
接下来 session B 要执行 update t set c = 5 where c = 1 这个语句了，一样地可以拆成两步：
- 插入 (c=5, id=5) 这个记录，被间隙锁(1,supremum]阻塞
- 删除 (c=1, id=5) 这个记录
第一步试图在已经加了间隙锁的 (1,10) 中插入数据，所以就被堵住了。
小结
可重复读隔离级别遵守两阶段锁协议，所有加锁的资源，都是在事务提交或者回滚的时候才释放的。
在分析加锁规则的时候可以用 next-key lock 来分析。但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的。
如果切换到读提交隔离级别 (read-committed) 的话，就好理解了，过程中去掉间隙锁的部分，也就是只剩下行锁的部分。
在读提交隔离级别下还有一个优化，即：语句执行过程中加上的行锁，在语句执行完成后，就要把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交。
