一文搞懂后端面试之数据库MySQL的各种锁以及锁优化【中间件 | 数据库 | MySQL | 锁机制】

LightOf_

于 2024-08-03 20:18:56 发布

阅读量774

点赞数 36

分类专栏： MySQL 后端面试题文章标签：数据库面试中间件 mysql 数据结构 sql 架构

本文链接：https://blog.csdn.net/lightofnight/article/details/140890816

版权

后端面试题同时被 2 个专栏收录

25 篇文章 3 订阅

订阅专栏

MySQL

9 篇文章 0 订阅

订阅专栏

锁与索引

在MySQL的InnoDB引擎里，锁是借助索引来实现的，加锁锁住的其实是索引项，更加具体的说，是锁住了叶子节点。
在这里插入图片描述
引出的问题：

一个表有很多索引，锁的是哪个索引呢？
答案是 查询最终使用的索引
万一查询没有使用任何索引呢？
那么就会锁住整个表，此时退化为表锁。
如果查询条件的值不存在，怎么锁？比如SELECT * FROM your_tab WHERE id = 15 FOR UPDATE

InnoDB引擎会利用最接近15的相邻的两个节点，构造一个临键锁
如果这个时候别的事务想要插入一个id=15的记录，就不会成功
那么范围查询呢？
利用索引上的数据，构建一个恰好能够装下这个范围的临键锁，例如：SELECT * FROM your_tab WHERE id > 33 FOR UPDATE，InnoDB引擎会构造一个(33,supremum]的临键锁，锁住整个范围。supremum是MySQL认为的一个虚拟的最大值

通过上述可以得出一个结论：锁和索引密切相关

释放锁时机

学习锁的时候容易有一个误区：认为锁是在语句执行完毕之后就立刻释放掉。
事实上，锁是在整个事务结束之后才释放的。也就是说，当一个事务内部给数据加上锁之后，只有执行Rollback或Commit的时候，锁才被释放掉。
在这里插入图片描述

乐观锁和悲观锁

是一种逻辑概念，是并发控制中常用的两种锁机制。

乐观锁是要修改数据的时候，才检测数据释放已经被别人修改过
悲观锁是在初始时刻就直接加锁保护好临界资源

乐观锁在数据库里通常利用CAS的思路进行更新操作，一般的使用形态如下：

SELECT * FROM your_tab WHERE id = 1; // 在这里拿到了 a = 1
// 一大堆的业务操作
UPDATE your_tab SET a = 3, b = 4 WHERE id = 1 AND a =1

在上述的语句里，预期是数据库里a的值是1才会进行更新，如果此时数据库中的值已经被修改了，这个UPDATE语句就会失败。业务方通过判断受影响的行数是否为0，来判断是否更新成功
在这里插入图片描述
悲观锁是指写入数据时直接加锁，还是以上面的语句为例，就是从最开始的SELECT语句就直接加上了锁，在加上锁以后可以直接更新了。

在使用悲观锁和乐观锁的时候，需要考虑数据一致性和并发性的问题。乐观锁适用于读多写少的场景，互联网大多数应用都是这一种。悲观锁适用于写多读少的场景，比如金融领域里对金额的操作就是以写为主。
相比较下，乐观锁的性能要比悲观锁好很多

行锁和表锁

行锁和表锁是根据锁的范围来划分的，一般来说，行锁指的是锁住行，可能是一行或多行；表锁则是直接把整个表都锁住。
在MySQL里，InnoDB同时支持行锁和表锁，但是行锁是借助索引来实现的，前面也提到过了，如果查询没有命中任何的索引，那么InnoDB引擎是用不了行锁的，只能用表锁；当然，如果用的是MyISAM引擎，就只能使用表锁，因为这些引擎不支持行锁。

共享锁与排它锁

共享锁和排它锁是在互斥的角度上看待锁的。

共享锁是指一个线程加锁之后，其他线程还是可以继续加同类型的锁
排它锁是指一个线上加锁之后，其他线上就不能再加锁了

概念很接近写锁和读锁，因为读锁本身就是共享的，而写锁就是排它的。

意向锁

相当于一个信号，告诉别人我要加锁了，所以意向锁并不是一个真正物理意义上的锁。
意向锁和共享锁、排它锁结合，就有了意向共享锁和意向排它锁。

意向共享锁：希望获得一个共享锁
意向排它锁：希望获得一个排它锁

意向锁的意向重点就是想要拿到这个锁，但是最终能否拿到这个锁，是不确定的。

在MySQL里，使用意向锁的场景是在增删改查的时候，对表结构定义加一个意向共享锁，防止在查询的时候有人修改表结构；在修改表结构的时候，加一个意向排它锁，这也就是修改表结构的时候直接阻塞掉所有增删改查语句的原因。使用意向锁可以提高数据库的并发性能避免死锁问题。

记录锁

记录锁、间隙锁和临键锁是面试中最难理解的三个概念
记录锁是指锁住了特定的某一条记录的锁，例如SELECT * FROM your_tab WHERE id = 31 FOR UPDATE，在使用了主键作为查询条件，并且是相等条件下，将只命中一条记录，这一条记录就会被加上记录锁。但是如果查询条件里没有命中任何记录，那么就不会使用记录锁，而是使用间隙锁。
在这里插入图片描述
如果使用唯一索引作为条件，比如user表里有一个email列是唯一索引，那么这条查询语句也是使用记录锁。类似，如果email='your_email' 这条记录不存在，那么会变成一个间隙锁。

SELECT * FROM your_tab WHERE email='your_email' FOR UPDATE

举个例子，如果数据库只有id为(1,4,7)的三条记录，也就是id=3这个条件没有命中任何数据，那么这条语句会在(1,4)这里加上间隙锁，所以，在生产环境里遇到了未命中索引的情况，对性能影响很大

MySQL里本身是加临键锁的，但是临键锁本身是由间隙锁和记录锁合并组成的，所以这里先用间隙锁描述

间隙锁

锁住了某一段记录的锁，直观的说就是锁住了一个范围的记录，比如在查询的时候使用了< <= BETWEEN 之类的范围查询条件，就会使用间隙锁

SELECT * FROM your_tab WHERE id BETWEEN 50 AND 100 FOR UPDATE

间隙锁会锁住(50,100)之间的数据，而50和100本身会被记录锁锁住，类似的<=这种查询，也可以认为=的那个值会被记录锁锁住。
如果表里没有50，数据库会一直向左，找到第一个存在的数据，比如40；同理，如果表里没有100，那么数据库就会一直向右，找到第一个存在的数据，比如120。此时，如果有人想要插入一个主键为70的行，是无法插入的，需要等到这个SELECT语句释放掉间隙锁。

间隙锁我们一般说两边都是开的，即端点是没有被间隙锁锁住的。记录锁和记录锁是排它的，但是间隙锁和间隙锁不是排它的，也就是说两个间隙锁之间即使重叠了，也还是可以加锁成功的
在这里插入图片描述

临键锁

临键锁是一种很独特的锁，直观上可以看作是一个记录锁和间隙锁的组合，也就是说临键锁不仅仅是会用记录锁锁住命中的记录，也会用间隙锁锁住记录之间的空隙。
临键锁和数据库隔离级别的联系最为紧密，可以解决在可重复读隔离级别之下的幻读问题。
间隙锁是左开右开，临键锁是左开右闭 ，如果id只有(1,4,7)三条记录，那么临键锁就把(1,4]锁住。

(幻读就是同一事务里面，同一个sql查询查出来的记录行数不一样。为什么会不一样？因为有别的事务在你执行sql的时候进行了插入，插入到了你的查询条件范围内了，导致你上一次查还好好的，下一次查就莫名奇妙多出来记录了)

总结

遇事不决临键锁：可以认为，全部都是加临键锁的，除了下面两个子句提到的例外情况
右边缺省间隙锁：例如你的值只有(1,4,7)三个，但是你查询的条件是where id < 5，那么加的是间隙锁，因为7本身就不在查询范围里。
等值查询记录锁：针对的是主键和唯一索引，不适用于普通索引

在这里插入图片描述

面试准备

公司出现过的死锁，包括排查过程、解决方案
其他锁使用不当的场景，比如因为锁使用不当造成的一些性能问题
收集至少一个使用乐观锁的场景，并看看相关的SQL是怎么写的
收集使用悲观锁的场景，尝试使用乐观锁来优化

这些案例非常重要，如果自己没有亲自遇到的话，也要找同事问清楚，或是参考网上的案例来实际看看锁的应用。

类似索引，面试官可能直接写一个SQL语句，问你可能加什么锁

在主键或唯一索引上使用等值查询，例如where email = 'abc@qq.com' 区分记录存在与不存在的情况（存在是记录锁不存在是间隙锁未命中索引是表锁）
在主键或唯一索引上使用范围查询，例如where email >= 'abc@qq.com' （临键锁大于部分是间隙锁等于部分是记录锁）
在普通索引上使用等值查询（临键锁）
在普通索引上使用范围查询（临键锁）
执行查询，但是查询不会使用任何索引（表锁）

不管怎么回答，都要强调间隙锁和临键锁是在可重复读的隔离级别下才有效果

聊到下面的话题，也可以引导到锁机制上

索引：MySQL的InnoDB是借助索引来实现行锁的
性能问题：锁使用不当引起的性能问题
乐观锁：比如原子操作的CAS操作，聊一聊在MySQL层面上怎么利用类似CAS的操作实现乐观锁

在 MySQL 层面上，要实现类似 CAS 操作来实现乐观锁，通常可以使用以下方式：

使用版本号或时间戳：在数据表中添加一个版本号或时间戳字段，每次更新数据时将版本号加一或更新时间戳。在进行更新操作时，需要检查更新前后的版本号或时间戳是否一致，如果一致则进行更新，否则认为数据已被其他事务修改。
使用乐观锁的存储过程：通过存储过程实现乐观锁的逻辑，可以在存储过程中进行数据检查和更新操作，以确保并发更新时的数据一致性。

这些方法可以帮助在 MySQL 层面上实现类似 CAS 操作的乐观锁机制，从而避免并发更新时出现数据不一致的问题。

语言相关的锁：比如Go的mutex
死锁：公司的数据库死锁案例

基本面试

知道MySQL的锁机制吗？
了解MySQL的锁吗？

类似的问题可以综合回答，介绍MySQL的五花八门的锁。

MySQL的锁机制非常丰富，以InnoDB引擎为例。首先，从锁的范围看，可以分为行锁和表锁；其次，从排它性来看，可以分为排它锁和共享锁；还有意向锁，结合排它性，可以分为排它意向锁和共享意向锁；还有三个重要的锁概念，记录锁、间隙锁和临键锁。记录锁，是指锁住某条记录；间隙锁，是锁住两条记录之间的位置；临键锁可以看成记录锁和间隙锁的组合情况。
还有一种分类是乐观锁和悲观锁，在数据库里使用乐观锁的话，本质是应用层面上的CAS操作。

先从大方向上解释清楚锁的根本特性，而不是深入去解释各种锁。根本特性是：锁是和索引、隔离级别密切相关的。

在MySQL的InnoDB的引擎里，锁和索引、隔离级别都是有密切关系的。在InnoDB引擎里面，锁是依赖于索引来实现的。或者说，锁都是加在索引项上的，如果一个查询用到了索引，就会用行锁；如果没用到任何索引，就会用表锁，此外，在MySQL里面，间隙锁和临键锁都是只工作在可重复读这个隔离级别下的。

后续的追问：

某一种锁的具体含义
某一种锁的适用场景，注意意向锁
怎么在数据库使用乐观锁，或者用乐观锁解决过什么问题
有没有优化过锁，或是解决过死锁
详细介绍记录锁、间隙锁和临键锁，也有可能问MySQL在可重复读的隔离级别下会不会有幻读问题？（在MySQL的可重复读隔离级别下，不会出现幻读问题。这是因为MySQL使用了临键锁来解决幻读问题，保证在可重复读隔离级别下事务执行过程中不会出现插入新数据导致的幻读情况。）

还是需要一些实际的锁优化案例来证明能力

亮点方案1：加索引

先说一个最简单的锁优化方案，MySQL的锁是依赖索引机制来实现的，如果查询没有使用索引，就会使用表锁，那么显然最简单的方案就是给这种查询创建一个索引，避免使用表锁。关键词是缺索引

早期发现我们的业务有一个神奇的性能问题，就是响应时间偶尔会突然延长，后来经过排查，确认响应时间是因为数据库查询变慢引起的。但是那些变长的查询，SQL完全没有问题，而且用EXPLAIN去分析，都很正常，也走了索引。
直到后面我们去排查业务代码的提交记录，才发现新加的功能会执行一个SQL，但是这个SQL本身不会命中任何索引，于是数据库就会使用表锁，偏偏这个SQL本身没有命中索引，又很慢，导致表锁一直得不到释放。结果其他正常的SQL反而被他拖累了。最终我们重新优化了这个使用表锁的SQL，让它走了一个索引，就解决了这个问题。

这个方案还是比较简单，还有两个稍微复杂的方案：

亮点方案2：临键锁引发的死锁

在一个业务中，有一个场景是先从数据库中查询数据并锁住。如果这个数据不存在，那么就需要执行一段逻辑，计算出一个数据，然后插入。如果已经有数据了，就把原始数据取出来，再利用这个数据执行一段逻辑，计算出一个结果，执行更新。
在这里插入图片描述
因为两端运算逻辑不同，所以不能简单地使用INSERT ON DUPLICATE的语句来取代。

以没有数据的逻辑来看，在计算之后插入新数据，伪代码如下：

BEGIN;
SELECT * FROM biz WHERE id = ? FOR UPDATE
// 中间有很多业务操作
INSERT INTO biz(id, data) VALUE(?, ?);
COMMIT;

事实上，这个地方会引起死锁。
假如现在数据库中ID最大的值是78，那么如果两个业务进来，同时执行这个逻辑，一个准备插入id=79的数据，一个准备插入id=80的数据，执行时序如下图
在这里插入图片描述

[40001][1213] Deadlock found when trying to get lock; try restarting transaction

造成死锁的原因是：在线程1执行SELECT FOR UPDATE的时候，因为id是79的数据不存在，所以数据库会产生一个(78,supremum]的临键锁；类似的，线程2也会产生一个(78,supremum]的临键锁。当线程1想要执行插入的时候，他想要获得79的行锁；当线程2想要执行插入的时候，它想要获得id=80的行锁，这个时候就会出现死锁，因为线程1和线程2同时还在等着对方释放掉持有的间隙锁。
在这里插入图片描述
从理论上来说，解决方案有三种：

不管有没有数据，先插入一个默认的数据。如果没有数据，那么会插入成功；如果有数据，会出现主键冲突或唯一索引冲突，插入失败。在插入成功的时候，执行以前数据不存在的逻辑，因为此时数据库里有数据，所以不会使用间隙锁，而是使用行锁，从而规避了死锁问题。
调整数据库的隔离级别，降低为已提交读就没有间隙锁了。可以进一步把话题引申到MVCC中。
放弃悲观锁，使用乐观锁。这也是亮点方案。

可以通过一个案例说明，关键词是临键锁。

早期优化过一个死锁问题，是临键锁引起的，业务逻辑很简单，先用 SELECT FOR UPDATE 查询数据。如果查询到了数据，那么就执行一段业务逻辑，然后更新结果；如果没有查询到，那么就执行另外一段业务逻辑，然后插入计算结果。
那么如果 SELECT FOR UPDATE 查找的数据不存在，那么数据库会使用一个临键锁。此时，如果有两个线程加了临键锁，然后又希望插入计算结果，那么就会造成死锁。
我这个优化也很简单，就是上来先不管三七二十一，直接插入数据。如果插入成功，那么就执行没有数据的逻辑，此时不会再持有临键锁，而是持有了行锁。如果插入不成功，那么就执行有数据的业务逻辑。
此外，还有两个思路。一个是修改数据库的隔离级别为 RC，那么自然不存在临键锁了，但是这个修改影响太大，被 DBA 否决了。另外一个思路就是使用乐观锁，不过代码改起来要更加复杂，所以就没有使用。

后续可能会追问隔离级别的事情，或是问乐观锁的细节。

亮点方案3：弃用悲观锁

很多人为了省事会直接使用悲观锁，比如事务里存在SELECT ... FOR UPDATE的语句，而后面紧跟一个UPDATE语句

// 开启事务
Begin()
// 查询到已有的数据 SELECT * FROM xxx WHERE id = 1 FOR UPDATE
data := SelectForUpdate(id) 
newData := calculate(data) // 一大通计算

// 将新数据写回去数据库 UPDATE xxx SET data = newData WHERE id =1
Update(id, newData) 
Commit()

考虑这一类代码直接把事务给去掉，纯粹依赖CAS操作

for {
  // 查询到已有的数据 SELECT * FROM xxx WHERE id = 1
  data := Select(id) 
  newData := calculate(data) // 一大通计算

  // 将新数据写回去数据库 
  // UPDATE xxx SET data = newData WHERE id =1 AND data=oldData
  success := CAS(id, newData, data) 
  // 确实更新成功，代表在业务执行过程中没有人修改过这个 data。
  // 适合读多写少的情况
  if success {
    break;
  }
}

这里是直接用data来比较的，实践中也可能引入version列，或是update_time来确保数据没有发生更改。
可以聊到乐观锁的情况下，用这个案例。

在入职这家公司之后，曾经系统地清理过公司内部使用悲观锁的场景，改用乐观锁。正常的悲观锁都是使用了 SELECT FOR UPDATE 语句，查询到数据之后，进行一串计算，再将结果写回去。那么改造的方案很简单，查询的时候使用 SELECT 语句直接查询，然后进行计算。但是在写回去的时候，就要用到数据库的 CAS 操作，即 UPDATE 的时候要确认之前查询出来的结果并没有实际被修改过。
一般来说就是 UPDATE xxx SET data = newData WHERE id = 1 AND data = oldData。这种改造效果非常好，性能提升了 30%。当然，并不是所有的悲观锁场景都能清理，还有一部分实在没办法，只能是考虑别的手段了。

最后将话题引导到你准备的其他优化锁的案例上。面试思路总结