Mysql锁之——行锁_mysql行锁

这篇文章我们来聊聊Mysql的行锁

InnoDB的行锁包括：记录锁、间隙锁（Gap Lock，解决幻读问题）和组合锁。

两阶段锁协议

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

行锁

顾名思义，就是给一行数据记录上锁。

间隙锁

注意：以下所有例子都是默认在RR隔离级别下：

// 创建表 t 
mysql> 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;

// 插入记录
mysql> INSERT INTO t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),
		(20,20,20),(25,25,25);
1
2
3
4
5
6
7
8
9
10
11
12

假设，只给d=5这条记录上锁，执行以下语句：

mysql> select * from t where d=5 for update;
1

什么是幻读？如上图：

• 事务A 执行select * from t where d=5 for update，查询d=5的所有记录
• 事务B将id=0的d字段修改5，事务A查询到两条结果
• 事务C插入一条记录，也满足事务A的查询条件，事务A查询到三条结果。

幻读

• 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，“幻读”只有在当前读下才会出现；
• 幻读专指新插入的行。

解决：
幻读产生的原因是，行锁只能锁住行，而插入语句是要插入到现有数据记录的“间隙”。因此，为了解决幻读，InnoDB引入了间隙锁。

间隙就是两个值之间的间隙，如开头创建的表t，插入了6条记录，则产生7个间隙，都是开区间，如下图：

分析：select * from t where d=5 for update;
实际上，因为d上没有索引，所以会走全表扫描，在一行一行的扫描过程中，不仅给每行加上锁，而且给两个值之间的间隙也加了间隙锁，这样就可以确保无法插入新的数据记录。

组合锁（Next-key lock）

就是间隙锁+行锁，前开后闭区间。如：（5，10]， (10, 15]，(15, 20]，(20, 25]。

加锁规则：

1. 加锁的单位是next-key-lock；
2. 只有访问到的对象才会加锁；
3. 对于唯一索引的等值查询来说，next-key-lock会退化为行锁；
4. 索引的等值查询来说，向右遍历时，右边界不满足等值条件时，next-key-lock会退化为间隙锁；
5. 对于唯一索引的范围查询来说，会访问到第一个不满足条件的记录为止。

例一：主键索引等值锁

分析：

• 根据规则1，Session A会在主键id上 加 （5,10] 的 next-key lock；
• 因为Session A的next-key lock，Session B的插入会进入锁等待，直至超时；
• 根据规则4，向右遍历的右边界10，不满足等值条件id=7，所以next-key lock退化为间隙锁（5,10），所以，Session C的更新可以成功。

例二：覆盖索引等值锁

分析：这里 session A 要给索引 c 上 c=5 的这一行加上读锁。

• 根据规则1，会在索引c上加（0，5] 的 next-key lock；
• 因为c是普通索引，到c=5这条记录不会停下来，会继续往后扫描，直至不满足等值条件，也就是 c=10 为止。根据规则2，还会在索引c上加（5,10]的next-key lock；
• 根据规则4，继续向后遍历，右边界10不满足等值条件，该next-key lock退化为间隙锁（5,10）；
• 根据规则2，只有在访问到的对象才会加锁。注意此时的查询用到了覆盖索引，并不需要访问主键索引，所以主键索引上没有任何锁。所以Session B的update语句可以执行成功。但是Session C的插入语句会被 Session A 的间隙锁锁住。

注意，这个例子中是以 lock in share mode 方式加锁，这种加锁方式默认不会修改数据，只会锁覆盖索引。如果是以 for update 方式，系统会认为你接下来会修改数据，顺便就主键索引上满足条件的行加上行锁。

例三：主键索引范围锁
先来看看，以下两条语句有什么不同吗？

mysql> select * from t where id=10 for update;
mysql> select * from t where id>=10 and id<11 for update;
1
2

对于整型字段来说，上述两条语句在逻辑上执行结果是一致的，但是加锁的范围却不同。下面，我们来看看第二条语句的加锁效果：

分析：

• 根据规则1，会在主键索引id上加（5,10] 的 next-key lock；
• 根据规则3，唯一索引的next-key lock会退化为行锁，也就是只加了id=10的行锁；
• 因为是范围查询，继续向后扫描，找到id=15停下来，根据规则2，会在id上加（10,15] 的next-key lock;

所以，Session A这时候的锁范围就在主键索引id=10的行锁，以及（10,15]的next-key lock；这样SessionB 和 C的执行结果你就理解了。

例四：非唯一索引范围锁

与例子三不同的是，用的是非唯一索引c。

select * from t where c>=10 and c<11 for update;
1

分析：

• 根据规则1，会在索引c上加（5,10] 的next-key lock；
• 因为c不是唯一索引，所以规则3不适用。
• 因为范围查询，继续向后扫描，找到c=15停下来。根据规则2，会在c上加（10,15] 的next-key lock;

所以，Session A就加了（5,10] 和 （10,15] 的next-key lock; 那么此时主键索引上有那些锁呢？我们继续来验证：

可以看到，主键id=10的记录被加了锁。

例五：唯一索引范围锁Bug

mysql> select * from t where id >10 and id <=15 for update;
1

分析：

• 根据规则1，会在id索引上加（10,15] 的 next-key lock；因为id是唯一键，所以到id=15这就结束了。但是，InnoDB 会继续往后扫描，直到第一个不满足条件的记录为止，也就是id=20。所以，根据规则2，就需要给id索引加上（15,20] 的next-key lock。

所以，Session B 对于id=20行记录的update会被阻塞，同样，Session C的插入语句也会被阻塞。

例六：非唯一索引上的“等值”的例子

先给表t插入一行记录

mysql> insert into t values(30, 10, 30);
1

新插入的行数据c=10，此时表中就有两条c=10 的记录，分别是（10,10,10）和 （30,10,30）。但是，这两条记录的主键值不同，也就是说，这两个c=10的记录中间也有间隙。

接下来我们来看例六：

分析：

• 根据规则1，会在索引c上加（5,10] 的 next-key lock;
• 因为是非唯一索引，会继续往后扫描，找到下一个c=10&&id=30的记录，继续往后扫描，直到不满足等值条件id=15时停止，根据规则2，加上（10,15] 的 next-key lock;
• 因为是等值查询，根据规则4，向右扫描，右边界15不满足等值c=10条件，next-key lock退化为间隙锁（10,15）；

所以，Session B被阻塞，Session C执行成功。加锁范围如下：

例七：limit语句

分析：
虽然，你知道只有两条满足条件的记录，两个语句的执行逻辑是一致的，但是与例六不同的是，加了limit 2限定，在找到两条c=10的记录后，就不再往后扫描了，所以加锁范围也有了不同，如下：

例八：死锁的例子

分析：

• Session A给索引c，加了（5,10] 的next-key lock，和 （10,15）的间隙锁；
• Session B也要加（5,10] 的 next-key lock，阻塞等待；
• Session A的插入也被阻塞；

很好的说明了，next-key lock等价于 “行锁 + 间隙锁”。实际上，Session B的加锁是分为两步的，先加（5,10）的间隙锁，间隙锁和间隙锁不冲突，添加间隙锁成功，再去申请行锁的时候，阻塞了。从而导致了Session A 往这个间隙的插入操作阻塞了，形成死锁，InnoDB 让事务B回滚。

补充知识：

可重复读隔离级别下，普通的查询（select … from t where …;）是快照读，不会加锁，也就是利用MVCC读取数据。

当前读：select…for update 是当前读，当前读就是能读到所有已提交的记录的最新值。

加锁介绍

select … where…lock in share mode，这种方式会加S共享锁
select…where…for update，这种方式会加X排它锁，

如果是update、delete都会加X排他锁，具体这个锁是加在聚簇索引还是二级索引，以及对应索引的哪些记录上，由过滤条件：1.是哪种索引（主键索引、唯一索引、非唯一索引）；2.事务的隔离级别来决定。

X锁 加锁过程

1. 主键索引：在符合条件的记录上加X锁
2. 非主键唯一索引：在符合条件的唯一辅助索引的索引记录上加X锁，并在对应的主键索引的记录上加X锁，若是查询用到了覆盖索引，并且使用的lock in share mode则不会给主键索引对应的记录加锁。
3. 非唯一索引：
   （RC）符合条件的索引记录上都加X锁，对应的主键索引上也加X锁
   （RR）符合条件的索引记录上都加X锁，对应的聚簇索引上也加X锁，此外，二级索引符合条件的前后的间隙都会加上间隙锁，防止幻读。
4. 无索引：
   （RC）主键索引的所有记录都会加X锁
   （RR）主键索引的所有记录都会加X锁，而且所有的间隙都会加间隙锁。

笔记参考于极客时间《MySQL实战45讲》