MySQL事务隔离级别和锁_MySQL

最近，我的团队正在开发的一个应用程序遇到了 MySQL 死锁的问题，我们花了一些时间来找出背后的原因。我们部署的这个应用程序在 2 节点集群上运行，它们都连接到 AWS MySQL 数据库。 MySQL 数据库表主要基于 InnoDB，它支持事务（意味着所有常见的提交和回滚语义）以及 MyISAM 引擎不提供的行级锁定。因此，当我们的用户由于某些设计不良的用户界面而能够在数据库上执行两次相同的长时间运行操作时，问题就出现了。

事实证明，由于我们有一个双数据库节点集群中，每个用户操作都源自不同的 Web 应用程序（这又意味着 2 个不同的事务运行相同的查询）。死锁查询恰好是一个“INSERT INTO T… SELECT FROM S WHERE”查询，该查询在 SELECT 查询中使用的记录上引入了共享锁。在这种情况下，T 和 S 恰好是同一张表，这并没有什么帮助。实际上，共享锁和排它锁都应用在同一个表上。下表可以解释查询死锁的可能原因。这是基于我们使用默认 REPEATABLE_READ 事务隔离级别的假设（稍后我将解释事务隔离的概念）

假设我们有一个这样的表：

RowId	Value
1	Collection 1
2	Collection 2
…	Collection N
450000	Collection 450000

以下是一个示例序列，基于运行 SQL 查询（例如“INSERT INTO T SELECT FROM T WHERE …”）的 2 个事务，可能会导致死锁：

Time	Transaction 1	Transaction 2	Comment
T1	Statement executed	Statement executed. A shared lock is applied to records that are read by selection
T2	Read lock s1 on Row 10-20	The lock on the index across a range. InnoDB has a concept of gap locks.
T3	Statement executed	Transaction 2 statement executed. Similar shared lock to s1 applied by selection
T4	Read lock s2 on Row 10-20	Shared read locks allow both transaction to read the records only
T5	Insert lock x1 into Row 13 in index wanted	Transaction 1 attempts to get exclusive lock on Row 13 for insertion but Transaction 2 is holding a shared lock
T6	Insert lock x2 into Row 13 in index wanted	Transaction 2 attempts to get exclusive lock on Row 13 for insertion but Transaction 1 is holding a shared lock
T7	Deadlock!

只有当我们使用REPEATABLE_READ（引入共享读锁）时才会出现上述场景。如果我们将事务隔离级别降低到 READ_COMMITTED，我们就会减少发生死锁的机会。当然，这意味着放宽数据库记录的一致性。就我们的数据需求而言，我们对强一致性没有这么严格的要求。因此，一个事务读取其他事务提交的记录是可以接受的。

因此，为了更深入地研究事务隔离的概念，ANSI/ISO SQL 将这一概念定义如下：最高隔离级别到最低：

1. 可序列化

   这是最高的隔离级别，通常需要使用共享读锁和独占写锁（如以下情况） MySQL）。这本质上意味着任何查询都需要访问记录上的共享读锁，以防止另一个事务的查询修改这些记录。每个更新语句都需要访问独占写锁。此外，当使用带有 WHERE 条件的 select 语句时，必须获取范围锁。这在 MySQL 中作为间隙锁实现。

2. 可重复读取

   这是 MySQL 中使用的默认级别。除了不使用范围锁之外，这主要与 Serialized 类似。然而，MySQL 实现这个级别的方式在我看来有点不同。根据维基百科有关事务隔离的文章，未实现范围锁，因此幻读仍然可能发生。幻读是指当在事务中进行相同查询时，选择查询可能会有额外的记录。然而，我从 MySQL 的文档中了解到，仍然使用范围锁，并且在同一事务中进行的相同选择查询将始终返回相同的记录。也许我的理解有误，如果我的解释有任何错误，我随时准备纠正。

3. 已提交读

   这是一个隔离级别，将保持写锁直到事务结束，但读锁将在 SELECT 语句结束时释放。它不保证 SELECT 语句在同一事务中再次运行时会找到相同的数据。但是，它将保证读取的数据不是“脏”并且已提交。

4. 读取未提交

   这是我怀疑的隔离级别对于大多数用例来说都是有用的。基本上，它允许事务查看所有已修改的数据，包括“脏”或未提交的数据。这是最低的隔离级别

通过不同的事务隔离级别，我们可以看到事务隔离级别的选择如何决定数据库锁定机制的类型。从实际的角度来看，当您处理像我们这样的场景时，默认的 MySQL 隔离级别 (REPEATABLE_READ) 可能并不总是一个好的选择，在数据读取中确实不需要如此强的一致性。我相信通过降低隔离级别，可能会减少数据库查询遇到死锁的机会。此外，它甚至可能允许对数据库进行更高的并发访问，从而提高查询的性能水平。当然，这需要注意的是，您需要了解一致性读取对于您的应用程序有多重要。如果您正在处理精度至关重要的数据（例如您的银行账户），那么绝对有必要施加尽可能多的隔离，以便您不会在交易中读取不一致的信息。

Reference:

MySQL Transaction Isolation Levels and Locksfrom ourJCG partnerLim Han at theDevelopers Cornerblog.

您可能还喜欢：

• 在 MySQL 中调试 SQL 查询
• MySQL 分页注意事项
• Java 并发教程 – 可重入锁

相关白皮书：
表>

Software Architecture This guide will introduce you to the world of Software Architecture! This 162 page guide will cover topics within the field of software architecture including: software architecture as a solution balancing the concerns of different stakeholders, quality assurance, methods to describe and evaluate architectures, the influence of architecture on reuse, and the life cycle of a system and its architecture. This guide concludes with a comparison between the professions of software architect and software engineer. Get it Now!

软件架构

本指南将向您介绍软件架构的世界！这本 162 页的指南将涵盖软件架构领域内的主题，包括：软件架构作为平衡不同利益相关者关注点的解决方案、质量保证、描述和评估架构的方法、架构对重用的影响，以及系统及其架构的生命周期。本指南最后对软件架构师和软件工程师的职业进行了比较。立即获取！