MySQL 面试：产生死锁的必要条件有哪些？如何解决死锁？

我们先来了解死锁是什么，并了解它们在事务中是如何发生的。

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

主题1.1：死锁的介绍

在多线程环境中，当两个或多个线程因为彼此持有并请求资源而无法进行时，就会出现死锁。在 MySQL 数据库的上下文中，死锁发生在两个或多个事务中，它们相互持有并请求锁，形成了资源的循环依赖。

在事务中，线程可能需要锁定多张表或行，这可能会导致线程需要一个已经被另一个线程锁定的资源的情况。同时，该线程可能正在等待另一个线程锁定的资源。这就是所谓的死锁。

让我们用一个简单的故事来说明：

想象两名漫画爱好者，Alice 和 Bob。Alice 现在持有最新的《超人》漫画，Bob 也想要它。同时，Bob 持有最新的《巧女》漫画，Alice 也想要它。现在，Alice 不想放弃她的《超人》漫画，直到她得到了《巧女》漫画。同时，Bob 也不想放弃他的《巧女》漫画，直到他得到了《超人》漫画。因此，两人都在等待对方释放他们的漫画，这就是死锁。

在数据库事务中，Alice 和 Bob 可能是事务，并且漫画可能是被锁定的资源。

主题1.2：了解必要条件以创造死锁

要了解死锁是如何发生的，我们需要熟悉 Coffman 条件，这是一组四个条件，它们必须全部满足才能发生死锁。这些条件被 Edward G. Coffman， Jr. 提出并命名。条件是：

1. 互斥：至少一个资源必须以非共享模式持有。这意味着只有一个进程（或线程）可以使用资源在任何给定时间内。如果另一个进程请求资源，请求进程必须被延迟，直到资源被释放。
2. 持有和等待（资源持有）：一个进程至少持有一个资源并等待其他资源，这些资源目前被其他进程持有。
3. 不可抢占：资源不能被强行从持有它们的进程处除，直到资源被使用完成。资源只能在持有它们的进程释放它们时释放。
4. 环形等待：存在一个环形链条中的进程，其中每个进程持有一个资源并请求另一个资源，被另一个进程在链条中持有。基本上，存在一个进程 P1，它在等待一个由进程 P2 持有的资源，并且 P2 在等待由 P1 持有的资源。这就形成了一个环形链条中的等待进程。

这四个条件 inherently 提供了一个逻辑结构来了解和结构防止策略。通过确保至少一个上述条件不会发生，我们可以防止死锁的形成。

主题1.3：在 MySQL 中检测死锁

在 MySQL 中，InnoDB 存储引擎自动检测并解决死锁，通过回滚与其中一笔交易。因此，您的应用应该总是准备好重新发出交易，因为它被回滚了，因为发生了死锁。

当在 MySQL 中发生死锁时，它会立即被检测和解决。这是通过维护哪些事务正在等待哪些其他事务持有的锁的 wait-for 图 来实现的。通过这种方法，MySQL 可以检查等待图中是否存在循环。如果它检测到循环，这意味着死锁，并且它会回滚一笔交易来解决死锁。

为了提供更多的见解，MySQL 还提供了诊断信息，当它检测和解决死锁时。这些信息可以从 SHOW ENGINE INNODB STATUS 命令获取，它会显示最新的死锁错误。

但是，需要注意的是，死锁并不总是表明设计错误或错误。在高并发系统中，死锁可能会偶然发生，并且可以被视为做生意的成本。然而，如果您经常遇到它们，可能值得进一步调查，以看看是否可以进行交易处理的改进。

主题1.4：避免死锁

下面是避免死锁的几个策略：

1. 锁定表的顺序保持一致：总是锁定表以相同的顺序。例如，如果所有的事务都先锁定“orders”表，就不会有一个事务锁定“orders”表，另一个事务锁定“products”表并等待“orders”表。
2. 快速和短的事务：短的事务更少可能会锁定需要的行，因为它们更少可能会锁定需要的行。
3. 错误处理：由于 InnoDB 自动检测死锁并回滚一笔交易，因此您需要准备好在代码中捕捉该错误并重新发出交易。
4. 使用较低的隔离级别：如果可能，请使用读提交隔离级别而不是重复读来减少死锁的可能性。
5. 避免热点：如果可能，请避免频繁更新的行，以减少死锁的可能性。例如，如果可能，请考虑使用不同的策略来计数操作，而不是使用计数表，每次操作时更新。

主题1.5：解决死锁

在解决死锁方面，理想情况是MySQL的InnoDB存储引擎自动检测和处理死锁。 InnoDB 使用一种称为 wait-for graph 的机制来检测死锁。当发生死锁时，InnoDB 选择一个事务并杀死它，从而解决死锁。

虽然这样解决了死锁，但对应用程序开发者来说，应该在应用程序中处理这些场景。当 InnoDB 因为死锁而杀死事务时，它会引发一个错误，需要在应用程序中捕捉。通常情况下，被终止的事务应该重试。

InnoDB 的自动死锁检测可以解决死锁，但在某些情况下，检测和杀死事务可能会花费较长的时间，影响应用程序的性能。因此，还要为避免死锁而设计应用程序。

虽然完全避免死锁在高并发系统中困难，但尽量避免它们会使数据库系统的操作更加稳定和高效。良好的编码习惯、有效地设计表格、正确地应用事务和锁控制可帮助避免大多数死锁。

主题1.6：诊断死锁

调查和诊断死锁可以提供有价值的见解，帮助防止它们或提高响应时间。MySQL 包含多种工具可帮助这个过程。

1. SHOW ENGINE INNODB STATUS：这条命令输出了一段文本报告，包含最近的死锁的信息，如果发生了死锁。它是在死锁发生后立即运行的，因为其信息会在下一个死锁中丢失。
2. InnoDB 监视器：这些是更详细和更广泛的报告，包含 InnoDB 内部的详细信息，包括死锁。它们分为标准、锁和互斥监视器。
3. 性能架构：MySQL 的性能架构可以配置为捕获详细数据事件，包括事务事件。这些数据存储在表中并可以像其他 MySQL 数据一样查询。
4. 二进制日志：MySQL 的二进制日志可帮助确定导致死锁的查询序列。这需要启用二进制日志并以行格式进行日志。
5. 错误日志：死锁会在这里记录下来，如果启用了 innodb_print_all_deadlocks 配置选项。

通过分析这些来源，可以确定参与死锁的事务并了解它们试图访问的资源。在许多情况下，详细分析可能会指出更好的锁序或更好的事务大小来避免预见的死锁。

主题1.7： 复习和评估

示例问题： 假设您在 MySQL 数据库中遇到了死锁。您决定运行命令 SHOW ENGINE INNODB STATUS 获取更多信息。

LATEST DETECTED DEADLOCK 部分给出了以下输出：

LATEST DETECTED DEADLOCK  
------------------------  

2022-08-24 23:08:02 7f3e6e2fd700  
*** (1) TRANSACTION:  
TRANSACTION 118945420, ACTIVE 22 sec inserting  
mysql tables in use 1, locked 1  
1700 lock struct(s), heap size 187648, 1249789 row lock(s), undo log entries 1  
MySQL thread id 155, OS thread handle 0x7f3e6e3e7700, query id 25749768 localhost user  
INSERT INTO customer (id, name, address) VALUES (3, 'John Doe', '123 Main St')  
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:  
RECORD LOCKS space id 66873 page no 70541 n bits 600 index `id` of table `test`.`customer` trx id 118945420 lock mode S waiting up to 3 years total: 47.56T, and currently at 47.68T to rise above: 47.68T  
*** (2) TRANSACTION:  
TRANSACTION 118945416, ACTIVE (PREPARED) 13 sec committing, thread declared inside InnoDB 476  
mysql tables in use 1, locked 1  
1 lock struct(s), heap size 368, 0 row lock(s)  
MySQL thread id 117, OS thread handle 0x7f3e6e2fd700, query id 25749765 localhost user  
COMMIT  
*** (2) HOLDS THE LOCK(S):  
RECORD LOCKS space id 66873 page no 70541 n bits 600 index `id` of table `test`.`customer` trx id 118945416 lock mode S  
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:  
RECORD LOCKS space id 66873 page no 70541 n bits 600 index `id` of table `test`.`customer` trx id 118945416 lock_mode X waiting up to 3 years total: 47.68T to rise above: 47.56T  
*** WE ROLL BACK TRANSACTION (1)

根据此信息，什么是导致死锁的原因，并可能解决它？

问题 1 — 简单熟练问题（难度 3/10）：

Coffman 条件中的四个条件是什么？请提供每个条件的简要说明。

问题 2 — 复杂熟练问题（难度 6/10）：

为了预防或最小化死锁发生，您将执行哪些步骤？

问题 3 — 复杂问题（难度 9/10）：

MySQL InnoDB 引擎为什么会自动解决死锁？什么是其优势和潜在的缺点？

请先自己试图找到答案，再问助。

解决示例问题的方法： 两个事务同时等待资源，其中事务（1）正在等待 id 索引的 S 模式（读）锁，该锁被事务（2）所持有。同时，事务（2）正在等待同一资源的 X 模式（写）锁，并持有 S 模式（读）锁。

这种情况意味着两个事务都无法进行，导致死锁。解决方法是确保事务请求锁的顺序，这是避免死锁的一种常见技术。

回答问题 1 的方法： Coffman 条件如下：

1. 互斥： 资源只能由一个进程所持有。
2. 保持和等待： 已经持有资源的进程可以请求新资源。
3. 不可抢： 只有持有资源的进程才能释放它。
4. 环形等待： 存在一个环形链条，其中每个进程持有下一个进程所需的资源。

回答问题 2 的方法： 多种方法可以防止或最小化死锁：

1. 锁超时设置： 限制事务等待获取锁的时间，可以减少等待时间并可能解决死锁。
2. 锁顺序： 按特定顺序请求锁可以避免环形等待条件。
3. 频繁提交： 较小的事务更可能与其他事务不发生冲突。
4. 死锁检测工具： 使用内置或第三方工具确定死锁发生的时间和原因，以便编程解决方案可以应用。

回答问题 3 的方法： 当 MySQL 发生死锁时，InnoDB 引擎会自动选择具有最少行锁的事务并回滚它来解决死锁。

优势：

1. 这种自动解决可以帮助保持数据库运行良好，无需手动干预。

潜在的缺点：

1. 如果事务较大或重要，则回滚可能会导致数据不被更新或保存正确。
2. 持续回滚可能会影响性能。
3. 不是所有的业务逻辑场景适合自动回滚。

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