Mysql的间隙锁

作者

学习Mysql, 总会有一座绕不过去的大山, 那就是锁。理论上，锁的花样再多，也超不出操作系统课上讲的那些范畴，但是Mysql锁让我翻车了。

在Mysql中锁的粒度可分为：表级锁，行级锁，间隙锁 三种。表级锁和行级锁都没什么太难理解的地方。只有间隙锁我无法准确理解其设计意图，而且我试验下来的现象让我觉得很诡异。

那么为什么会有间隙锁这种东西呢，按大部分能查到的资料表示，间隙锁的引入是为了解决在RR隔离级别的幻读问题。

下面来看一个实例，首先创建一个Table:

Create Table: CREATE TABLE `foo` (
  `uid` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`uid`),
  KEY `age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into foo values(1,1),(4,4),(7,7),(9,9);

然后，开两个mysql客户端(M1,M2)，其执行顺序如下：

M1: begin;
M1: select * from foo were uid > 1 and uid < 5 for update;
M2: begin;insert into foo values(2,2);commit;
M1: select * from foo were uid > 1 and uid < 5 for update;
M1: commit;

如果在M1第一次执行select语句时只加行锁，那么锁住的就只有uid=4这一行。
在M1第二次执行select语句时,由于M2插入了一条(2,2), 因此会多查询出一条（2,2)的记录。
这就会产生幻读。

mysql的解决方案是：使用间隙锁，将uid在间隙区间（1,4),(4,7）的全部加锁，这样当M2在insert行数据（2，2）甚至（6，6）会被锁阻塞以防止M1出现幻读。

如果事情到这里完美结束，那我也不会翻车了，再看另外三条sql语句：

M1: begin;
M1: select * from foo were age = 4 for update;
M2: begin;insert into foo values(6,6);commit;
M1: select * from foo were age = 4 for update;
M1: commit;

手动执行一下就会发现M2会被锁阻塞住，这是因为他对age加了间隙锁（锁是加在索引上的）。

由于锁是加在索引上的， 按照我第一反应，直接对age=4这一条索引加锁就解决问题了，为什么要加间隙锁？

我查了很久，才找到一个很少有人提到但很重要的点二级索引中存储的主键，会参于二级索引排序，比如age索引进行排序时，实际用的是(age,uid)来进行排序。而之所以会使用uid参与排序我想大部分原因应该是B+树内不允许存储相同的值。使用age,uid进行拼接之后可以保证所有的二级索引，在B+树中的值一定是惟一的。

换句话说，我们无法单纯的锁住age=4这一条件，因为可能会存在（age,uid）= (4,1)/(4,2)/(4,5)等任意索引。

二级索引在拼接时，由于age在前uid在后，因此age的值在一定程度上就代表了整个索引值。这也是为什么间隙锁可以锁住age=4这一条件。

为了验证上述说法正确性，来看如下sql:

M1: begin;
M1: select * from foo were age = 4 for update;
M2: begin;update foo set age = 2 where uid = 1;commit;
M1: select * from foo were age = 4 for update;
M1: commit;

先简单分析一下 ：

  1. age是非惟一二级索引
  2. 二级索引在内部实现是由age,uid拼接之后才参与排序的
  3. 间隙锁住了(age,uid) = (1,1) ~ (4,4)的开区间
  4. M2执行的语句是想插入一个二级索引值(2,1)

根据间隙锁原理，我们可以推段出M2会被间隙锁给阻塞住，而事实也正是这样。

ps. 二级索引中存储的主键会参于二级索引排序，这一点我认为非常重要。不知道为什么很多参考书都有意无意略过去了。

发表评论

+ 48 = fifty eight