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

发表评论

nine + one =