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

面试官：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

这时就会产生死锁。

定位死锁：

1. 查看死锁日志：MySQL会将死锁信息记录在错误日志中，你可以在日志中查找Deadlock信息，包括涉及的事务ID、锁定的资源等。

2. 使用SHOW ENGINE INNODB STATUS命令：执行这个命令会返回一份详细的InnoDB引擎状态信息，其中会包含关于死锁的信息。

3. 使用信息模式（Information Schema）：可以查询information_schema.INNODB_LOCKS和information_schema.INNODB_LOCK_WAITS视图来了解锁和等待情况。

4. 应用层监控：一些应用层监控工具可以帮助你实时监测数据库的死锁情况。

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

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