阅读 782

关于MySQL常见的所有锁,已汇总整理完毕

「本文已参与好文召集令活动,点击查看:后端、大前端双赛道投稿,2万元奖池等你挑战!

这篇就啥表锁、IS、IX、MDL、记录锁、间隙锁、Next-key locks、插入意向锁、Auto-Inc Locks一文搞懂!

咱们通过面试实战来理解。


我,小Y。

又来面试了,还是之前那家公司,即将和之前那个老面试官进行第二次 battle,心情还是xue微有点忐忑。

没看过第一次 battle 的同学可以看这里,一个MVCC和面试官大战三十回合

又一抹光亮闪过,面试官推门而入,我抬头望去,没错,还是那味儿。

看到面试官头上那“傲然矗立”的头发,差点又想站起来给他敬了个礼,算了先稳住,低调一点。

面试官瞥了我一眼:来吧,咱们继续面试,上次没办法,女朋友就是粘人,这次问 MySQL InnoDB 的锁喔。

我:.....(行,我知道你有女朋友了),好的面试官,您请。

面试官:MySQL InnoDB 的锁 和 MyISAM 的锁有什么区别?

我:MyISAM 只支持表锁,一锁就锁整张表,而 InnoDB 不仅支持表锁,还支持粒度更低的行锁,仅对相关的记录上锁即可,所以对于写入操作来说 InnoDB 的性能更高。

面试官:那不论表锁还是行锁,其实有分为两类的,你知道是哪两类吗?

我:你指的是 shared (S) locks 和 exclusive (X) locks 吗?

  • S锁,称为共享锁,事务在读取记录的时候获取 S 锁,它允许多个事务同时获取 S 锁,互相之间不会冲突。

  • X锁,称为独占锁,事务在修改记录的时候获取 X 锁,且只允许一个事务获取 X 锁,其它事务需要阻塞等待。

所以 S 锁之间不冲突,X 锁则为独占锁,所以 X 之间会冲突, X 和 S 也会冲突。

冲突SX
S不冲突冲突
X冲突冲突

不论是表级别锁还是行级别锁,S 和 X 的特性都是一样的。

面试官:你说事务在读取记录的时候需要获取 S 锁?这不对吧?

我:确实不准确。益与 MVCC 的功劳,普通的 select 是不需要加锁的,而 SELECT ... LOCK IN SHARE MODE; 这种读取需要对记录上 S 锁。SELECT ... FOR UPDATE; 需要对记录上 X 锁。

面试官:对了,你刚提到表级锁,那你平时用过 InnoDB 的表锁吗?

我:没用过,InnoDB 的表锁很鸡肋,我知道:

  • LOCK TABLES yes READ 是对 yes 这个表上 S 锁。
  • LOCK TABLES yes WRITE 是对 yes 这个表上 X 锁。

但是基本上没用。

面试官:噢?怎么个鸡肋了?

我:平日的 update 、select 要用也是用行锁了,不可能用粒度粗的表锁。唯一能想到用上表锁的就是 DDL 语句了,比如 ALTER TABLE 的时候,应该锁定整个表,防止查询和修改,但是这个 server 已经提供了一个叫 MDL 的东西,即 Metadata Locks,所以已经用 MDL 来阻塞了,表锁也就排不上用场了。

真要用表锁,估计也就是数据恢复的时候,手动锁表还原数据了。

面试官摸了摸头上的反光处:可以,但是如果真要到用表锁的时候,那表锁和行锁之间不是会冲突的吗?如果表里面已经加了行锁怎么办?得一条记录一条记录遍历过去找行锁吗?

我:这确实是一种实现方式,但是性能太差了,假设数据库里有上千万的数据,这加个表锁得找死。

所以有了个叫意向锁(Intention Locks)的东西。

  • IS(Intention Shared Lock),共享意向锁
  • IX(Intention Exclusive Lock),独占意向锁。

这两个锁是表级别的锁,当需要对表中的某条记录上 S 锁的时候,先在表上加个 IS 锁,表明此时表内有 S 锁。当需要对表中的某条记录上 X 锁的时候,先在表上加个 IX 锁,表明此时表内有 X 锁。

这样操作之后,如果要加表锁,就不需要遍历所有记录去找了,直接看看表上面有没有 IS 和 IX 锁。

比如,此时要上表级别的 S 锁,如果表上没有 IX ,说明表中没有记录有独占锁,其实就可以直接上表级 S 锁。

如果此时要上表级别的 X 锁,如果表上没有 IX 和 IS ,说明表中的所有记录都没加锁,其实就可以直接上表级 X 锁。

因此 IS 和 IX 的作用就是在上表级锁的时候,可以快速判断是否可以上锁,而不需要遍历表中的所有记录

所以 IS 和 IX 互相之间是不会冲突的,因为它们的作用只是打个标记,来丰富一下上面的表格:

冲突SXISIX
S不冲突冲突不冲突冲突
X冲突冲突冲突冲突
IS不冲突冲突不冲突不冲突
IX冲突冲突不冲突不冲突

面试官:行,那再来说说行锁吧,InnoDB 有几类行锁?

我:有记录锁(Record Locks)、间隙锁(Gap Locks)、Next-Key Locks。

面试官:详细说说看?

我:记录锁顾名思义就是锁住当前的记录,它是作用到索引上的。我们都知道 innodb 是肯定有索引的,即使没有主键也会创建隐藏的聚簇索引,所以记录锁总是锁定索引记录

比如,此时一个事务 A 执行 SELECT * FROM yes WHERE name = 'xx' FOR UPDATE; 那么 name = xx 这条记录就被锁定了,其他事务无法插入、删除、修改 name = xx 的记录。

此时事务 A 还未提交,另一个事务 B 要执行 insert into yes (name) values ('xx'),此时会被阻塞,这个很好理解。

但是,如果另一个事务 C 执行了 insert into yes (name) values ('aa'),这个语句会被阻塞吗?

看情况

如果 name 没有索引。前面提到记录锁是加到索引上的,但是 name 没索引啊,那只能去找聚簇索引,但聚簇索引上面只有主键啊,它哪知道各自的 name 是什么,所以咋办?都锁了呗!

因此,如果 name 没有索引,那么事务 C 会被阻塞,如果有索引,则不会被阻塞

所以这里要注意,没索引的列不要轻易的锁,不要以为有行锁就可以为所欲为,并不是这样滴。

面试官:哟,有点东西,继续继续。

我:然后是间隙锁,这个东西它有点东西。

前面说了,记录锁需要加到记录上,但是如果要给此时还未存在的记录加锁怎么办?也就是要预防幻读的出现!

这时候间隙锁就派上用场了,它是给间隙加上锁。

比如此时有 1、3、5、10 这四条记录,之前的文章分析过,数据页中还有两条虚拟的记录,分别是 InfimumSupremum

可以看到,记录之前都有间隙,那间隙锁呢,锁的就是这个间隙

比如我把 3 和 5 之间的间隙锁了,此时要插入 id = 4 的记录,就会被这个间隙锁给阻塞了,这样就避免了幻读的产生!也就实现了锁定未插入的记录的需求!

还有个 Next-Key Locks 就是记录锁+间隙锁,像上面间隙锁的举例,只能锁定(3,5) 这个区间,而 Next-Key Locks 是一个前开后闭的区间(3,5],这样能防止查询 id=5 的这个幻读。

面试官:那间隙锁之间会不会冲突?

我 :不会,间隙锁的唯一目的就是防止其他事务插入数据到间隙中 ,所以即使两个间隙锁要锁住相同的间隙也没有关系,因为它们的目的是一致的,所以不冲突。

面试官:那间隙锁可以显式禁用吗?

我 :可以的。间隙锁是在事务隔离级别为可重复读的时候生效的,如果将事务隔离级别更改为 READ COMMITTED,就会禁用了,此时,间隙锁对于搜索和索引扫描是禁用的,仅用于外键约束检查和重复键检查。

面试官:说到间隙锁,那你知道什么是插入意向锁吗?

我:插入意向锁,即 Insert Intention Locks,它也是一类间隙锁,但是它不是锁定间隙,而是等待某个间隙。比如上面举例的 id = 4 的那个事务 C ,由于被间隙锁给阻塞了,所以事务 C 会生成一个插入意向锁,表明等待这个间隙锁的释放。

并且插入意向锁之间不会阻塞,因为它们的目的也是只等待这个间隙被释放,所以插入意向锁之间没有冲突。

面试官:所以这个插入意向锁其实没什么用的?

我:确实,它的目的不在于锁定资源防止别人访问,我个人觉得更像是为了遵循 MySQL 的锁代码实现而为之。

锁其实就是内存里面的一个结构,每个事务为某个记录或者间隙上锁就是创建一个锁对象来争抢资源。

如果某个事务没有抢到资源,那也会生成一个锁对象,只是状态是等待的,而当拥有资源的事务释放锁之后,就会寻找正在等待当前资源的锁结构,然后选一个让它获得资源并唤醒对应的事务使之得以执行。

所以按照这么个逻辑,那些在等待间隙锁的插入事务,也需要对应的建立一个锁结构,然后锁类型是插入意向锁

这样一来,间隙锁的事务在释放间隙锁的时候,才能得以找到那些等待插入的事务,然后进行唤醒,而由锁的类型也可以得知是插入意向锁,之间不需要阻塞,所以可以一起执行插入。

面试官:说到插入新记录我问你个问题,如果插入的事务还未提交,现在有另一个事务通过SELECT ... LOCK IN SHARE MODE 或者SELECT ... FOR UPDATE 打算读取这条记录怎么办?此时生效的是什么锁?

我:(我丢,面试官想给我挖坑?哼,但是这难不倒我霸中霸!)

SELECT ... LOCK IN SHARE MODE 或者SELECT ... FOR UPDATE` 是要获取记录 S 锁和 X 锁的,但是此时事务还未提交,因此这两类 select 会阻塞。

具体是怎么阻塞的呢?因为有事务ID!通过 MVCC 可以利用事务ID 来进行判断当前记录是否可见,这其实相当于一把隐式锁!知道当前记录不可见,于是这个查询事务会为之前未提交的插入的事务生成一个锁结构,然后查询事务自己也生成锁结构,接着等待插入事务的释放,这样就完成了阻塞!

面试官:(这小子,我要压不住他了!)行,那你知道什么是 AUTO-INC Locks 锁吗?

我:知道,Auto-Inc Lock 是一个特殊的表级锁,用于自增列插入数据时使用。 在插入一条数据的时候,需要在表上加个 Auto-Inc Lock,然后为自增列分配递增的值,在语句插入结束之后,再释放 Auto-Inc Lock。

在 MySQL 5.1.22 版本之后,又弄了个互斥量来进行自增减的累加。互斥量的性能高于 Auto-Inc Lock,因为 Auto-Inc Lock是语句插入完毕之后才释放锁,而互斥量是在语句插入的时候,获得递增值之后,就可以释放锁,所以性能更好。

但是我们还需要考虑主从的情况,由于并发插入的情况,基于 statement -based binlog 复制时,自增的值顺序无法把控,可能会导致主从数据不一致。

所以 MySQL 有个 innodb_autoinc_lock_mode 配置,一共有三个值:

  • 0,只用 Auto-Inc Lock。
  • 1,默认值,对于插入前已知插入行数的插入,用互斥量,对于插入前不知道具体插入数的插入,用 Auto-Inc Lock,这样即使基于 statement -based binlog 复制也是安全的。
  • 2,只用互斥量。

面试官:那 MyISAM 有 AUTO-INC Locks 锁吗?

我:没啊,MyISAM 插入本来就用了表锁。

面试官:(这小子行啊,我得找回行子)那你还知道 MySQL 有什么锁吗?

我:(这还没问够??)表锁、IS、IX、MDL、记录锁、间隙锁、Next-key locks、插入意向锁、Auto-Inc Locks,还有啥?

面试官瞥了我一眼:(好小子,总算治了你了)不知道了?

我:(该怂的时候,还是得怂)知识盲区了,请面试官教教我。

面试官: 还有个 Predicate Locks,谓词锁。

我:什么玩意?

面试官:InnoDB 是支持空间数据的,所以有空间索引,为了处理涉及空间索引的操作的锁定,next-key locking 不好使,因为多维数据中没有绝对排序的概念,因此不清楚“下一个” key 在哪。

所以为了支持具有空间索引的表的隔离级别,InnoDB使用谓词锁。

空间索引包含最小边界矩形(MBR)值,因此 InnodB 通过在用于查询的 MBR 值上设置谓词锁定,使得 InnoDB 在索引上执行一致性读, 其他事务无法插入或修改与查询条件匹配的行。

我:(.....果然超出了我的知识范围,这个B被他装到了)老面试官您真的是66666。

面试官:行吧,你今天答的马马虎虎还可以,下次再问问你啥 buffer pool、change buffer、doublewrite buffer 啥的。

我:(????还问呢),这是还继续面吗?这算三面吗?

面试官:你管我,我就想多面面你,充分了解一下,我们公司很严格的,人不是随便招的!赶紧回去等通知!

我:行行行,您老等着,就一堆 buffer 是吧,我回去好好准备准备哈~


今天就到这儿,下篇我再画个图总结汇总一波今天的锁~

更多面试文章,可看我的个人 GitHub 仓库 github.com/yessimida/y…

微信搜「 yes的练级攻略 」第一时间阅读,回复【算法】有我准备一份拿到 ssp offer 20W 字的算法笔记。

等我下篇哈,来个关注不迷路~如果觉得文章不错,来个点赞哟!

文章分类
后端
文章标签