内容包括共享锁S、排它锁X、意向共享锁IS、意向排它锁IX、行锁、间隙锁Gap Lock、Next-Key锁、插入意向锁。
以后面试官再问你MySQL的锁,不要只会回答悲观锁和乐观锁了,感觉太Low!!!
前言
上周五值班时遇到一个很诡异的线上问题,对表A的M行数据加排它锁后,然后批量插入数据到表B,结果导致A表和B表中其它的数据N加锁和插入数据耗时过长。如果是加的行锁,这个还好理解,但是A数据的加锁和更新,为什么会对B数据有影响呢?
然后我同时提出两个假设:
- 大量插入数据到B表时,可能会将B表的行锁升级为表锁;
- 插入数据时会加插入意向锁,可能是插入意向锁阻塞了其它数据的加锁和更新。
内容回顾
之前学习MySQL,我知道共享锁、排它锁、行锁、间隙锁和Next-key锁,为了偷懒,我就直接贴几张之前写的PPT,首先看一下共享锁、排它锁:
这个很简单,我们经常使用排它锁锁住该行,然后再去更新数据,避免“丢失更新”类的问题:
那行锁、间隙锁和Next-key锁又是什么呢?
一般我们用for update加锁时,其实是加的Gap Lock,但是因为我们经常会将for update加到主键上,所以Gap Lock就降级为行锁。
这个很基础,仅作简单回顾,不懂的同学可以再去看一下书哈。
意向锁
基本概念
InnoDB 支持行锁和表锁,意向锁是是一种不与行级锁冲突的表级锁,这里有2点需要注意:
- 意向锁是表锁;
- 意向锁不会与行级锁冲突。
意向锁分为两种:
- 意向共享锁(intention shared lock, IS):事务有意向对表中的某些行加共享锁(S锁)
-- 事务要获取某些行的 S 锁,必须先获得表的 IS 锁。 SELECT column FROM table ... LOCK IN SHARE MODE;
- 意向排他锁(intention exclusive lock, IX):事务有意向对表中的某些行加排他锁(X锁)
-- 事务要获取某些行的 X 锁,必须先获得表的 IX 锁。 SELECT column FROM table ... FOR UPDATE;
意向锁是由数据引擎自己维护,用户无法手动操作意向锁,在为数据行加共享 / 排他锁之前,InooDB 会先获取该数据行所在在数据表的对应意向锁。
意向锁的兼容互斥性
意向锁之间的兼容互斥性:
意向锁与普通的排他 / 共享锁的兼容互斥性:
注意:这里的排他 / 共享锁指的都是表锁!!!意向锁不会与行级的共享 / 排他锁互斥!!!
下面我们看一个问题,我们有下面一张表,里面数据如下:
事务 A 获取了某一行的排他锁,并未提交:
事务 B 想要获取 t_rule1 表的表锁:
事务A持有意向排它锁,事务B想要去获取 t_rule1 表的表级排它锁,这两者明显互斥,所以事务B会被阻塞。
如果没有意向锁,会出现什么情况呢?事务B会去扫描 t_rule1 表,看是否每一行数据是否持有行级排它锁,当发现id=5加了行锁,事务B才会阻塞。
这就是为啥要加意向锁,其实主要是为了减少对行锁的扫描!嗯。。。我可以把意向锁理解为一个小红旗,当工厂里有人工作时,就把小红旗升上来,告诉工厂外面的人,有人在里面工作。当工厂里面没有工人工作时,就把小红旗降下来。这样外面的人就只需要通过小红旗判断是否有人在工厂工作,而不需要进入工厂,挨个排查是否有人在工作了。
意向锁不会影响行锁
意向锁只是对表锁而言,和行级别的共享锁 / 排它锁不会发生互斥!!!举个非常简单的例子,我们在A事务操作:
select * from t_rule1 where id = 5 for update;
该行获取2把锁,分别为意向排它锁和行级排它锁,我在B事务操作:
select * from t_rule1 where id = 10 for update;
该行需要获得意向排它锁和行级排它锁,由于事务B的意向排它锁和事务A的意向排它锁兼容,且和事务A的行级排它锁不会发生互斥,所以事务B的意向排它锁可以获取成功。
同理,事务B的行级排它锁也可以和事务A的两把锁兼容,为啥两个行级排它锁可以兼容呢?因为两个事务锁住的不是同一行!
意向锁小结
- 意向锁之间,是互相兼容的;
- 意向锁只和表级锁会冲突:意向锁(共享IS/排它IX)和表锁(共享S/排它X),除了IS和S兼容,其它全部互斥;
- 意向锁不会与行级锁冲突:意向锁(共享IS/排它IX),不会和行级的X/S发送冲突;
- 意向锁的作用:提高并发,减少行锁判断。
插入意向锁
不同事务数据插入
还是回到“前言”中提到的问题,当插入数据A时,会阻塞数据B的插入么,比如事务A中执行,未commit:
然后在事务B中执行:
当两个事务都提交后,两行数据都插入成功,所以事务A的插入,不会影响事务B的插入,除非插入的是同一行,比如事务A插入28,事务B也插入28,这时就会阻塞:
这个只是稍微扩展了一下,大家可以自己去分析,如果这都不知道,证明你前面的知识还没有完全掌握。
基础知识
有同学可能会说,两个事务数据的插入,如果插入的不是同一行,当然不会影响,这个小学生都知道。那我再多问一句,我让你从锁的角度分析,为什么他们不会影响呢?这里就需要提到一个概念“插入意向锁”。
插入意向锁是在插入一条记录行前,由 INSERT 操作产生的一种间隙锁。该锁用以表示插入意向,当多个事务在同一区间(gap)插入位置不同的多条数据时,事务之间不需要互相等待。假设存在两条值分别为 4 和 7 的记录,两个不同的事务分别试图插入值为 5 和 6 的两条记录,每个事务在获取插入行上独占的(排他)锁前,都会获取(4,7)之间的间隙锁,但是因为数据行之间并不冲突,所以两个事务之间并不会产生冲突(阻塞等待)。
总结来说,插入意向锁的特性可以分成两部分:
- 插入意向锁是一种特殊的间隙锁 —— 间隙锁可以锁定开区间内的部分记录。
- 插入意向锁之间互不排斥,所以即使多个事务在同一区间插入多条记录,只要记录本身(主键、唯一索引)不冲突,那么事务之间就不会出现冲突等待。
需要强调的是,虽然插入意向锁中含有意向锁三个字,但是它并不属于意向锁而属于间隙锁,因为意向锁是表锁而插入意向锁是行锁。
再回到上面的多事务插入示例,事务A插入数据27时,获取到的是(25,30)的间隙锁和27的行锁,事务B插入数据28时,获取到的也是(25,30)的间隙锁和28的行锁。
因为行锁27和行锁28不是同一行,所以不会冲突,然后两个事务获取到的插入意向锁不会互相排斥,所以可以插入成功。
讲了这么多,感觉“插入意向锁”好像没啥用?真的是这样的么?
为什么不用间隙锁
刚才提到,插入意向锁,其实就是特殊的间隙锁,如果只是使用普通的间隙锁会怎么样呢?
还是回到刚才的示例,我们在事务A插入数据时:
insert into t_rule1(id,c,d) values(26,16,26);
其实会获取到3把锁:
- id=26的行锁
- 字段c在区间(15,16)的间隙锁
- 字段c在区间(16,20)的间隙锁
最终,事务 A 插入了该行数据,并锁住了(15,20)这个区间。
随后事务 B 试图插入一行数据:
insert into t_rule1(id,c,d) values(27,17,26);
因为c=17位于(15,20)这个区间,而该区间内又存在一把间隙锁,所以事务 B 别说想申请自己的间隙锁了,它甚至不能获取该行的记录锁,自然只能乖乖的等待事务 A 结束,才能执行插入操作。
很明显,这样做事务之间将会频发陷入阻塞等待,插入的并发性非常之差。这时如果我们再去回想我们刚刚讲过的插入意向锁,就不难发现它是如何优雅的解决了并发插入的问题。
插入间隙锁小结
- MySql InnoDB 在 Repeatable-Read 的事务隔离级别下,使用插入意向锁来控制和解决并发插入。
- 插入意向锁是一种特殊的间隙锁。
- 插入意向锁在锁定区间相同但记录行本身不冲突的情况下互不排斥。
我甚至可以简单理解为,插入意向锁是为了避免普通间隙锁导致的并发插入问题才引入的,其实就是告诉其它事务,我是一把插入意向锁,我的权利比普通间隙锁大,你们可以不用管它,想怎么插入,就怎么插入。
锁总结
这篇文章应该包含了MySql中排查问题时,需要考虑的锁:
- 共享锁S:行级锁,读锁,读读不互斥,读写互斥;
- 排它锁X:行级锁,写锁,写写互斥;
- 行锁:锁住一行,通过排它锁X实现;
- 间隙锁Gap Lock:锁住一个区间;
- Next Key锁:行锁 + 间隙锁Gap Lock,锁住一个区间;
- 意向共享锁IS:表级锁,和意向锁都兼容,和表级共享锁S兼容,和表级排它锁X互斥;
- 意向排它锁IX:表级锁,和意向锁都兼容,和其它表级排互斥;
- 插入意向锁:特殊间隙锁,和其它插入意向锁兼容。
后记
再回到“前言"中提到的那个问题,现在可以基本断定加锁和插入耗时过高,并不是插入意向锁导致。但是如果大量数据插入,会出现锁表的情况么,我感觉应该不会。
其实后来我们排查时,事务A还有大量数据更新的操作,因为索引是联合索引,然后命中的索引只命中了第一个字段(不知道MySQL内部又搞了啥优化),且区分度很低(历史的坑),导致数据更新非常慢,我们怀疑是这里的大量更新,导致行锁升级为表锁,当然这个也只是猜测,解决问题的方式就是需要调换联合索引的字段顺序,让区分度高的字段排在索引的第一列。