MySQL中的行锁

MySQL中的行锁

我们经常会谈论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;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

记录锁

Record Locks 记录锁应该才算真正意义上的“行锁”，因为每一条记录就对应一行。

间隙锁

间隙锁是对记录之间的锁，或者是对第一个索录之前或最后一个记录之后的间隙的锁。例如：

SELECT * FROM t WHERE c BETWEEN 10 and 15 FOR UPDATE;

这将阻止其他事务将13的值插入到t.c 列中，无论列中是否已经存在相同值，因为(5,10)和(10,15)该范围内所有现有值之间的间隙被锁定。

间隙锁存在的原因就是解决幻读。不过，要加间隙锁得使用加锁读（SELECT… FOR SHARE、SELECT… FOR UPDATE），如果只是普通的读，并不会加锁（MVCC机制）。这就将解决幻读抛给了实际使用者，如果操作不当还是会产生幻读，即：开启事务后先用普通读，此时另一个session还是可以插入数据，然后采用加锁读，得到的结果会和最开始的普通读不一样，从定义上来说这也算发生了幻读。所以，即使是平常司空见惯的select操作，我们也必须知道关于加锁的底层原理，这样才能尽可能避免SQL使用不当带来的问题。

Next-Key 开闭锁

由于没有统一的中文译名，这里我斗胆起个中文名——开闭锁，方便行文表述。它其实就是间隙锁和行锁的合称，每个开闭锁是前开后闭区间，这也是我想取名叫开闭锁的原因。这里的“开区间”是指记录之前的间隙锁的范围，“闭区间”是指该记录上的记录锁。这么说可能不太好理解，举个例子：

select * from t where c = 15 for update;

这个加锁读会加上间隙锁(5,10)，合并记录锁10之后就是开闭锁(5,10]。

加锁规则

知道锁的基本定义之后还不够，实际使用时我们需要知道我们用的等值查询、范围查询究竟会给哪些记录、哪些间隙上锁，这样才不会因为锁多了或者锁少了而后知后觉。

加锁规则源自：21 | 为什么我只改一行的语句，锁这么多？-MySQL 实战 45 讲-极客时间 (geekbang.org)

• 原则1：加锁的基本单位是next-key 锁（开闭锁）。

• 原则2：查找过程中访问到的对象才会加锁。

• 优化1：索引上的等值查询，给唯一索引加锁的时候，开闭锁退化为记录锁。

• 优化2：索引上的等值查询，向右遍历且最后一个值不满足等值条件的时候，开闭锁退化为间隙锁。

优化1 示例

select * from t where id = 10 for update;

由于id=10满足等值条件，且id上有主键索引（唯一索引），所以开闭锁(5,10]退化为记录锁10。

举例如下：

在另一个会话插入不受影响：

优化2 示例

select * from t where id = 7 for update;

由于在表中没有满足id=7的记录，所以开闭锁(5,10]不会退化为记录锁10。与此同时，这也会触发优化2，导致退化为间隙锁(5,10)。

举例如下：

在另一个会话插入被阻塞：

原则2 示例1

SELECT * FROM t WHERE c BETWEEN 10 and 14 FOR UPDATE;

回到我们最开始的间隙锁举例，这里范围查找会一直找到c=15的记录才停下，总共访问过c=10、c=15两条记录，所以最终加锁是(5,10]、(10,15]。

举例如下：

想插入c=9、c=10、c=13都是不允许的：

原则2 示例2

select id from t where c = 5 for share;

如果用到了覆盖索引，由于不需要回表，也就不需要访问主键索引，那么主键索引上就没有任何锁。

不过，此情况仅限for share加锁读，如果是for update加锁读或者更新操作，系统认为会与更新数据相关，顺便给主键索引上满足条件的行上锁。

---

如今互联网上各类文章满天飞，但是大部分要不是寥寥数语，让人过目即忘；要不是过多细枝末节又没有实操，让人不知所云。我将从个人学习和工作经历出发，给大家带来深入浅出的技术解析。我的文章力求简短精悍，尽量结合实战，以便大家在碎片时间即可充分吸收，后续还能学以致用。

欢迎大家关注我的微信公众号，所有文章第一时间更新~