MySQL 面试：简述 MySQL 的间隙锁

1月 3 2024 mysql 25 分钟读完 (约 3729 字)

你曾在面试中被要求类似的问题吗？或者将来会遇到，让我们一起探索和掌握它。

感谢您阅读这篇文章。更多面试问题:
https://programmerscareer.com/zh-cn/software-interview-set/

主题：深入探讨 MySQL

MySQL 是一个广泛使用的、开源的关系数据库管理系统 (RDBMS)。它使用关系数据库和结构化查询语言 (SQL) 来管理其数据。“My” 在 MySQL 中是 Michael Widenius 的女儿 My 的名字的缩写。

MySQL 数据库是一个稳定、可靠和强大的解决方案，具有高级特性，例如：

坚实事务支持
复制和故障转移集群支持
工作流控制和计划任务
查询缓存
高级复制技术

这使 MySQL 成为应用要求完整数据保护和实时分析的优秀选择，例如财务、银行、电子商务、CRM、ERP 应用等等。

接下来，让我们开始从理论上了解数据库。为了简单起见，让我们想象数据库是一个大的数字文件柜，满载的文件夹。文件夹代表表。在每个表中，包含真实数据，表示记录。每条记录包含有关单个实体的信息。

例如，如果你是一个商业主人，你可能有一个数据库，其中包含一个客户表和一个订单表等等。每行在客户表中代表一个单独的客户，每行在订单表中代表一个单独的订单。

主题：探讨 MySQL 事务

事务是数据库系统的基本概念。在 MySQL 中，事务是一组 SQL 语句的执行单元。事务遵循 ACID 模型，即原子性、一致性、隔离性和持久性。这个模型确保了数据库事务的可靠性。

例如，如果你在转账时从一个银行账户转移资金，这需要多个操作，例如从一个账户扣除资金并将其转移到另一个账户。在这种情况下，事务确保这些操作（信用和贷方）全部发生或不发生，确保数据一致性。

我们的下一步是学习 MySQL 中的锁定机制，这与事务密切相关。在数据库的上下文中，锁是与记录相关的标志。这个标志可以控制记录是否可以被读取或写入。

它是锁定使多名用户同时访问数据库时不发生冲突的关键。当记录或表被锁定时，这意味着某个事务正在访问数据，并且不应中断。

中文翻译:

主题：MySQL中锁定的介绍

在数据库领域，“锁定”是一个重要的特性，它确保并发数据访问的一致性和顺序。在 MySQL 中，InnoDB 存储引擎支持多种类型的锁定在不同的级别上，以确保事务不会相互干扰。

锁定特别重要在多个事务试图访问和操作同一块数据时。当一个事务锁定某块数据时，它阻止其他事务进行冲突的更改，直到锁定被释放。

MySQL 中有两种主要类型的锁定：

**共享锁 (S)**：这是一个只读锁定。多个共享锁可以同时保持对同一块数据，只要没有排他锁。
**排他锁 (X)**：排他锁是一个写锁定。当一个事务持有排他锁时，其他事务不能读取或写入该数据，直到锁定被释放。

在 MySQL 中，锁定可以发生在三个级别上：

行级锁定：这些锁定被放在数据行上。这是锁定的最细粒度，并允许最高的并发性。
页级锁定：这些锁定被放在数据页上。页级锁定比行级锁定更粗糙，并提供中等的并发性。
表级锁定：这些锁定被放在整个表上。这是锁定的最粗糙的，并提供最低的并发性。通常，在高并发环境中，我们希望避免表级锁定，因为它们可能会成为瓶。

了解这些基本锁定的概念后，我们可以深入研究 MySQL 中的更复杂的锁定类型，例如间隙锁。

主题：行锁和表锁在 MySQL 中

为了保证数据一致性并允许最高的并发性，MySQL 使用两种类型的锁定：行级锁定和表级锁定。每种类型都有自己的地方和目的。

行级锁定

行级锁定更细粒度，在更新特定行的表时使用。这意味着只锁定涉及操作的行，并不锁定整个表。这允许更高的并发性，其中多个事务可以同时访问不同行的同一表。

InnoDB 支持行级锁定。它会自动设置锁定在读和写操作时，但它不会锁定整个表。

例子：如果您正在更新特定记录的员工表，例如，如果使用这种锁定机制，则只会阻止试图修改该特定员工记录的事务。其他员工记录的任务可以继续进行。

表级锁定

表级锁定更粗糙。它锁定整个表在特定数据库操作时。通常，MySQL 在写操作时应用这种锁定。

虽然这种锁定允许简单的管理和更少的内存使用，但在高并发使用情况下，其并发性较低，因为多个事务需要同时访问同一表。

通过了解这两种类型的锁定，您已经一步 closer 了解了数据库操作的内部工作原理，并且在深入研究更复杂的锁定类型，例如间隙锁，时间戳锁定等方面有所帮助。

中文翻译:

主题：讨论 MySQL 中的间隙锁

间隙锁是 MySQL 中一个重要的机制，用于防止幻行。幻行是一种行，它满足 WHERE 子句的条件，但是在事务中未被初始查看或更新。

让我们考虑一种情况，其中我们有一个事务，它选择了特定范围内的行，并且后面要更新这些行。在这个操作期间，另一个事务插入了新行到该范围，创造了我们所称为的“幻行”。如果没有间隙锁，第一个事务不会意识到新行被第二个事务添加，并可能导致数据不一致。

这就是间隙锁的作用！

间隙锁是锁定索引记录之间的间隙。更具体地说，它是锁定索引记录的范围。在 MySQL 中，间隙锁防止其他事务在间隙被锁定的事务提供可重复读时插入新行。

例如，假设您有一个索引列，并且运行了以下语句在 REPEATABLE READ 隔离级别下：

1	SELECT * FROM table_name WHERE index_column > 100 FOR UPDATE;

MySQL 会为所有索引记录上锁，其中 index_column 的值大于 100，并为其后的间隙上锁。

请记，然而，间隙锁是双刃剑！虽然它可以确保一致性，但它也可能会引入锁等待或甚至死锁，如果不正确地管理。

主题：间隙锁的例子场景

要更好地理解间隙锁，让我们考虑一个例子场景。假设我们有一个表 orders，它有许多行。

场景 1:
例如，考虑以下 SQL 语句，

1	SELECT * FROM orders WHERE id > 3 FOR UPDATE;

在这个查询中的事务中，MySQL 会为所有记录上锁，其中 id 的值大于 3。这意味着另一事务不能在 orders 表中插入任何新记录，其中 id 的值大于 3，直到第一事务完成。

场景 2:
现在考虑另一个 SQL 语句，

1	INSERT INTO orders (id, item) VALUES (102, 'New_Item');

如果我们试图在先前事务（带有 SELECT … FOR UPDATE 语句）仍然处于活动状态时执行此语句，它将被阻止，直到第一事务完成。这是由第一事务所应用的间隙锁所导致的，它不允许任何新记录的插入，其中 id 的值大于 3。

这些例子场景说明了间隙锁如何控制并发事务，确保数据状态的一致性并消除幻读在某些事务隔离级别，例如 REPEATABLE READ 或 SERIALIZABLE 中。

现在，我们可以深入研究 MySQL 中锁定的更复杂的细节。

主题：通过锁的迷宫

在 MySQL 中，锁形成了一个复杂的迷宫，每个锁都有重要的作用，但如果不处理正确，可能会导致延迟或死锁。

下面是锁之间的简单交互方式：

共享锁和排他锁：

共享锁允许其他事务读取（共享锁）锁定的对象，但不允许写入（排他锁）。
排他锁阻止其他事务读取/写入锁定的对象。
可以应用额外的共享锁到已锁定的对象上，但请求排他锁将等待。

表锁和行锁：

表锁简单明了，但提供了更低的并发性，可能会导致事务延迟。
行锁提供更高的并发性，因为它们只锁定特定行在表中。

Gap 锁和 Next-Key 锁：

Gap 锁阻止插入到特定范围内的索引记录。它们与行锁（或 Next-Key 锁）配合起来防止 REPEATABLE READ 或 SERIALIZABLE 隔离级别中的幻读。

意向锁：

意向锁表明事务计划要获取的锁（共享或排他）之前就已经获取了它。它们是通知机制，不是控制机制。

自增锁：

自增锁用于维护自增值的序列。它们避免了多个事务同时尝试插入到自增列中的冲突。

成功地通过这个迷宫需要对每种锁类型和事务之间的影响有清晰的理解。

主题：复习和评估

在过去的课程中，我们深入探讨了 MySQL 和其锁机制。让我们回顾一下这些主题，以确保良好的理解：

深入了解 MySQL：我们开始时，了解了 MySQL 的界面、命令和与其他 SQL 实现的差异，为学习过程奠定了坚实的基础。
探索 MySQL 事务：我们深入探讨了 MySQL 事务的核心概念，讨论了其一致性和隔离级别，确保数据的准确性和并发性。
了解 MySQL 锁：我们介绍了 MySQL 锁的概念，这是维护数据完整性和并发控制的关键。
行锁和表锁：我们探讨了行级锁和表级锁，并讨论了它们在 MySQL 中的重要性。
讨论 MySQL 的Gap 锁：我们深入探讨了 gap 锁，包括它是什么、它是如何工作的和它在 REPEATABLE READ 或 SERIALIZABLE 隔离级别中的重要性。
Gap 锁的示例场景：我们步行了常见的场景，以了解 gap 锁的实际应用。
通过锁的迷宫：我们讨论了 MySQL 中锁之间的交互和影响，这是一个复杂但有趣的主题。

例子问题：

考虑一种高流量数据库，您经常遇到死锁。您的任务是识别一个可能的解决方案来最小化这些发生的可能性。

解决方案：可能的解决方案包括缩短事务时间、确保事务访问表的相同顺序或者增加 innodb_lock_wait_timeout 值。还可以确保使用最具体的锁来帮助减少死锁的可能性。

中文翻译：

简单问题：

考虑一个事务，它读取和写入表中的多条记录。要确保高流量数据库中的最小阻塞，应该使用哪种锁（行级锁、表级锁或Gap锁），并且避免幻读？

进阶问题：

在票务预订系统中，可能会有多个并发事务试图同时预订同一座位。如何使用 MySQL 的锁机制来确保公平的系统？

专家问题：

在 MySQL 的上下文中，如何处理银行应用中的死锁场景，其中两个事务同时尝试转移两个账户之间的资金？

简单问题解决方案：

对于这种场景，使用行级锁机制将是最有效的。它会提供所需的锁定来确保数据完整性，同时避免高流量情况下不相关行的不必要阻塞。此外，包含“FOR UPDATE”子句在 SELECT 语句中可能会避免幻读。

进阶问题解决方案：

在票务预订系统中，要确保公平的系统，我们可以使用 SELECT FOR UPDATE 命令。这会为所遇到的所有索引记录放置排他的 Next-Key 锁，从而防止其他事务在覆盖的记录锁下插入新行。它还会选择座位的当前状态，并如果它可用，则更新其为已预订，确保座位不会被双预订。

专家问题解决方案：

在银行应用中，如果两个事务同时尝试转移两个账户之间的资金，我们可能会遇到死锁场景。要处理这种情况，我们可以使用访问帐户的固定顺序。例如，事务可能会先访问具有较低 ID 的帐户。这将防止死锁，因为两个事务不会无限期地等待对方，消除了死锁的循环等待条件。

English post: https://programmerscareer.com/mysql-interview10/
作者：Wesley Wei – Twitter Wesley Wei – Medium
注意：本文为作者原创，转载请注明出处。

#interview