对线面试官 - MySQL 隔离级别、锁机制

2023-08-31
辽宁
本文字数：1919 字
阅读完需：约 6 分钟

面试官：MySQL 如何实现的 Read Repeatable 的？

派大星：MySQL 是通过 MVCC 机制来实现的，就是多版本并发控制，multi-version concurrency control。innodb 存储引擎，会在每行数据的最后加两个隐藏列，一个保存行的创建事件，一个保存行的删除事件，但是这儿存放的不是时间，而是事务 id，事务 id 是 mysql 自己维护的自增的，全局唯一。事务 id，在 mysql 内部是全局唯一递增的，事务 id=1，事务 id=2，事务 id=3 在一个事务内查询的时候，mysql 只会查询创建时间的事务 id 小于等于当前事务 id 的行，这样可以确保这个行是在当前事务中创建，或者是之前创建的；同时一个行的删除时间的事务 id 要么没有定义（就是没删除），要么是必当前事务 id 大（在事务开启之后才被删除）；满足这两个条件的数据都会被查出来。

面试官：那么如果某个事务执行期间，别的事务更新了一条数据呢？

派大星：这个很关键的一个实现，其实就是在 innodb 中，是插入了一行记录，然后将新插入的记录的创建时间设置为新的事务的 id，同时将这条记录之前的那个版本的删除时间设置成刚刚的新的事务的 id。现在 get 到这个点了吧？这样的话，你的这个事务其实对某行记录的查询，始终都是查找的之前的那个快照，因为之前的那个快照的创建时间小于等于自己事务 id，然后删除事件的事务 id 比自己事务 id 大，所以这个事务运行期间，会一直读取到这条数据的同一个版本。创建事务id <= 当前事务id< 删除事务idTips：

基于 undo log 多版本链条以及 ReadView 机制实现的多事务并发执行的 RC 隔离级别、RR 隔离级别，就是数据库的 MVCC 多版本并发控制机制。
RR 关键点在于每次查询都生成新的 ReadView
RC 不会生成新的 ReadView

面试官：不错，那我们继续聊一聊 MySQL 锁类型有哪些吧？

派大星：表锁，行锁，和页锁(几乎很少使用)。

MyIsam 使用的就是表锁，在默认情况下执行查询的时候会加个表共享锁，也就是表读锁。这个时候其他请求只能查询数据不能修改数据。MyIsam 写的时候也会加个表独占锁也就是表写锁，其它请求不能读也不能写。
行锁有两种：分别是共享锁(s)、和排它锁(x)。InnoDB 常用的就是行锁，(当然它也有表锁)。

InnoDB 在insert 、update、delete以上操作都会加行级排它锁。select则不会加锁，因为 InnoDB 默认实现了可重复读，也就是 mvcc 机制。所以多个事务随便读一个数据，一般不会有什么冲突。

面试官：OK，如何手动添加共享锁、排它锁。简单说说？

派大星：ok。简单代码如下：

共享锁

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

复制代码

排它锁(悲观锁)

select * from table where id = 1 for update;

复制代码

面试官：不错，了解悲观锁和乐观锁是什么吗，具体的使用场景是什么？

派大星：

MySQL 中的悲观锁指的就是select * from table where id = 1 for update。简单理解就是它担心自己拿不到锁，所以会先锁定，不允许其他请求再获得锁。同时不能加共享锁也不能加排它锁。
乐观锁：相对来说就比较简单。就是它觉得不会有其他请求与其争抢锁。所以它不需要提前获得锁。一般都是通过版本号来确定select id, name, version from table where id =1

具体使用场景如下：悲观锁：

长时间操作或复杂的业务逻辑，为了避免其他事务修改数据，可以使用悲观锁来确保一致性。
并发读写冲突频繁发生的地方，使用悲观锁可以有效减少冲突。
适用于数据更新频率高，操作需要较长时间的情况。

乐观锁：

数据更新操作频率相对较低，冲突发生的概率较小。
适用于数据读取远远多于数据更新的场景，减少锁的开销。
希望通过版本号等机制来避免并发冲突，同时在冲突发生时能够进行特定处理。

面试官：嗯，那你了解 MySQL 死锁原理嘛？以及如何定位

派大星：首先产生死锁的原因有很多种，简单说一种，假设

事务 A 持有 id=1 的锁：select * from table where id =1 for update
事务 B 持有 id=2 的锁：select * from table where id =2 for update
事务 A 此时又持有 id=2 的锁：select * from table where id =2 for update
事务 B 此时又持有 id=1 的锁：select * from table where id =1 for update

这是就会产生死锁。

定位死锁：

查看死锁日志： MySQL 会将死锁信息记录在错误日志中，你可以在日志中查找 Deadlock 信息，包括涉及的事务 ID、锁定的资源等。
使用 SHOW ENGINE INNODB STATUS 命令：执行这个命令会返回一份详细的 InnoDB 引擎状态信息，其中会包含关于死锁的信息。
使用信息模式（Information Schema）：可以查询 information_schema.INNODB_LOCKS 和 information_schema.INNODB_LOCK_WAITS 视图来了解锁和等待情况。
应用层监控：一些应用层监控工具可以帮助你实时监测数据库的死锁情况。

面试官：能简单说说为什么 MySQL 的默认隔离级别是可重复读吗？

派大星：有点累了，下次吧。