2023-03-14 08:59 门头沟学院 Java 发布于广东

关注

一天吃透MySQL面试八股文

本文已经收录到Github仓库，该仓库包含计算机基础、Java基础、多线程、JVM、数据库、Redis、Spring、Mybatis、SpringMVC、SpringBoot、分布式、微服务、设计模式、架构、校招社招分享等核心知识点，欢迎star~

****************************************************

为什么需要加锁

如果有多个并发请求存取数据，在数据就可能会产生多个事务同时操作同一行数据。如果并发操作不加控制，不加锁的话，就可能写入了不正确的数据，或者导致读取了不正确的数据，破坏了数据的一致性。因此需要考虑加锁。

表级锁和行级锁有什么区别？

MyISAM 仅仅支持表级锁，一锁就锁整张表，这在并发写的情况下性非常差。

InnoDB 不光支持表级锁，还支持行级锁，默认为行级锁。行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。

表级锁和行级锁对比 ：

表级锁： MySQL 中锁定粒度最大的一种锁，是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。
行级锁： MySQL 中锁定粒度最小的一种锁，是针对索引字段加的锁，只针对当前操作的记录进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

共享锁和排他锁有什么区别？

不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类：

共享锁（S 锁） ：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
排他锁（X 锁） ：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。

排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。

S 锁	不冲突	冲突
X 锁	冲突	冲突

由于 MVCC 的存在，对于一般的 SELECT 语句，InnoDB 不会加任何锁。不过，你可以通过以下语句显式加共享锁或排他锁。

# 共享锁
SELECT ... LOCK IN SHARE MODE;
# 排他锁
SELECT ... FOR UPDATE;

意向锁有什么作用？

如果需要用到表锁的话，如何判断表中的记录没有行锁呢？一行一行遍历肯定是不行，性能太差。我们需要用到一个叫做意向锁的东东来快速判断是否可以对某个表使用表锁。

意向锁是表级锁，共有两种：

意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁是有数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

意向锁之间是互相兼容的。

IS 锁	兼容	兼容
IX 锁	兼容	兼容

意向锁和共享锁和排它锁互斥（这里指的是表级别的共享锁和排他锁，意向锁不会与行级的共享锁和排他锁互斥）。

S 锁	兼容	互斥
X 锁	互斥	互斥

InnoDB 有哪几类行锁？

按锁粒度分类，有行级锁、表级锁和页级锁。

行级锁是mysql中锁定粒度最细的一种锁。表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突，其加锁粒度最小，但加锁的开销也最大。行级锁的类型主要有三类： Record Lock，记录锁，也就是仅仅把一条记录锁上；Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。
表级锁是mysql中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分mysql引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。
页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折衷的页级锁，一次锁定相邻的一组记录。

按锁级别分类，有共享锁、排他锁和意向锁。

共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。
排他锁又称写锁、独占锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。
意向锁是表级锁，其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型。InnoDB 中的两个表锁：

意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁；

意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

意向锁是 InnoDB 自动加的，不需要用户干预。

对于INSERT、UPDATE和DELETE，InnoDB 会自动给涉及的数据加排他锁；对于一般的SELECT语句，InnoDB 不会加任何锁，事务可以通过以下语句显式加共享锁或排他锁。

共享锁：SELECT … LOCK IN SHARE MODE;

排他锁：SELECT … FOR UPDATE;

什么是死锁？如何防止死锁？

什么是死锁？

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

如何防止死锁？

尽量约定固定的顺序访问表，因为交叉访问更容易造成事务等待回路。
尽量避免大事务，建议拆成多个小事务。因为大事务占用的锁资源越多，越容易出现死锁。
降低数据库隔离级别，比如RR降低为RC，因为RR隔离级别，存在GAP锁，死锁概率大很多。
死锁与索引是密不可分的，合理优化你的索引，死锁概率降低。
如果业务处理不好可以用分布式事务锁或者使用乐观锁

如何处理死锁？

通过innodblockwait_timeout来设置超时时间，一直等待直到超时。

发起死锁检测，发现死锁之后，主动回滚死锁中的事务，不需要其他事务继续。

什么是全局锁？它的应用场景有哪些？

全局锁就是对整个数据库实例加锁，它的典型使用场景就是做全库逻辑备份，这个命令可以使用整个库处于只读状态，使用该命令之后，数据更新语句，数据定义语句，更新类事务的提交语句等操作都会被阻塞。

使用全局锁会导致的问题？

如果在主库备份，在备份期间不能更新，业务停止，所以更新业务会处于等待状态。

如果在从库备份，在备份期间不能执行主库同步的binlog，导致主从延迟。

乐观锁和悲观锁是什么？

数据库中的并发控制是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观锁和悲观锁是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，会对操作的数据进行加锁，直到提交事务，才会释放锁，其他事务才能进行修改。实现方式：使用数据库中的锁机制。
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否数据是否被修改过。给表增加version字段，在修改提交之前检查version与原来取到的version值是否相等，若相等，表示数据没有被修改，可以更新，否则，数据为脏数据，不能更新。实现方式：乐观锁一般使用版本号机制或CAS算法实现。

select for update加的是表锁还是行锁

需要情况讨论：RR和RC隔离级别，还有查询条件（唯一索引、主键、一般索引、无索引）

在RC隔离级别下

如果查询条件是唯一索引，会加IX意向排他锁（表级别的锁，不影响插入）、两把X排他锁（行锁，分别对应唯一索引，主键索引）
如果查询条件是主键，会加IX意向排他锁（表级别的锁，不影响插入）、一把对应主键的X排他锁（行锁，会锁住主键索引那一行）。
如果查询条件是普通索引，如果查询命中记录，会加IX意向排他锁（表锁）、两把X排他锁（行锁，分别对应普通索引的X锁，对应主键的X锁）;如果没有命中数据库表的记录，只加了一把IX意向排他锁（表锁，不影响插入）
如果查询条件是无索引，会加两把锁，IX意向排他锁（表锁）、一把X排他锁（行锁，对应主键的X锁）。

查询条件是无索引，为什么不锁表呢？ MySQL会走聚簇(主键)索引进行全表扫描过滤。每条记录都会加上X锁。但是，为了效率考虑，MySQL在这方面进行了改进，在扫描过程中，若记录不满足过滤条件，会进行解锁操作。同时优化违背了2PL原则。

在RR隔离级别

如果查询条件是唯一索引，命中数据库表记录时，一共会加三把锁：一把IX意向排他锁（表锁，不影响插入），一把对应主键的X排他锁（行锁），一把对应唯一索引的X排他锁（行锁）。
如果查询条件是主键，会加IX意向排他锁（表级别的锁，不影响插入）、一把对应主键的X排他锁（行锁，会锁住主键索引那一行）。
如果查询条件是普通索引，命中查询记录的话，除了会加X锁（行锁），IX锁（表锁，不影响插入），还会加Gap 锁（间隙锁，会影响插入）。
如果查询条件是无索引，会加一个IX锁（表锁，不影响插入），每一行实际记录行的X锁，还有对应于supremum pseudo-record的虚拟全表行锁。这种场景，通俗点讲，其实就是锁表了。