【MySql系列2】一文带你搞懂MySql的各种锁

内容包括共享锁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 表看是否每一行数据是否持有行级排它锁当发现id5加了行锁事务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 的两条记录每个事务在获取插入行上独占的排他锁前都会获取47之间的间隙锁但是因为数据行之间并不冲突所以两个事务之间并不会产生冲突阻塞等待。总结来说插入意向锁的特性可以分成两部分插入意向锁是一种特殊的间隙锁 —— 间隙锁可以锁定开区间内的部分记录。插入意向锁之间互不排斥所以即使多个事务在同一区间插入多条记录只要记录本身主键、唯一索引不冲突那么事务之间就不会出现冲突等待。需要强调的是虽然插入意向锁中含有意向锁三个字但是它并不属于意向锁而属于间隙锁因为意向锁是表锁而插入意向锁是行锁。再回到上面的多事务插入示例事务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把锁id26的行锁字段c在区间15,16的间隙锁字段c在区间16,20的间隙锁最终事务 A 插入了该行数据并锁住了15,20这个区间。随后事务 B 试图插入一行数据insert into t_rule1(id,c,d) values(27,17,26);因为c17位于15,20这个区间而该区间内又存在一把间隙锁所以事务 B 别说想申请自己的间隙锁了它甚至不能获取该行的记录锁自然只能乖乖的等待事务 A 结束才能执行插入操作。很明显这样做事务之间将会频发陷入阻塞等待插入的并发性非常之差。这时如果我们再去回想我们刚刚讲过的插入意向锁就不难发现它是如何优雅的解决了并发插入的问题。插入间隙锁小结MySql InnoDB 在 Repeatable-Read 的事务隔离级别下使用插入意向锁来控制和解决并发插入。插入意向锁是一种特殊的间隙锁。插入意向锁在锁定区间相同但记录行本身不冲突的情况下互不排斥。我甚至可以简单理解为插入意向锁是为了避免普通间隙锁导致的并发插入问题才引入的其实就是告诉其它事务我是一把插入意向锁我的权利比普通间隙锁大你们可以不用管它想怎么插入就怎么插入。锁总结这篇文章应该包含了MySql中排查问题时需要考虑的锁共享锁S行级锁读锁读读不互斥读写互斥排它锁X行级锁写锁写写互斥行锁锁住一行通过排它锁X实现间隙锁Gap Lock锁住一个区间Next Key锁行锁 间隙锁Gap Lock锁住一个区间意向共享锁IS表级锁和意向锁都兼容和表级共享锁S兼容和表级排它锁X互斥意向排它锁IX表级锁和意向锁都兼容和其它表级排互斥插入意向锁特殊间隙锁和其它插入意向锁兼容。