MySQL事务隔离级别和锁

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

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

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

行编号	值
1个	集合1
2	集合2
…	集合N
450000	收藏450000

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

时间	交易1	交易2	评论
T1	语句执行		语句已执行。 共享锁应用于通过选择读取的记录
T2	在第10-20行读取锁s1		索引在整个范围内的锁定。 InnoDB有一个间隙锁的概念。
T3		语句执行	事务2语句已执行。 与选择所应用的s1类似的共享锁
T4		在第10-20行读取锁s2	共享的读取锁允许两个事务仅读取记录
T5	将锁x1插入到想要的索引的第13行中		事务1尝试获取第13行上的排他锁以进行插入，但事务2持有共享锁
T6		将锁x2插入到想要的索引的第13行中	事务2尝试获取第13行上的排他锁以进行插入，但事务1持有共享锁
T7			僵局！

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

因此，为了更深入地了解事务隔离的概念，ANSI / ISO SQL已将该概念定义为从最高隔离级别到最低隔离级别的以下各项：

1. 可序列化

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

2. 可重复读

   这是MySQL中使用的默认级别。 除了不使用范围锁之外，这主要类似于Serializable。 但是，在我看来，MySQL实现此级别的方式有些不同。 根据Wikipedia关于事务隔离的文章 ，没有实现范围锁定，因此幻像读取仍然可能发生。 幻像读取是指在事务中进行相同查询时，选择查询将具有其他记录的可能性。 但是，我从MySQL的文档中了解到，仍然使用范围锁，并且在同一事务中进行的相同选择查询将始终返回相同的记录。 也许我的理解是错误的，如果我的理解上有任何错误，我随时准备纠正。

3. 阅读已提交

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

4. 读未提交

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

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

翻译自: https://www.javacodegeeks.com/2014/05/mysql-transaction-isolation-levels-and-locks.html