MySQL深入18-改一行语句锁问题
# MySQL深入18-改一行语句锁问题
相关文章：MySQL深入-幻读 (opens new window)
加锁规则：两个“原则”，两个“优化”和一个“bug”。
原则1：加锁的基本单位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);
# 案例一：等值查询间隙锁
现在表t中是没有id=7的记录，下面我们来分析一下：
session A会加锁，加锁的单位是next-key lock，所以加锁范围为（5，10】。
id = 7 这是一个等值查询，而 id = 10是第一个不满足条件的数据，所以next-key lock会退化为间隙锁，因此最终加锁的范围是（5，10）。
现在加锁范围确定了，下面来分析session B和session C的情况：
对于session B，由于插入的数据id = 8，在加锁范围内，所以被锁住。
对于session C，修改的数据id = 10， 不在加锁范围内，所以可以修改。
# 案例二：非唯一索引等值锁
首先还是来分析加锁情况：
- 根据原则1，加锁范围为(0,5]。
- 根据优化2，c是普通索引，因此需要扫描到c = 10这一行数据才结束（c = 10是第一个不满足条件），访问到的都需要加锁，因此要给（5，10】加next-key lock。
- 又根据优化2，next-key lock会退化为间隙锁，所以最终加锁范围为（5，10）。
对于访问到的对象才会加锁：
session A是覆盖索引，所以不会涉及到回表，所以这行加锁并不会给主键索引上加锁。
加锁，是加在索引上的。 列上，有索引，就加在索引上； 列上，没有索引，就加在主键上；
- session B更新操作能够执行
- session C插入一个 (7,7,7) 的记录，就会被 session A 的间隙锁 (5,10) 锁住。
注意点
lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。 执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。
# 案例三：主键索引范围锁
- session A找到第一个id=10的行，next-key lock(5,10]，由于是唯一索引就需要退化成行锁，相当于只加锁id = 10这一行行锁。
- 范围查找就往后继续找，找到 id=15 这一行停下来，因此需要加 next-key lock(10,15]。
- 所以最终加锁范围就是行锁 id=10 和 next-key lock(10,15]，所以session B和session C结果就比较容易理解了。
首次 session A 定位查找 id=10 的行的时候，是当做等值查询来判断的，而向右扫描到 id=15 的时候，用的是范围查询判断。
# 案例四：非唯一索引范围锁
这里和案例三的区别在于查询字段由唯一索引变成了非唯一索引。
- 定位到c=10的数据后，给索引c加了 (5,10]这个 next-key lock
- 由于索引c是为非唯一索引，且这里并非是等值查询，所以不存在优化情况，进行范围查询的时候查到c=15这条数据结束，同时进行加锁，因此最终加锁范围为(5,10] 和 (10,15]
普通索引的等值查询不满足条件才会退化成间隙锁，范围查询不会退化
# 案例五：唯一索引范围锁
session A是一个范围查询，查询到id = 15这个语句，索引id上会加（10，15]这个next-key lock，但是范围查询需要查找到第一个不满足条件的行位置，也就是需要查找到id=20这一行数据，因此索引 id 上的 (15,20]这个 next-key lock 也会被锁上。所以最终锁的范围是（10，15]和 (15，20]。
# 案例六：非唯一索引上存在“等值”
现在往表里插入一行数据：
insert into t values(30,10,30);
新插入的这一行 c=10，也就是说现在表里有两个 c=10 的行。由于非唯一索引上包含主键的值，所以是不可能存在“相同”的两行的。
虽然有两个 c=10，但是它们的主键值 id 是不同的（分别是 10 和 30），因此这两个 c=10 的记录之间，也是有间隙的。
- session A在找到c=10这一行数据的时候，根据原则1，这里加的是(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上的加锁范围就是下图中蓝色区域覆盖的部分：
# 案例七：limit语句加锁
现在对案例六进行简单修改：
删除语句中加了limit 2，虽然这和案例六中删除效果是一样的，但是加锁的效果不一样。
这是因为，本案例里的 delete 语句明确加了 limit 2 的限制，因此在遍历到 (c=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了。
因此，索引 c 上的加锁范围就变成了从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间，如下图所示：
启示：
在删除数据的时候尽量加 limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。
# 案例八：死锁例子
- 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 回滚。
这里需要对session B加锁的情况进行说明，实际上分成了两步：
- 先是加(5,10) 的间隙锁，加锁成功。
- 然后加c=10的行锁，这时候才被锁住。