理解MySQL中的锁粒度

理解MySQL中的锁粒度

如果您已经使用MySQL一段时间了，您可能已经听说过术语“表级锁定”和“行级锁定”。这些术语指的是MySQL中的锁粒度——在本博客中，我们将解释它们的含义和用途。

什么是MySQL中的锁粒度？

每个MySQL存储引擎都支持不同级别的锁粒度。MySQL有三个锁级别：行级锁、页级锁和表级锁。每个MySQL存储引擎实现锁定的方式都不同，都有一些明显的优点和缺点。我们将首先研究锁粒度是什么，然后研究不同存储引擎中的一切是如何工作的。

广义地说，MySQL中的锁属于这些类别之一。锁可以是：

• 页面级别–这种类型的锁粒度在MySQL的旧引擎中可用，特别是BDB 在MySQL 5.1中已经过时。 此处不再讨论。
• 表级别–MySQL对除InnoDB之外的所有存储引擎使用表级别锁定。
• 行级别–行级别锁定由InnoDB使用。

表级锁的优缺点

MySQL对除InnoDB之外的所有存储引擎使用表级锁定，这意味着表级锁用于运行MyISAM、MEMORY和MERGE存储引擎的表，一次只允许一个会话更新表。表级锁与行级锁相比有一些明显的优势（例如，表级锁通常比行级锁需要更少的内存，因为行级锁每行（或组）被锁定的行需要一些内存，并且通常速度很快，因为只涉及一个锁。如果表上没有锁，则将表写锁放在表上——如果有问题的表上存在预先存在的锁，则表锁请求将放在读取请求队列中。值得一提的是，表级锁定本身也有一些明显的缺点——例如，它可能不太适合需要“来回”执行大量事务的应用程序（例如，在线银行应用程序），因为在任何时候只有一个会话可以写入表，并且一些支持表级锁定的表（例如MyISAM）不支持ACID模型。

这里有一个例子：假设一个银行应用程序在一个数据库中使用两个表——假设这些表被称为“checking”和“savings”。你需要把100美元从一个人的支票账户转到他的储蓄账户。从逻辑上讲，您应该执行以下步骤：
1.确保账户余额大于100美元。
2.从支票账户中减去100美元。
3.在储蓄账户中增加100美元。

要执行这些操作，您需要几个查询，例如：

SELECT balance FROM checking WHERE account_id = 123;
UPDATE checking SET balance = balance - 100 WHERE account_id = 123;
UPDATE savings SET balance = balance + 100 WHERE account_id = 123;

这些查询可能看起来很简单，但如果您使用MyISAM（我们以MyISAM为例，因为它是支持表级锁的主存储引擎之一），您应该熟悉这样一个事实，即该引擎也不支持ACID，这意味着如果数据库服务器在执行这些查询时崩溃，你运气不好：人们可能在两个账户中都有现金，也可能在其中任何一个账户中没有现金。MySQL中唯一支持基于ACID的事务的引擎是InnoDB，因此如果您需要大量可靠的事务，可能值得研究一下。InnoDB还支持行级锁定–这就是我们现在要研究的内容。

行级锁定的优缺点

MySQL对InnoDB表使用行级锁定，以支持多个会话同时进行写访问。使用行级锁定的一些优点包括能够长时间锁定单个行，以及在多个线程访问不同的行时减少锁定冲突。但是，行级锁定具有

MySQL对InnoDB表使用行级锁定，以支持多个会话同时进行写访问。使用行级锁定的一些优点包括能够长时间锁定单行，以及在多个线程访问不同的行时减少锁定冲突。然而，行级锁定也有缺点：其中之一是行级锁定通常比页级或表级锁定占用更多内存，它通常也比页级和表级锁定慢，因为引擎必须获得更多锁。InnoDB是支持行级锁定机制的引擎之一：它也符合ACID，这意味着它非常适合基于事务的应用程序（请参阅上面的示例）。现在，我们将研究锁定粒度在一个MySQL存储引擎中是如何工作的。

锁粒度如何在InnoDB中工作？

众所周知，InnoDB支持行级锁定，但也值得注意的是，引擎支持多种类型的锁定，这意味着您可以同时使用行级和表级锁定。InnoDB通过对搜索或扫描表索引时遇到的索引记录设置共享锁或独占锁来执行行级锁定。共享锁是这样一种锁，它允许持有该锁的事务读取相关行，而独占锁则允许持有该锁定的事务更新或删除行。

InnoDB还有其他类型的锁——其中一些包括共享锁和独占锁、意向锁、记录锁、间隙锁、next-key锁和下一个意向锁。例如，意向锁也可以是共享的或独占的-此类锁通常表示事务打算在表中的各行上设置某种类型的锁（共享锁或独占锁），记录锁是索引记录上的锁等。

通常，InnoDB锁粒度与其他MySQL存储引擎（例如MyISAM）中的锁粒度不同，因为当使用表级锁时，一次只能运行一个会话来更新某些表。当使用行级锁定时，MySQL支持跨多个会话的同时写入访问，使行级锁定存储引擎（InnoDB）成为任务关键型应用程序的合适选择。

锁粒度和死锁

MySQL中的锁粒度和锁级别可能是一件好事，但它们也可能导致问题。锁粒度引起的最常见问题之一是死锁——当不同的MySQL事务无法继续进行时，就会发生死锁，因为每个事务都持有另一个需要的锁。幸运的是，当使用InnoDB存储引擎时，默认情况下会启用死锁检测–当检测到死锁时，InnoDB会自动回滚事务。如果在MySQL中处理锁粒度时遇到死锁，请不要担心，只需考虑重新启动事务即可。为了主动监视数据库，您还应该考虑利用 ClusterControl .提供的功能。

ClusterControl如何帮助您？

ClusterControl是Severalnines公司开发的产品，它可以帮助您：

• 保护所有业务数据
• 如果数据损坏（可能是由于未使用符合ACID的存储引擎或上述其他因素造成的），该工具可以运行一个自动进程，实际验证您是否可以恢复数据。
• 了解哪些数据库未备份，或显示备份的状态（备份成功还是失败）
• 数据库操作的自动化
• 有助于系统管理员、开发人员和DBA使用行业最佳实践高效地管理整个数据库集群，并将风险降至最低
• 总体上有效管理数据库基础架构

今天，技术的转变与复杂的基础架构解决方案相结合，需要先进的工具和知识来实现业务关键型应用程序的高可用性和最佳性能。ClusterControl 还可以帮助您部署、监控、管理和扩展最流行的开源数据库技术，包括MySQL、MariaDB、MongoDB、PostgreSQL、TimeScaleDB等。

小结

不同的MySQL存储引擎具有不同类型的可用锁粒度。在决定应该使用哪种存储引擎之前，请确保尽可能多地了解 其相关信息（例如，正如已经指出的那样，在处理任务关键型数据时应避免使用MyISAM，因为它不符合ACID），了解所有相关的性能影响，包括锁粒度、死锁等，然后明智地选择。