Java八股文面试网 – Java2Top.cn

MySQL进阶—锁🔒机制篇

小龙coding大约 15 分钟

MySQL (5)

详述MySQL锁机制？从不同维度说说有哪些锁？

  1. 从锁的粒度来看：
    • 行级锁：是最细粒度的锁，它可以最大程度地支持并发处理（同时也带来了最大的锁开销）。InnoDB支持行级锁。
    • 页级锁：介于表级锁和行级锁之间，锁定粒度比表级锁小，比行级锁大。MySQL的BDB存储引擎支持页级锁。
    • 表级锁：粒度大，冲突可能性高，但开销相对较小，加锁速度快。MyISAM和InnoDB都支持表级锁。
  1. 从锁的级别来看：
    • 共享锁（S锁）：允许事务读取被锁定的数据。
    • 排他锁（X锁）：允许事务删除或更新被锁定的数据。
    • 意向锁：是一种表级锁，用于在存储引擎层面协调多个事务对同一表的行级锁和表级锁的申请，包括意向共享锁和意向排他锁。
  1. 从加锁方式来看：
    • 自动锁：InnoDB在执行SQL语句时自动加的锁（update、delete会自动加锁）
    • 显式锁：用户主动申请的锁，如LOCK TABLES...。（lock in share mode、for update显式加锁）
  1. 从操作方式来看：
    • DML锁：数据操作语言（DML）的锁，如SELECT、INSERT、UPDATE、DELETE等。
    • DDL锁：数据定义语言（DDL）的锁，如CREATE、DROP、ALTER等。
  1. 从使用方式来看：
    • 乐观锁：假设数据通常情况下不会造成冲突，只在提交操作时才会检查是否有冲突。
    • 悲观锁：假设数据很可能会造成冲突，直接在数据读取时就加锁。
  1. 从存储引擎角度来看：
    • MyISAM：只支持表级锁，不支持事务。
    • InnoDB：支持行级锁和表级锁，且默认为行级锁，支持事务。

这些锁的类型和级别都有各自的使用场景，需要根据具体的业务需求和系统环境来选择。

InnoDB引擎级别 支持哪几种锁？

InnoDB存储引擎提供了以下几种类型的锁：

  1. 行级锁：InnoDB的行级锁是通过给索引上的索引项加锁来实现的，分为记录锁和间隙锁。
    • 记录锁：是对索引记录加锁，阻止其他事务修改这个记录。即使定义的表没有索引，对于这种情况， InnoDB创建一个隐藏的聚集索引并将该索引用于记录锁定。
    • 间隙锁：是对索引记录之间的“间隙”加锁，阻止其他事务在这个间隙中插入新的记录。
    • 临键锁：InnoDB在行级锁的基础上引入了临键锁，用于解决幻读问题。临键锁实际上是记录锁+间隙锁，可以锁定一个范围，防止幻读的发生。
  1. 表级锁：InnoDB的表级锁是用来处理全表扫描的查询，以及外键约束等情况的。
  2. 意向锁：意向锁是一种表级锁，用于在存储引擎层面协调多个事务对同一表的行级锁和表级锁的申请，包括意向共享锁和意向排他锁。
  3. 自增锁：InnoDB为了处理自增ID生成时的并发问题，引入了自增锁。当有多个事务同时插入数据并需要生成自增ID时，InnoDB会使用自增锁来保证ID的连续性。
  4. 插入意向锁：这是一种特殊的间隙锁，当InnoDB在插入记录时，会先判断该位置是否可以插入，如果可以则加上插入意向锁，防止其他事务在此位置插入。

每种锁都有其特定的使用场景，可以根据实际情况选择使用。

InnoDB的各种锁在不同的场景下的效用？

  1. 记录锁：在需要对某一行数据进行修改（如UPDATE，DELETE）时，InnoDB会对该行数据加记录锁，防止其他事务同时修改这一行数据，保证数据的一致性。
  2. 间隙锁：在可重复读（REPEATABLE READ）隔离级别下，InnoDB会在查找范围时使用间隙锁，防止其他事务在这个范围内插入新的记录，从而解决了幻读问题。
  3. 临键锁：在可重复读（REPEATABLE READ）隔离级别下，InnoDB默认使用临键锁，可以防止幻读。
  4. 插入意向锁：在插入记录前，InnoDB会先判断该位置是否可以插入，如果可以则加上插入意向锁，防止其他事务在此位置插入。
  5. 自增锁：在插入数据并需要生成自增ID时，InnoDB会使用自增锁来保证ID的连续性。
  6. 意向锁：在多个事务对同一表的行级锁和表级锁的申请时，InnoDB会使用意向锁来协调，防止死锁。

然而，这些锁也有可能在某些场景下失效：

  1. 在READ COMMITTED隔离级别下，间隙锁和临键锁不会被使用，因此无法防止幻读。
  2. 如果事务不正确地使用锁，可能会导致死锁。例如，两个事务都试图获取对方已经持有的锁。
  3. 如果事务在持有锁的同时进行其他非原子操作，可能会导致数据不一致。
  4. 在并发高的情况下，锁的开销可能会导致性能下降。

讲一下 MySQL 死锁的原因及处理方法？

当然，你还可以参照（场景篇系统了解死锁场景下的一些方案）

MySQL死锁通常是由于多个事务同时锁定资源而导致的。当两个或更多的事务相互等待对方释放资源时，就会发生死锁。以下是一些常见的导致死锁的原因：

  1. 并发事务。当多个事务试图同时锁定同一资源时，可能会发生死锁。
  2. 锁定顺序不一致。如果两个事务以不同的顺序锁定资源，也可能会导致死锁。
  3. 事务持有锁的时间过长。如果一个事务在完成其操作之前持有锁的时间过长，也可能会导致其他事务等待时间过长，从而引发死锁。

处理MySQL死锁的方法主要有以下几种：

  1. 重新设计数据库和应用。确保事务在尽可能短的时间内完成，减少锁定资源的时间。
  2. 设置锁定超时时间。可以通过设置innodb_lock_wait_timeout参数来控制一个事务等待锁定资源的最长时间。
  3. 捕获并处理死锁。当检测到死锁时，MySQL会自动回滚其中一个事务，释放其持有的所有锁，从而解决死锁。应用程序可以捕获这个错误，然后重新执行事务。
  4. 优化索引和查询。通过优化索引和查询，可以减少锁定的行数，从而减少死锁的可能性。
  5. 使用锁定顺序。在所有事务中，总是以相同的顺序锁定资源，可以避免死锁。

InnoDB 锁的算法？

参照 “InnoDB引擎级别 支持哪几种锁？”理解吧，重复的不再赘述

InnoDB存储引擎提供了多种锁定算法，包括：

  1. Record Locks：单个行记录上的锁。这是最基本的锁类型，对索引记录进行加锁，防止其他事务修改这个记录。
  2. Gap Locks：间隙锁，锁定一个范围，但不包括记录本身。这种锁防止其他事务在该范围内插入新的记录。
  3. Next-Key Locks：下一个键锁，是记录锁和间隙锁的组合，锁定一个范围，包括记录本身。这种锁防止其他事务在该范围内插入新的记录，也防止其他事务修改这个记录。
  4. Insert Intention Locks：插入意向锁，当事务打算在某个间隙插入一条记录，但由于其他事务持有在该间隙的共享锁或独占锁，就会在该间隙上设置插入意向锁。
  5. Auto-Increment Locks：自增锁，当表有自增列时，InnoDB会使用这种特殊的表级锁，防止并发插入导致的自增ID重复。

以上就是InnoDB存储引擎的锁定算法，这些锁定算法可以确保事务的隔离性，并且可以在并发处理时防止数据的不一致性。

表级锁和行级锁有哪些区别？

表级锁和行级锁是数据库中两种常见的锁定机制，它们的主要区别在于锁定的粒度和性能影响。

  1. 锁定粒度：表级锁会锁定整个表，包括表中的所有行。而行级锁只会锁定被操作的那一行或者几行数据。因此，表级锁的粒度大，行级锁的粒度小。
  2. 并发性能：由于表级锁会锁定整个表，因此在高并发的情况下，可能会导致大量的事务等待，降低并发性能。而行级锁只会锁定特定的行，其他事务可以访问表中的其他行，因此并发性能更好。
  3. 死锁：由于行级锁的粒度小，更容易发生死锁。而表级锁由于锁定的粒度大，发生死锁的可能性相对较小。
  4. 资源消耗：行级锁需要更多的内存和存储资源，因为数据库需要为表中的每一行都维护一个锁。而表级锁只需要为每个表维护一个锁，因此资源消耗较小。
  5. 使用场景：表级锁适合查询为主，更新较少的场景，因为查询不会产生锁竞争。行级锁适合更新频繁，有大量并发事务的场景，因为它可以最大程度地支持并发处理。

MySQL 记录锁+间隙锁可以防止删除操作而导致的幻读吗？

结论：MySQL的InnoDB存储引擎通过记录锁（行锁）和间隙锁（Gap Lock）的结合，可以防止"幻读"的发生。

"幻读"是指在一个事务内，多次查询返回的结果集不一致，这是由于在查询过程中，其他事务插入或删除了记录导致的。例如，你在一个事务中先后两次执行相同的查询，第二次查询返回了第一次查询中不存在的行，这就是"幻读"。

为了解决这个问题，InnoDB引入了间隙锁。间隙锁是在索引记录之间的“间隙”上设置的锁，用于防止其他事务在这个“间隙”中插入新的记录。这样，即使有新的记录插入，也不会影响当前事务的查询结果，从而避免了"幻读"。

同时，记录锁（行锁）则是对索引记录加锁，防止其他事务修改或删除这个记录。

通过记录锁和间隙锁的结合，InnoDB可以在可重复读（REPEATABLE READ）和串行化（SERIALIZABLE）隔离级别下，防止"幻读"的发生。

什么是幻读？RR隔离级别幻读解决了吗？怎么解决幻读？（事务篇-回顾）

幻读：是指在一个事务内读取到了别的事务插入的数据，导致前后读取的数据不一致。例如，你在一个事务中先后两次执行了相同的查询语句，第一次查询返回了5条记录，但第二次查询却返回了6条记录，这就是幻读。

在标准的 SQL 隔离级别定义中，可重复读隔离级别并不能解决幻读问题。因为标准定义下的重复读隔离级别，虽然可以防止同一条记录被其他事务修改，但无法防止其他事务插入新的满足查询条件的记录，因此可能出现幻读。只有在串行化（Serializable）隔离级别下，才能完全防止幻读。

但在 MySQL 的 InnoDB 存储引擎中，可重复读（Repeatable Read）隔离级别实际上已经解决了幻读的问题。

  1. 对于读操作，由于使用了一致性读（consistent read）和多版本并发控制（MVCC），可以保证在同一事务中多次读取同样记录的结果是一致的，避免了不可重复读和幻读的问题。
  2. 对于写操作，InnoDB 使用了 next-key locking 机制，这种机制会锁定记录行以及索引记录的“间隙”，防止其他事务在这些“间隙”中插入新的记录，从而避免了幻读。

但是，虽然在可重复读隔离级别下，InnoDB 可以有效地防止幻读，但如果事务在读取数据后又进行了写操作（例如，先SELECT，然后UPDATE），可能会出现因为并发操作而导致的数据不一致问题。这种情况下，可能需要使用更高的隔离级别（如串行化）或者显式地使用锁（如SELECT FOR UPDATE）来确保数据的一致性。

快照读与当前读有什么区别？

在MySQL的InnoDB存储引擎中，读操作可以分为两种：快照读（Snapshot Read）和当前读（Current Read），有时候也被称为一致性读（Consistent Read）和锁定读（Locking Read）。另外，快照读其实也称为普通读

快照读

快照读是InnoDB默认的读取方式，也是最常见的一种。当进行快照读时，InnoDB会读取记录在事务开始时的版本，而不是最新的版本。这就意味着，即使在事务执行过程中，其他事务对记录进行了修改，这些修改也不会影响到当前事务的快照读结果。这种读取方式不会设置任何锁，一般的SELECT查询就是快照读。

快照读的实现原理：主要依赖于InnoDB的多版本并发控制（MVCC），可总结为：MVCC+undo log 版本链，具体详情图示原理参考“事务篇”，这里做个大致概述

普通读的执行方式是生成 ReadView，直接利用 MVCC 机制来进行读取，并不会对记录进行加锁。

在MVCC中，每个事务有一个唯一的事务ID，每个记录也有一个创建版本号和删除版本号，分别对应创建该记录和删除该记录的事务ID。当事务进行快照读时，InnoDB会根据事务ID和记录的创建版本号和删除版本号，找到在事务开始时已经存在且在事务执行过程中未被删除的记录版本。

当前读

当前读会读取记录的最新版本，也就是说，如果其他事务在当前事务开始后对记录进行了修改，那么当前事务的当前读会看到这些修改。

当前读在读取记录的同时，会给记录设置锁，具体是设置共享锁还是排他锁，取决于当前读是由哪种SQL语句触发的。例如，SELECT ... FOR UPDATE会触发当前读并设置排他锁，而SELECT ... LOCK IN SHARE MODE会触发当前读并设置共享锁。除此之外，UPDATE，DELETE，INSERT等语句也都会触发当前读。

当前读的实现原理：主要依赖于InnoDB的行锁。当事务进行当前读时，InnoDB会给读取的记录设置锁，防止其他事务在当前事务完成前对记录进行修改。如果记录已经被其他事务锁定，那么当前事务会等待，直到其他事务释放锁。

总的来说，快照读和当前读的主要区别在于读取的是记录的哪个版本，以及是否设置锁。快照读读取的是记录在事务开始时的版本，并且不设置锁；而当前读读取的是记录的最新版本，并且会设置锁。这种设计可以在保证数据一致性的同时，提高并发性能。

哪些SQL执行使用的是快照读，哪些SQL执行使用的当前读？

在MySQL的InnoDB存储引擎中，以下是一些常见的SQL语句对应的读取方式：

  1. 使用快照读（Snapshot Read）的SQL语句：
    • 一般的SELECT查询，例如：SELECT * FROM table_name。
    • 在事务中的SELECT查询，例如：START TRANSACTION; SELECT * FROM table_name; COMMIT;。这种情况下，即使在事务执行过程中，其他事务对记录进行了修改，这些修改也不会影响到当前事务的SELECT查询结果。
  1. 使用当前读（Current Read）的SQL语句：
    • SELECT ... FOR UPDATE。这种语句会给选中的记录设置排他锁，防止其他事务在当前事务完成前对记录进行修改。
    • SELECT ... LOCK IN SHARE MODE。这种语句会给选中的记录设置共享锁，允许其他事务进行读取，但防止其他事务进行修改。
    • UPDATE，例如：UPDATE table_name SET column_name = value WHERE condition。这种语句会给选中的记录设置排他锁，并更新记录的内容。
    • DELETE，例如：DELETE FROM table_name WHERE condition。这种语句会给选中的记录设置排他锁，并删除记录。
    • INSERT，例如：INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)。这种语句会给新插入的记录设置排他锁。

总的来说，快照读和当前读的主要区别在于是否设置锁，以及读取的是记录的哪个版本。快照读不设置锁，读取的是记录在事务开始时的版本；而当前读会设置锁，读取的是记录的最新版本。