我们如何处理生产环境中的 MySQL 死锁

免责声明：请注意，我已对产品的内部细节进行了大部分删减。部分内容中，我使用了简化的解释来阐述 MySQL 索引，尤其是在网络上已有其他详细解释的情况下。我已在适当的地方提供了链接。

在本文的剩余部分，我首先简要介绍一下我们在 Productive 公司的一些工作方式。然后，我会简要解释什么是死锁，最后介绍我们解决死锁的方法。

生产环境中的异步作业

在 Productive 系统中，我们使用 Sidekiq来调度异步任务。其中一些任务会在用户未记录工时时向他们发送电子邮件。另一些则是批处理任务，需要更新一些财务数据、以事务安全的方式复制某些对象等等。几个月前，我们开始注意到应用程序中死锁数量激增。尤其值得注意的是，这些死锁开始出现在需要处理大量相对繁重的数据库事务的 Sidekiq 批处理任务中。

什么是死锁？

数据库死锁是指两个事务无法继续进行，因为其中一个事务持有另一个事务需要的锁。死锁本身并没有什么问题，只要它不会严重影响数据库，导致某些事务完全无法处理。但是，你需要一种机制来重试死锁的事务。通常情况下，只需重试第二个事务（即导致死锁的事务）就足够了，因为 MySQL 只会中止该事务以释放锁，从而允许第一个事务继续执行。

在生产环境中查找死锁的根本原因非常困难。死锁难以复现（因为它们通常取决于特定时刻的事务吞吐量）。专门针对死锁启用额外日志记录也很棘手，因为它们会生成庞大的日志文件。即使找到了事务及其持有的锁，也几乎不可能立即找到合适的缓解措施。

实验

准备工作

随着应用程序中死锁的数量逐月增加，我们必须采取措施防止更严重的问题发生。首先，我们搭建了一个独立的临时环境，该环境与生产环境高度相似。我们还对产品代码进行了一些修改，以便更轻松地运行出现问题的 Sidekiq 作业的“模拟”。此外，我们还将该环境连接到监控系统，以便捕获日志。一旦开始批量运行作业，我们就立即发现了死锁问题。

死锁检测

检测死锁的方法不止一种。我们使用了两种。第一种是innodb status显示存储引擎运行信息的方法，其中包括最近发生的死锁。以下是该方法的输出示例innodb status：

该报表仅显示最近检测到的死锁，并打印出发生死​​锁的表的详细信息，包括被锁定的记录以及使用的锁类型。参与死锁的两个事务都会报告此信息。

查找死锁的第二个途径是performance_schema查看记录所有已授予数据库锁的数据库。以下是一个示例表输出data_locks，其中显示了所有当前已授予的锁（此表中的数据是“实时”的，这意味着锁会随着语句的执行而出现和消失）：

从该输出中，您可以判断某些 SQL 语句是否锁定了过多行，因为这有时会导致死锁（至少在我们的情况下是这样）。

分析僵局

分析死锁中涉及的语句以及它们持有的锁，并不能真正帮助我们找到导致死锁执行的代码块。即便能够找到，我们也需要找到一种方法，确保所有事务按照相同的顺序执行语句，以避免死锁。

通过查看输出，innodb status我们找到了正在使用的索引以及它们所在的表。的输出innodb status相当直观，仅显示哪个 SQL 事务卷入了死锁、使用了哪个索引以及使用了哪种类型的锁。每次新的死锁都会覆盖之前的输出innodb status，因此通过刷新状态，您可以分析发生了哪些死锁。

从输出结果可以看出，MySQL 会在不同时间对同一张表选择两个不同的索引。这两个索引都指向同一个列（简单索引）。值得注意的是，根据所使用的索引不同，最终被锁定的记录也会不同，这是 MySQL 确保数据完整性的方式。

了解导致死锁的索引和锁类型，有助于我们进一步分析performance_schema，特别是其data_locks表。该表包含所有已授予锁的实时信息。我们在该表中找到了导致死锁的锁以及它们锁定的索引记录（请注意，在 MySQL 中，被锁定的不是行，而是索引记录）。

索引优化

查看表格后，我们意识到，单独使用这两个索引时，MySQL 需要锁定比实际需要更大的索引记录范围。例如，我们的作业只需要更新一条记录D，但 MySQL 却锁定了介于两者之间的所有记录B，F因为这是它能够锁定的最小索引记录。由于被锁定的行数超过了实际需要，而我们的 Sidekiq 作业吞吐量又很高，这导致了大量的死锁。

分析结果表明，我们需要优化这两个索引。由于有问题的 MySQL 语句在其WHERE条件中使用了这两个索引列，因此我们决定删除这些索引。

我们选择使用包含这两列的复合索引。我们假设这样做可以减少因索引记录粒度更细而导致的锁定行数。合并两列缩小了 MySQL 查找需要修改的记录的范围，从而减少了锁定行数。在我们的测试环境中执行实验后，死锁次数从 5 万次减少到了 0 次。

第二个实验

新的僵局

使用新的复合索引再次运行实验后，我们再次发现死锁，但这次发生在不同的表上。症状与之前非常相似，因此我们采取了类似的方法，检查了innodb status锁的出现情况，并决定删除简单索引，最终用复合索引替换它们。重复实验后，我们发现使用新的复合索引后，死锁的数量实际上增加了一倍（从大约 300 个增加到 600 个），因此我们决定不再保留复合索引。

第二次实验中死锁数量的增加令人困惑，因此我们决定深入调查。直到查看数据库模式后，我们才注意到一个BEFORE INSERT数据库触发器。该触发器内部有一个语句，其 WHERE 条件包含两个列条件。其中一个列包含一个模式，对于我们的大多数客户而言，该模式的典型值为“{n}”。

再次尝试复合指数

与我们第一次实验中通过添加复合索引将 50,000 个死锁减少到 0 个不同，同样的方法这次却不起作用。问题在于存储模式的列（>98%模式为“ {n}”）没有变化，因此 MySQL 无法缩小搜索范围并减少锁定的记录。结果，MySQL 不得不锁定几乎整个表，死锁不可避免。我们找不到其他列来创建更合适的索引或更改触发器。但由于第二个表的死锁数量相对较少，我们决定不做任何更改。

结论

最终我们决定接受一定程度的死锁。结果发现，在生产环境中，当特定作业处于高峰期时，我们最终会遇到大约 50 次死锁，但通常情况下远少于此。事实上，在过去的几个月里，我们一直在监控死锁情况，每周最多只会遇到大约 16 次。然而，值得一提的是，优化索引并非总是解决死锁问题的最佳方案。正如上文所述，具体情况需要具体分析。

但同样重要的是，只要有事务重试策略，数据库就能正常运行，死锁本身并不一定是坏事。总而言之，三个月过去了，通过简单的索引修改，已经解决了超过 5 万个死锁问题。

总之，我们找到了一种超越MySQL 官方文档建议的死锁解决方法。优化索引是一种成本低廉但有效的减少死锁的方法。然而，谨慎地添加索引远比之后因为索引可能引发的问题而不得不将其删除要好得多。