面试题011-数据库-MySQL(事物+锁)

面试题011-数据库-MySQL(事物+锁)

题目自测

• 1. 什么是事务？如何在MySQL中使用事务？
• 2.并发事务带来了哪些问题?
• 3. 不可重复读和幻读有什么区别？
• 4. MySQL的事物隔离级别有哪些？
• 5. MySQL事务的隔离级别是如何实现的？
• 6. InnoDB对MVCC的实现？
• 7. MySQL中的锁是什么？
• 8. 表级锁和行级锁有什么区别？
• 9. 哪些操作会加表级锁？哪些操作会加行级锁？
• 10. InnoDB 有哪几类行锁？
• 11. MySQL发生死锁了怎么办？

题目答案

1. 什么是事务？如何在MySQL中使用事务？

答：事务是指在数据库管理系统中，作为一个单一逻辑工作单元执行的一系列操作。这些操作要么全部完成，要么全部不完成。事务有4个基本特性，称为ACID特性。

• 原子性：事务中的所有操作要么全部成功，要么全部失败回滚。

• 一致性：事务执行前后，数据库都处于一致的状态。

• 隔离性：并发执行的事务不会相互干扰。

• 持久性：一旦事务提交，其对数据库的改变是永久性的。
  可以用以下命令来管理事务：

• 开始事务：使用关键字 START TRANSACTION 或 BEGIN 开始一个事务。

• 提交事务：使用关键字 COMMIT 提交一个事务，确保所有更改永久生效。

• 回滚事务：使用关键字 ROLLBACK 回滚一个事务，将所有更改撤销

  -- 开始事务
  START TRANSACTION;
  
  -- 执行一些SQL操作
  INSERT INTO accounts (account_number, balance) VALUES ('12345', 1000);
  UPDATE accounts SET balance = balance - 100 WHERE account_number = '12345';
  
  -- 如果所有操作成功，提交事务
  COMMIT;
  
  -- 如果发生错误，回滚事务
  ROLLBACK;
  

2. 并发事务带来了哪些问题?

答：并发事务在数据库系统中是指多个事务同时运行，并操作相同的数据来完成各自的任务。并发可以提高系统的性能和吞吐量，但同时也带来了一下问题：

• 脏读：事务读取了另一个事务尚未提交的修改数据。如果第二个事务回滚了，这些读取到的数据就是无效的。
• 修改丢失：当两个或更多的事务试图修改同一数据项时，后提交的事务可能会覆盖先提交的事务所做的更改，导致先提交事务的更新被丢失。
• 不可重复读：事务在两次读取同一行数据时，得到了不同的结果，这是由于另一个事务在两次读取之间提交了修改。
• 幻读：事务在两次查询期间，发现了其他事务插入或删除的新行，导致查询结果的行数不一致。

3. 不可重复读和幻读有什么区别？

答：不可重复度和幻读都是数据库中处理事务可能出现的两种问题。

• 不可重复读：关注的是单行数据的修改，同一行数据在一次事务中的两次读取间被修改。
  • 通常通过设置较高的隔离级别（如可重复读或可串行化）来避免。在这些隔离级别下，数据库会使用锁或多版本并发控制（MVCC）机制来保证同一事务中的查询能够得到相同的数据结果。
• 幻读：关注的是数据集的变化，一次事务中的两次查询得到的结果集行数不同，是由于其他事务插入或删除了符合条件的新数据。
  • 除了设置较高的隔离级别外，还可以通过增加范围锁来避免幻读。最高隔离级别SERIALIZABLE_READ可以保证不出现幻读的问题。

4. MySQL的事物隔离级别有哪些？

答：MySQL支持四种事务隔离级别，这些级别定义了数据可见性的规则，从而影响并发控制和事务的隔离程度。

• 读未提交(READ UNCOMMITTED)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• 读已提交(READ COMMITTED)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• 可重复读(REPEATABLE READ)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• 可串行化(SERIALIZABLE)：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

5. MySQL事务的隔离级别是如何实现的？

答：MySQL的事务隔离级别是通过InnoDB存储引擎的多版本并发控制（MVCC）和锁机制来实现的。

• 读未提交：MySQL允许读取未提交的数据，不需要使用任何额外的锁机制。
• 读已提交：通过快照读和行级共享锁来实现，防止脏读。
• 可重复读：通过多版本并发控制和间隙锁来实现，防止脏读和不可重复度。
• 可串行化：通过共享锁和排他锁来确保事务完全隔离。

6. InnoDB对MVCC的实现？

答：MVCC 是一种并发控制机制，用于在多个并发事务同时读写数据库时保持数据的一致性和隔离性。它是通过在每个数据行上维护多个版本的数据来实现的。当一个事务要对数据库中的数据进行修改时，MVCC 会为该事务创建一个数据快照，而不是直接修改实际的数据行。
在InnoDB中，MVCC的实现依赖于 隐藏字段、Read View、undo log。

1. 事务开始：当一个新事务开始时，InnoDB会为其分配一个唯一的事务ID，并获取当前的Read View（在可重复读隔离级别下，只在事务第一次SELECT时获取）。
2. 数据读取：事务在读取某个行记录时，InnoDB会根据Read View和行记录的隐藏字段（DB_TRX_ID、DB_ROLL_PTR等）来判断该记录是否对其可见。
   • 如果行记录的DB_TRX_ID小于Read View中的最小活跃事务ID（up_limit_id），则该记录对事务可见。
   • 如果行记录的DB_TRX_ID大于Read View中的最大活跃事务ID（low_limit_id），则该记录对事务不可见。
   • 如果DB_TRX_ID在up_limit_id和low_limit_id之间，则需要在Read View的活跃事务ID列表（trx_ids）中查找DB_TRX_ID。如果找到，则记录对事务不可见；如果未找到，则记录对事务可见。
3. 数据修改：当事务修改某个行记录时，InnoDB会为该记录创建一个新的版本，并更新DB_TRX_ID和DB_ROLL_PTR等隐藏字段。同时，将旧版本的信息记录到Undo日志中。
4. 事务提交或回滚：如果事务提交，则修改的数据版本成为当前版本；如果事务回滚，则通过Undo日志恢复到修改前的版本。

7. MySQL中的锁是什么？

答：在MySQL中，锁是一种用于控制并发访问数据库资源的机制。锁可以确保在多用户环境中数据的一致性和准确性，防止多个事务同时修改相同的数据，从而避免数据冲突和不一致的情况。MySQL中的锁主要在服务器层和存储引擎层实现，具体类型和行为取决于使用的存储引擎。

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

答：MyISAM存储引擎只支持表级锁。InnoDB存储引擎支持表级锁和行级锁，默认为行级锁。

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

9. 哪些操作会加表级锁？哪些操作会加行级锁？

答：在MySQL中，加锁的行为取决于多种因素，包括SQL语句的类型、使用的存储引擎、事务隔离级别以及是否显式地请求了某种类型的锁。

• 加表级锁：
  • DDL操作（数据定义语言操作）：ALTER TABLE、DROP TABLE、CREATE TABLE等。
  • MyISAM引擎的读写操作：SELECT加共享锁，INSERT、UPDATE、DELETE加排他锁。
  • 显示表锁：使用 LOCK TABLES 语句显示的加锁。
• 加行级锁：
  • InnoDB引擎的读写操作

10. InnoDB 有哪几类行锁？

答：InnoDB 行锁是通过对索引数据页上的记录加锁实现的。

• 记录锁(Record Lock)：记录锁是最基本的行锁类型，它直接锁定一行记录。
• 间隙锁(Gap Lock)：间隙锁锁定一个范围，但不包括记录本身。它用于防止幻读，确保在某个间隙内插入新记录时，事务的可见性保持一致。
• 临键锁(Next-Key Lock)：是记录锁和间隙锁的结合，它锁定一个范围，并且锁定记录本身。锁定的范围是左开右闭的区间。

11. MySQL发生死锁了怎么办？

答：产生死锁的四个必要条件：互斥、占有且等待、不可剥夺、相互等待。
在数据库层面，有以下两种方法可以解除死锁状态：

• 设置事务等待锁的超时时间：当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。
• 开启主动死锁检测：主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。
  在系统设计层面：
• 优化事务逻辑：重新设计或优化事务的逻辑，以减少事务的持锁时间和锁的范围。
• 使用读写分离：将读操作和写操作分离到不同的服务器上执行，可以减少并发操作的冲突和死锁的可能性。
• 终止事务：通过KILL命令终止占用锁资源的事务，从而释放锁并解除死锁状态。

参考资料

• JavaGuide
• 牛客网-Java面试宝典
• ChatGPT
• MySQL官网
• 图解MySQL