mysql的锁是应用级的吗_MySQL的锁到底有多少内容？再和腾讯大佬的技术面谈，我还是小看锁了！...-CSDN博客

本文链接：https://blog.csdn.net/weixin_34979632/article/details/113294165

对酒当歌，人生几何！朝朝暮暮，唯有己脱。微信搜索【莲花童子哪吒】

苦苦寻觅找工作之间，殊不知今日之时乃我心之痛，难到是我不配拥有工作嘛。自面试后他所谓的等待都过去一段时日，可惜在下京东上的小金库都要见低啦。每每想到不由心中一紧。正处为难之间，手机忽然来了个短信预约后续面试。我即刻三下五除二拎包踢门而出。飞奔而去。微信搜搜【莲花童子哪吒】

此刻面试门外首先映入眼帘的是一个白色似皮球的东西，似圆非圆。好奇冬瓜落地一般。上半段还有一段湿湿的部分，显得尤为入目。这是什么情况？

紧接着现身一名中年男子。他身着纯白色T桖衫的，一灰色宽松的休闲西裤，腰围至少得三十好几。外加一双夏日必备皮制凉鞋。只见，他正低头看着手上的一张A4纸。透过一头黑色短发。满脸的赘肉横生。外加上那大腹便便快要把那T桖衫给撑爆的肚子。

看得我好生害怕，不由得咽了咽口水，生怕自己说错话。这宛如一颗肉粽呀。不在职场摸滚打拼8、9年，也不会有当前这景象。微信搜索【莲花童子哪吒】

什么是锁

「面试官：」: 你是来参加面试的吧？「吒吒辉：」不不不，我是来参加复试呢。

「面试官：」: 看到上次别人点评，MySQL优化还阔以。那你先谈谈对锁的理解？

「吒吒辉：」嘿嘿，还好！

「锁」是计算机在进行「多进程、线程」执行调度时强行限制资源访问的同步机制，用于在「并发访问」时保证数据的一致性、有效性；锁是在执行多线程时，用于强行限制资源访问的同步机制，即用在并发控制中保证对互斥的要求。一般的锁是建议锁(advisory lock)，每个线程在访问对应资源前都需获取锁的信息，再根据信息决定是否可以访问。若访问对应信息，锁的状态会改变为锁定，因此其它线程此时不会来访问该资源，当资源结束后，会恢复锁的状态，允许其他线程的访问。有些系统有强制锁(mandatory lock)，若有未授权的线程想要访问锁定的数据，在访问时就会产生异常。 ---《维基百科》

锁的类型和应用原理

「面试官：」: 那一般数据库有哪些锁？一般怎么使用？此刻，用我那呆若木鸡的眼神看向面试官，内心实属「尴尬+害怕」，数据库不就是共享和互斥锁吗？

这样看来，是我太嫩。此处必有坑。殊不知此刻我内心已把你拿捏，定斩不饶。微信搜索【莲花童子哪吒】

「吒吒辉：」数据库的锁根据不同划分方式有很多种说法，在业务访问上有以下两种：排他锁

在访问共享资源之前对其进行加锁，在访问完成后进行解锁操作。加锁成功后，任何其它线程请求来获取锁都会被阻塞，直到当前线自行释放锁。线程3状态:就绪、阻塞、执行

如解锁时，有一个以上的线程阻塞(资源已释放)，那么所有尝试获取该锁的线程都被CPU认为「就绪状态」，如果第一个就绪状态的线程又执行加锁操作，那么其他的线程又会进入就绪状态。在这种方式下，只能有一个线程访问被互斥锁保护的资源

故此，MySQL的SQL语句加了互斥锁后，只有接受到请求并获取锁的线程才能够访问和修改数据。因为互斥锁是针对线程访问控制而不是请求本身。共享锁

被加锁资源是可被共享的，但仅限于读请求。它的写请求只能被获取到锁的请求独占。也就是加了共享锁的数据，只能够当前线程修改，其它线程只能读数据，并不能修改。

「吒吒辉：」在 SQL 请求上可分为读、写锁。但本质还是对应对共享锁和排它锁。

「面试官：」那 SQL 请求上不加锁怎么访问？为啥说它们属于共享锁和排他锁? 这之间有何联系？微信搜索【莲花童子哪吒】

「吒吒辉：」除加锁读外，还有一种不加锁读的情况。这种方式称为「快照读，读请求加锁称为共享读。」

针对请求加共享、排它锁的原因在于，读请求天生是「幂等性」的，不论你读多少次数据不会发生变化，所以给读请求加上锁就应该为共享锁。不然怎么保证它的特点呢？而写请求，本身就需对数据进行修改，所以就需要排它锁来保证数据修改的一致性。

「吒吒辉：」如果按照锁的颗粒度划分看，就有「表锁和行锁」表锁：是MySQL中最基本的锁策略，并且是开销最小的策略。并发处理较少。表锁由MySQL服务或存储引擎管理。多数情况由服务层管理，具体看SQL操作。

例如：服务器会为诸如「ALTER TABLE 之类的语句使用表锁」，而忽略存储引擎的锁。加锁机制:

它会锁定整张表。一个用户在对表进行写操作(插人、删除、更新等)前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他用户才能获取到读锁。行锁:

锁定当前访问行的数据，并发处理能力很强。但锁开销最大。具体视行数据多少决定。由innoDB存储引擎支持。

页级锁：页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折衷的页级锁，一次锁定相邻的一组记录。由BDB 存储引擎管理页级锁。

「面试官：」为啥是表锁开销小，而不是行锁呢？毕竟表锁锁定是整张表

「吒吒辉：」表锁锁定的是表没错，但它不是把表里面所有的数据行都上锁，相当于是封锁了表的入口，这样它只是需要判断每个请求是否可以获取到表的锁，没有就不锁定。

而行锁是针对表的每一行数据，数据量一多，锁定内容就多，故开销大。但因它颗粒度小，锁定行不会影响到别的行。所以并发就高。而如果表锁在一个入口就卡死了，那整体请求处理肯定就会下降。

「面试官：」我记得行锁里面有几种不同的实现方式，你知道吗？您可真贴心啊，替我考虑这么多，大佬都是这么心比针细？我要是说不知道，你老是不是又准备给出穿小鞋啦。强忍内心啃人的冲动ps:读懂图，说明你有故事

「吒吒辉：」 innodb虽支持行锁，但锁实现的算法却和SQL的查询形式有关系：Record Lock(记录锁)：单个行记录上的锁。也就是我们日常认为的行锁。由where =的形式触发

Gap Lock(间隙锁)：间隙锁，锁定一个范围，但不包括记录本身(它锁住了某个范围内的多个行，包括根本不存在的数据)。

GAP锁的目的，是为了防止事务插入而导致幻读的情况。该锁只会在隔离级别是RR或者以上的级别内存在。间隙锁的目的是为了让其他事务无法在间隙中新增数据。 SQL里面用 where >、>=等范围条件触发，但会根据锁定的范围内，是否包含了表中真实存在的记录进行变化，如果存在真实记录就会进化为临建锁。反之就为间隙所。Next-Key Lock(临键锁)：它是记录锁和间隙锁的结合，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。next-key 锁是InnoDB默认的。是一个左开右闭的规则

IS锁：意向共享锁、Intention Shared Lock。当事务准备在某条记录上加S(读)锁时，需要先在表级别加一个IS锁。

IX锁：意向排它锁、Intention Exclusive Lock。当事务准备在某条记录上加X(写)锁时，需要先在表级别加一个IX锁。

「面试官：」那这个东西是怎么实现的？

t(id PK, name KEY, sex, flag);

表中有四条记录：1, zhazhahui, m, A

3, nezha, m, A

5, lisi, m, A

9, wangwu, f, B记录锁

select * from t where id=1 for update;锁定 id =1的记录

间隙锁select * from t where id > 3 and id < 9 ;

锁定(3,5]，(5，9)范围的值，因为当前访问3到9的范围记录，就需要锁定表里面已经存在的数据来解决幻读和不可重复读的问题临建锁select * from t where id >=9 ;

会锁定 [9,+∞) 。查询会先选中 9 号记录，所以锁定范围就以9开始到正无穷数据。

「面试官：」那意向排它、共享锁呢？是怎么个内容

「吒吒辉：」意向排它锁和意向共享锁，是针对当前SQL请求访问数据行时，会提前进行申请访问，如果最终行锁未命中就会退化为该类型的表锁。

「面试官：」那有这个意向排它锁有什么好处呢？

「吒吒辉：」可提前做预判，每次尝试获取行锁之前会检查是否有表锁，如果存在就不会继续申请行锁，从而减少锁的开销。从而整个表就退化为表锁。

「面试官：」那你动手给我演示下每个场景

嗯。。。(瞳孔放大2倍)我这不说的很明白吗？

难道故意和作对，这是干嘛啊。欺负人嘛不是

只见那面试官忽然翘起来二郎腿，还有节拍的抖动着腿，看向我。一看就是抖音整多了哎，没办法官大以及压死人。打碎了牙齿自己咽。你给我看细细看好了，最好眼睛都别眨

「吒吒辉：」因为锁就是解决事务并发的问题，所以记录锁就不演示了，直接游荡在间隙和临建锁里面。

建立语句：CREATE TABLE `t1` (

`id` int(10) NOT NULL AUTO_INCREMENT,

`name` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,

`age` tinyint(3) unsigned DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

表数据：

间隙锁：关闭 MySQL 默认的事务自动提交机制。关闭前：微信搜索【莲花童子哪吒】关闭后：微信搜索【莲花童子哪吒】

加锁：微信搜索【莲花童子哪吒】

直接插入 >8 的数据就阻塞，都会上锁。为的就解决插入新数据而导致幻读。微信搜索【莲花童子哪吒】

【「啊！幻读不知道呀」。下篇文章给大家安排上】微信搜索【莲花童子哪吒】

「面试官：」你这条件不是>=8吗？那等于8呢？被吃辣？

「吒吒辉：」别着急嘛，这不还没说完吗。为什么不指定8呢？

因为「>=8」的条件会从「间隙锁升级为临建锁」，因为你条件里面包含了 8 这个真实存在的数据。所以会把它锁起来。如下：微信搜索【莲花童子哪吒】

所以，最终的行锁会和SQL语句的条件触发有关系，一旦范围查询包含了数据库里面真实存在数据，就会升级为临建锁。不要问我为什么？看前面的定义「面试官独白」：这小伙多少看来还有有点货，不错。此刻面试官露出一丝笑容。殊不知他内心又开酝酿起了新的想法。就等我入瓮

「面试官：」那什么场景下行锁不会生效呢？锁锁定的又是什么？此刻，我呆了，这都什么跟什么啊。不带这么玩的吧。天杀的，净使坏微信搜索【莲花童子哪吒】

锁的触发机制

「吒吒辉：」innodb的行锁是根据索引触发，如果没有相关的索引，那行锁将会退化成表锁(即锁定整个表里的行)。而「锁」锁定的是「索引」即索引树里面的数据库字段的值。id为主键索引字段。微信搜索【莲花童子哪吒】给 age 字段上锁微信搜索【莲花童子哪吒】age 字段没索引，退化成表锁。直接查询将失败。

有索引，用索引字段查询可得数据，其余字段查询将失败。因为获取不到行锁，只能等待。而锁定的是索引，故此其它用其它索引值查询能拿查询数据索引字段上锁微信搜索【莲花童子哪吒】索引当前字段锁定，用其余索引字段可查询微信搜索【莲花童子哪吒】不是索引字段都差不到。

「面试官：」你前面说到的锁可以解决事务并发，然而MVCC也是用于解决并发，那干嘛还用锁来呢？你给说说

「吒吒辉：」通过MVCC可以解决脏读、不可重复读、幻读这些读一致性问题，但实际上这只是解决了普通select语句的数据读取问题。

事务利用MVCC进行的读取操作称之为快照读，所有普通的SELECT语句在READ COMMITTED、REPEATABLE READ隔离级别下都算是快照读。

除了快照读之外，还有一种是「锁定读」，即在读取的时候给记录加锁，在「锁定读」的情况下依然要解决脏读、不可重复读、幻读的问题。

比如：如果 1 4 7 9 的数据。如果条件为 where > 4 的，那如果不锁定到 (4，7] (7,9],(9，+∞)。那势必就会早幻读，不可重复读的问题。ps:不重复读？脏读是如何产生的？

死锁

「面试官：」那你说下数据库的死锁是个什么情况？

「吒吒辉：」死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。

当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。

一般可通过死锁检测和死锁超时机制来解决该问题。

死锁检查：

像InnoDB存储引擎，就能检测到死锁的循环依赖，并立即返回一个错误。否则死锁会导致出现非常慢的查询。通过参数 innodb_deadlock_detect 设置为on，来开启。

超时机制：

就是当查询的时间达到锁等待超时的设定后放弃锁请求。InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚(这是相对比较简单的死锁回滚算法)。

可通过配置参数 innodb_lock_wait_timeout 用来设置超时时间。如果有些用户使用哪种大事务，就设置「锁超时时间大于事务执行时间」。

但这种情况下死锁超时检查的发现时间是无法接受的。

「面试官：」那你说说InnoDB和MyisAM是如何发现死锁的？

「吒吒辉：」innodb

数据库会把事务单元锁维持的锁和它所等待的锁都记录下来，Innodb提供了wait-for graph算法来主动进行死锁检测，每当加锁请求无法立即满足需要进入等待时，wait-for graph算法都会被触发。当数据库检测到两个事务不同方向地给同一个资源加锁(产生循序)，它就认为发生了死锁，触发wait-for graph算法。

比如：事务1给A加锁，事务2给B加锁，同时事务1给B加锁(等待)，事务2给A加锁就发生了死锁。那么死锁解决办法就是终止一边事务的执行即可，这种效率一般来说是最高的，也是主流数据库采用的办法。

Innodb目前处理死锁的方法就是将持有最少行级排他锁的事务进行回滚。这是相对比较简单的死锁回滚方式。死锁发生以后，只有部分或者完全回滚其中一个事务，才能打破死锁。

对于事务型的系统，这是无法避免的，所以应用程序在设计必须考虑如何处理死锁。大多数情况下只需要重新执行因死锁回滚的事务即可。MyisAM

MyisAM自身只支持表级锁，故加锁后一次性获取的。所以资源上不会出现多个事务之间互相需要对方释放锁之后再来进行处理。故不会有死锁

「面试官：」 wait-for graph 算法怎么理解？

「吒吒辉：」如下所示，四辆车就是死锁

它们相互等待对方的资源，而且形成环路！每辆车可看为一个节点，当节点1需要等待节点2的资源时，就生成一条有向边指向节点2，最后形成一个有向图。我们只要检测这个有向图是否出现环路即可，出现环路就是死锁！这就是wait-for graph算法。

Innodb将各个事务看为一个个节点，资源就是各个事务占用的锁，当事务1需要等待事务2的锁时，就生成一条有向边从1指向2，最后行成一个有向图。

「面试官：」既然死锁无法避免，那如何减少发生呢？

对应用程序进行调整/修改。某些情况下，你可以通过把大事务分解成多个小事务，使得锁能够更快被释放，从而极大程度地降低死锁发生的频率。在其他情况下，死锁的发生是因为两个事务采用不同的顺序操作了一个或多个表的相同的数据集。需要改成以相同顺序读写这些数据集，换言之，就是对这些数据集的访问采用串行化方式。这样在并发事务时，就让死锁变成了锁等待。

修改表的schema，例如：删除外键约束来分离两张表，或者添加索引来减少扫描和锁定的行。

如果发生了间隙锁，你可以把会话或者事务的事务隔离级别更改为RC(read committed)级别来避免，可以避免掉很多因为gap锁造成的死锁，但此时需要把binlog_format设置成row或者mixed格式。

为表添加合理的索引，不走索引将会为表的每一行记录添加上锁(等同表锁)，死锁的概率大大增大。

为了在单个InnoDB 表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖(行)使用SELECT ... FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。

通过SELECT ... LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。因进行获锁读取在修改

这时，只见对面所坐面试官，捋了捋那没有毛发的下巴，故作深思熟虑，像是在端详这什么。难道难道是让我通过了吗？

此刻内心犹如小鹿乱撞，呐喊到我要干它二量。真的是不容易。就在此时，他起身而立，那白色T桖衫包裹着那甩大肚子，犹如波浪上下翻滚。一看就是没少在酒桌上撸肉。微信搜索【莲花童子哪吒】