Skip to main content

moregeek program

mysql随手记-我没有被面试官“锁”住-多极客编程

一位风尘仆仆的男子走了过来,对我说:小伙子,我看你简历上写的精通mysql,那我就问问你mysql的知识吧!

d989355dceac04d6d9e4bcd658804154.png

我:好的(千万别多问,千万别多问~~)

面试官:对mysql锁了解的多吗?

我:还行(其实就是很好)。

面试官:那好,那我接下来主要问锁相关的知识

我:好的好的(放马过来吧,我也就是客气一下。)

942d7301d51fc1bd2e7896e6f15b1659.jpg

面试官:mysql支持哪些级别的锁?

我:支持库锁、表锁、行锁。

面试官:那先说说库锁吧,锁库有几种方式?

我:两种,分别是FTWRL(Flush tables with read lock)和 set global readonly=true

面试官:这两种有什么区别?

我:首先不管是谁,只要锁库了,那么整个库都处于只读状态,所有的更新操作都将被阻塞,FTWRL模式风险稍微小点,如果客户端异常断开后,FTWRL锁会自动释放,但是global readonly=true不会自动释放锁。

面试官:那myisam和innodb的锁有什么区别。

我:myisam不支持行锁,只支持表级的锁,innodb支持更细粒度的行锁。

面试官:表级锁使用过吗?

我:没有用过,表锁性能差。

面试官:那你知道MDL锁吗?

我:了解,MDL(metadata lock)锁是server层的锁,表级锁,它是隐式的,不需要显式的使用,mysql每次读写数据时(insert、update、select、delete)都要先去获取MDL读锁,只有获取到了MDL读锁,才能进行接下来的操作,否则阻塞,其中MDL读锁之间是共享的,当对数据库进行表结构变更的时候,会获取MDL写锁,MDL写锁和任何MDL锁都是互斥的,不管是MDL读锁还是MDL写锁。

面试官:那MDL锁的作用是什么?

我:MDL锁是为了解决DDL和DML之间冲突的问题。

  1. 假设事务A先查询得到一个数据,然后事务B执行了字段修改,那么事务A再次去查的时候,发现数据对不上了。

c825f97effd8a71d42e36e6ef1e9ed94.jpg

  1. 事务A先更新数据未提交,事务B修改字段提交,slave就会先修改字段,再更新数据,那么就会有问题

c4c578b036cf5d7dd7a82d1f1e087c6e.jpg

当使用了MDL锁后,DDL操作必须要先获得MDL写锁,我们知道写锁和写锁,写锁和读锁是冲突的,那么在DDL之前如果有任何查询或者更新,都必须要阻塞等待,不会让DDL执行的,从而解决了冲突问题。

面试官:那有了MDL锁,在线DDL是不是就很安全?

我:不一定。

67f9b82e0efc97b8b9f4ae8775484251.jpg

假设session1先执行查询,但是不提交。session2紧接着执行添加字段的DDL,最后session3再执行查询。此时会发现session2和session3都是阻塞的,如果后面还来了sessionN的查询,那么都将阻塞,严重时将造成大量线程阻塞。

面试官:那你能解释下为什么这种情况下会阻塞吗?

我:首先MDL锁一定是在事务提交后才释放的,session1在执行查询后,并没有commit,那么MDL读锁是没有释放的,session2紧接着执行DDL,执行DDL是要获取MDL写锁的,由于写锁和读锁是互斥的,那么session2是卡住的,它在等待session1释放读锁,session3在session2之后执行的,此时session3是需要一个读锁的,但是由于获取锁是有先后顺序的,它们要排队,并且写锁的优先级要高于读锁,这也是为什么session3会卡住。

面试官:所以是session1执行commit之后,然后session2先执行完,最后session3先执行完?我刚试了下你的例子,看着好像session2和session3几乎同时运行完,你能否具体说说为什么呢?(看你进不进坑)

2148b46872c93c7054bdda54f1f52592.png

我:(想给我挖坑,没门),不是的,并不是session2先执行完,其实是session3先执行完,但是session2会先获得MDL写锁,由于session3没有显式的开启一个事务,那么session3默认执行完毕之后自动commit,所以在session1 commit之后,看起来像session2和session3几乎同时进行的。如果让session3显式的开启事务,就能发现运行的细节了。

0a899a186b413e00f073ff878c1b19ac.jpg

这样当session1 commit之后可以发现session3先执行,session2依然是卡住的,只有当session3 commit之后,发现session2才能运行。所以真实情况应该是session3先执行,然后session2。

面试官:那这和你上面说的获取MDL锁排队的问题是不是矛盾了?

我:不矛盾,这其实涉及到online DDL的知识了,我们知道mysql支持在线DDL,不阻塞用户操作,当执行一个DDL,它的流程大概是这样的:

  1. 拿MDL写锁
  2. 降级成MDL读锁
  3. 真正做DDL
  4. 升级成MDL写锁
  5. 释放MDL锁

其中session2在拿到MDL写锁后,会降级成MDL读锁,降级后,session3拿到MDL读锁,然后执行select,但是没有commit,这样MDL读锁就没释放,然后session2在升级成MDL写锁的时候因为session3没释放读锁从而导致session2阻塞。

面试官:(这小子不错呀),那你知道为什么DDL过程中1-2要降级,而3-4又要升级吗?

我:(早知道就要问,还好我有准备),

7868d8386a84f9da76338f8d8db514f9.jpg

首先在MDL写锁期间,干的事就是创建临时的frm和idb文件,这个过程要安全,是排他的,同时这个过程也是快速的,在临时文件创建好之后,就不需要排他了,那么就降级为读锁,支持正常的增删改查,这也是为什么DDL支持online的原因之一。在新的数据文件写好之后,要替换老的数据文件,这个过程要安全,所以在3执行完后,会尝试升级成MDL写锁,这个过程也是快速的,也是支持online DDL的原因之二。

面试官:我们知道InnoDB支持行级锁,行锁还分两类你知道吗?

我:知道,S(共享锁)和X(排他锁),S锁和S锁是共享的,X锁和任意锁互斥。

面试官:既然X锁和任意锁互斥,那么如果存在两个事务,事务A更新数据后,不提交,那么事务B去查询这条数据是不是就阻塞?

7353a969a8440ee0ad033576953dc854.jpg

我:不会的,因为InnoDB支持MVCC(多版本控制),当一个事务执行查询的时候,它可以通过undo log查询到一个快照,这样就不用锁。

面试官:那你知道IS(意向共享锁)和IX(意向排他锁)吗?

我:首先它俩都是表级别的锁,因为InnoDB是支持行锁的,当某些行已经上了X锁之后,再想对这个表上锁的话,就得确认当前表中没有任何X锁,在没有意向锁的情况下,就得一行一行去判断,这样效率会非常低下,在有了意向锁之后,就不需要一行一行判断了,举个例子:

select * from user where id=1 for update;

当一个事务对id=1这行数据上了X锁之后,就会对user表也加一个IX锁。

LOCK TABLES user READ;

这时想要给表加上读锁,但是发现表上有IX锁,所以会阻塞无法执行。类似的如果一个事务对一行数据加上共享锁

select * from user where id=1 lock in share mode;

就会给对应的表加上IS锁。这时候如果执行

LOCK TABLES user WRITE;

也会因为表上有IS锁而阻塞。

面试官:那我们来聊聊行锁吧,InnoDB支持哪些行锁?

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

面试官:假设有一张表,表里有10条记录,还有个字段user_id,并且user_id是普通索引。

+----+---------+
| id | user_id |
+----+---------+
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
| 4 | 40 |
| 5 | 50 |
| 6 | 60 |
| 7 | 70 |
| 8 | 80 |
| 9 | 90 |
| 10 | 100 |
+----+---------+

如果事务A执行:

SELECT * FROM user WHERE user_id=50 FOR UPDATE;

紧接着事务B执行下面的sql会发生什么?:

INSERT INTO user set user_id=45;

我:阻塞。

面试官:能说下原因吗?

我:因为InnoDB的Next-Key Lock算法,不仅仅会锁住user_id=50这条记录,还会锁住50左右的间隙。Next-Key Lock锁定的范围是左开右闭的,那么理论上最终(40,50],(50,60]的区间数据会被锁定。

e1dae23598609a49bfc0dc357510626a.jpg

由于要插入的45在40-50之间,所以就会发生阻塞。

面试官:那按照你说的区间锁法,是包含60这条数据的是吧,所以如果插入一条60的数据,就会发生阻塞?

INSERT INTO user set user_id=60;

我:其实不会,这里涉及到next key lock的优化,在等值查询中,向右遍历时且最后一个值不满足等值条件的时候,Next-Key Lock会退化为Gap Lock,所以对于(50,60]这个区间最终会降级为(50,60),那么60这条数据就不会被锁住,是可以插入成功的。

面试官:那40这条数据是不在这个锁区间的,所以可以插入40这条数据?

INSERT INTO user set user_id=40;

我:其实也不会,40这条数据的插入也会阻塞,首先对于非聚集索引user_id它的叶子节点一定是排序的,大概就像(40,4),(50,5)这样,其次因为主键id是自增的,那么对于再插入一条40的数据,它的主键id一定是大于4的,就目前10条数据来说,下一次插入的id肯定是11,那(4,11)这条数据肯定是要在(40,4)后面的,这样的话,就落入到了间隙锁中,所以会阻塞,其实上面60那条数据可以插入也是同样的道理。

07d2612a92196b198b02347a13e048d6.jpg

面试官:那如果我一开始不用 select for update 了,而用 select lock in share mode ,那么所有的插入会有什么变化吗?

我:没有变化,还是一样,因为插入需要X锁,X锁和任何锁都互斥。

面试官:如果user_id不是普通索引而是唯一索引,那会有什么变化?

我:当索引是唯一索引时,那么就会发生降级,Next Key Lock会降级成Record Lock,最终只会锁住50这条记录。

面试官:如果user_id没有索引怎么办?

我:那就所有的记录都会锁上,任何的插入都会阻塞。

面试官:那你知道为什么要有间隙锁这个东西吗?

我:为了解决幻读。比如当事务A执行以下查询时:

SELECT * FROM user WHERE id>=9 for update

应该返回两条记录(id=9和id=10),这时候如果另一个事务B执行

INSERT INTO user set user_id=110;

在没有间隙锁的情况下,那么事务A再次查询会发现多了一条记录,就出现了幻读,如果有了间隙锁,那么[9,+∞)这个区间都会被锁住,事务B的插入就会阻塞。但是只有在事务的隔离级别设置成可重复读的时候,才支持间隙锁。

面试官:如果某个上了锁的事务一直不提交,那么后面需要获取相关锁的事务就会阻塞,这样会有什么问题?

我:如果阻塞的事务越来越多,那么阻塞的线程也会越来越多,严重时会造成连接池满了,mysql不能提供服务了。但是InnoDB支持阻塞超时后,会自动放弃这个等待锁的sql命令,这个值默认是50s。

+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+

面试官看了看我:那你知道AUTO-INC Locking吗?

我:知道,自增长锁,在InnoDB引擎中,每个表都会维护一个表级别的自增长计数器,当对表进行插入的时候,会通过以下的命令来获取当前的自增长的值。

SELECT MAX(auto_inc_col)  FROM user FOR UPDATE;

插入操作会在这个基础上加1得到即将要插入的自增长id。

面试官:我们知道事务中的锁是在事务提交后才释放的,那么在更新自增长id后,当事务没来及提交,其它的事务获取自增长id就要等待吗?这样的话效率是不是有点低?

我:不用等待的,为了提高插入性能,自增长的锁不会等到事务提交之后才释放,而是在相关插入sql语句完成后立刻就释放的,这也是为什么一些事务回滚之后,发现id不连续的原因:

select * from user;
+----+---------+
| id | user_id |
+----+---------+
| .. | .. |
| 9 | 90 |
| 10 | 100 |
| 12 | 120 |
+----+---------+
# 11那条数据回滚了,但是id也被消耗了,id不会回滚。

面试官:虽然AUTO-INC Locking可以不用等事务提交就释放,但是在并发的时候,因为AUTO-INC Locking本身会对自增id上锁的,还是会影响效率,这个该怎么解决?

我:现在InnoDB支持互斥量的方式来实现自增长,通过互斥量可以对内存中的计数器进行累加操作,比AUTO-INC Locking要快些。

面试官:那你知道死锁吗?

我:知道。

面试官:什么情况下会出现死锁?

我:死锁出现的条件就是请求和保持,就是每一方都保持着对方的需要的资源同时请求对方占用的资源。比如:

f61c4dac83d1cbc2e286fede857f23a6.jpg

事务1先锁id=1这条数据,紧接着事务2锁住id=2这条数据,然后事务1再次尝试锁住id=2这条数据,但是发现被事务2占着在,所以此时事务1会阻塞,最后事务2尝试获取id=1的锁,但是发现被事务1占着在,所以也会阻塞,那么此时就陷入僵局了,这就是死锁。

面试官:那如何解决死锁问题呢?

我:

  1. InnoDB提供锁超时的功能,当一个事务获取锁超时之后会自动放弃,另一个事务就可以执行。
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
  1. 可以让每次更新都按照约定的顺序去更新,这样也可以避免死锁。

961786240123826e03acd08059863d3d.jpg

  1. 通过死锁检测来提前判断,InnoDB默认是开启死锁检测的。
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON |
+------------------------+-------+

InnoDB通过等待图的方式来进行死锁检测的,这要求存储锁的信息链表和事务的等待链表,然后通过链表构造一张等待图,每次获取锁的时候会通过等待图来判断是否会造成死锁。

面试官看了看自己的劳力士:(看来今天锁不住这个小伙子了,时间也差不多了)那你在这稍等下,我喊下hr。

179edd7de7f01afd5a6c9f2c19cd6233.png

我:好的好的(终于结束了)。

©著作权归作者所有:来自51CTO博客作者你礼貌吗的原创作品,如需转载,请注明出处,否则将追究法律责任
mysql随手记-我没有被面试官“锁”住
https://blog.51cto.com/u_15305578/3982906

「推荐收藏!」【MySQL技术之旅】(2)「主从架构」MySQL主从数据同步模式的指南分析-多极客编程

MySQL的主从复制 一般在大规模的项目上,都是使用MySQL的复制功能来创建MySQL的主从集群的。 主要是可以通过为数据库服务器配置一个或多个备库的方式来进行数据同步。 复制的功能不仅有利于构建高性能应用,同时也是高可用、可扩展性、灾难恢复、备份以及数据仓库等工作的基础。 通过MySQL的主从复制来实现读写分离,相比单点数据库又读又写来说,提升了业务系统性能,优化了用户体验。 另外通过主

CentOS+Nginx+Tomcat+Mysql+PHP-多极客编程

一、安装centos 7.0 选择最小安装,将相关的"调试工具"、“兼容性程序库”、“开发工具”选中。此操作是为了减少后期安装或编译相关服务时出现依赖、或环境的问题。硬盘分区,可根据个人的习惯而定,不清楚的可以直接选择系统自动分区,由于个人的习惯,本人的分区如下,仅供参考:/boot        500M                 用于启动Linux的核心文件swap        81

对线面试官之MySQL索引篇-多极客编程

面试官:我看你简历上写了MySQL,对MySQL InnoDB引擎的索引了解吗? 候选者:嗯啊,使用索引可以加快查询速度,其实上就是将无序的数据变成有序(有序就能加快检索速度) 候选者:在InnoDB引擎中,索引的底层数据结构是B+树 面试官:那为什么不使用红黑树或者B树呢? 候选者:MySQL的数据是存储在硬盘的,在查询时一般是不能「一次性」把全部数据加载到内存中 候选者:红黑树是「二叉查找树」

MySQL基础学习教程,30分钟快速入门mysql数据库-多极客编程

MySQL的相关概念MySQL 为关系型数据库(Relational Database Management System), 这种所谓的"关系型"可以理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成, 如图所示的一个表格:表头(header): 每一列的名称;列(row): 具有相同数据类型的数据的集合;行(col): 每一行用来描述某个人/物的具体信息;值(value): 行的具体

想了解Xtrabackup备份原理和常见问题分析,看这篇就够了-多极客编程

摘要:本文来自华为云MySQL研发团队,主要分享了MySQL备份工具Xtrabackup的备份过程、华为云数据库团队对其做的优化改进,以及在使用中可能遇到的问题与解决方法。本文分享自华为云社区《​​华为云带你探秘Xtrabackup备份原理和常见问题分析​​》,作者:GaussDB 数据库 。本文来自华为云MySQL研发团队,主要分享了MySQL备份工具Xtrabackup的备份过程、华为云数据库

docker 安装 wordpress,通过nginx反向代理,绑定域名,配置https-多极客编程

关注公众号:大数据技术派,回复“资料”,领取资料,学习大数据技术。假设​​docker​​​已经安装好了,如果没有安装,可以照着 ​​5分钟安装docker教程​​。 一. 下载镜像默认下载最新版本,如果想指定对应版本,可以用冒号后加版本,像这样​​mysql:5.7​​: docker pull mysql:5.7docker pull wordpres

JVM的内存分代,这篇文章帮你理一理-多极客编程

听说微信搜索《Java鱼仔》会变更强哦! 本文收录于github和gitee ,里面有我完整的Java系列文章,学习或面试都可以看看哦 (一)JVM分代 堆内存是JAVA虚拟机所管理的内存最大的一块,Java堆被所有线程共享,几乎所有的对象实例都是在堆中分配内存,因此Java的堆是垃圾回收的主要区域。 JVM的内存分代讲的就是堆内存的分代,为了更加高效的回收垃圾,将内存划分为了多个genera

最新的前端大厂面经(详解答案)-多极客编程

前言 文章转载:乐字节 Hello 大家好 我是鲨鱼哥 还记得辣个一天面了6 家公司最后拒了所有大厂的男人吗 没错正是在下 哈哈 不了解的同学可以看下这篇一天面试 6 家大厂-offer 拿来吧你 介于群友一直在催我的面经 因为前段时间忙(其实是懒) 在拖延了一个半月后 我们又见面啦 哈哈 本文整理了鲨鱼哥最近面试期间出现的面试题并且附带详解答案 难度分为简单 中等 困难 三种类型 大家可以先不看

学会这5种JS函数继承方式,前端面试你至少成功50%-多极客编程

​​​​摘要: 函数继承是在JS里比较基础也是比较重要的一部分,而且也是面试中常常要问到的。下面带你快速了解JS中有哪几种是经常出现且必须掌握的继承方式。掌握下面的内容面试也差不多没问题啦~ 本文分享自华为云社区《人类高质量JS函数继承》,作者:北极光之夜。  。 一、前言 函数继承是在JS里比较基础也是比较重要的一部分,而且也是面试中常常要问到的。下面带你快速了解JS中有哪几种是经常出现且必

关于垃圾收集器你了解多少?一文总结七大垃圾收集器-多极客编程

听说微信搜索《Java鱼仔》会变更强哦! 本文收录于github和gitee ,里面有我完整的Java系列文章,学习或面试都可以看看哦 (一)概述 如果说垃圾收集算法是内存回收的理论,那么垃圾收集器就是内存回收的具体实现。 垃圾收集器目前存在的有很多,但是依旧没有哪个收集器是万能的存在,我们只能选择一个最适合应用的收集器。 下面会介绍目前主流Java虚拟机中所采用的七种垃圾收集器:Serial