解决MySQL幻读的终极指南

​译者 | 布加迪

审校 | 孙淑娟

一说起关系数据库，人们通常会先想到MySQL。MySQL使用InnoDB作为其存储引擎，而可重复读取隔离级别（在事务开始之前查看数据）最常见。

不过与PostgreSQL不同，InnoDB中的可重复读取隔离级别无法处理丢失更新和幻读（phantom read）之类的问题，而您不需要额外的技巧就能解决PostgreSQL中的丢失更新。您可以使用几个技巧来执行幻读，比如范围类型及其他机制。

MySQL开发人员应了解可能存在的陷阱，并且能够采取适当的做法，避免丢失更新和幻读等问题。本文介绍MySQL开发人员如何解决幻读可能导致的“写偏”。

导致幻读的场景

不同的场景可能导致幻读。一般来说，这些场景都遵循类似的模式。最初搜索MySQL数据库中的特定范围，然后根据已搜索范围的结果执行CREATE（创建）、UPDATE（更新）或DELETE（删除）等操作。之后，执行的操作直接影响从已搜索的范围中获取的结果。

比如，假设在搜索特定范围后采取的操作是UPDATE或DELETE。在这种情况下，MySQL开发人员可以使用独占锁来避免“写偏”。然后，开发人员可以在“SELECT”操作开始时使用FOR UPDATE，之后他们可以强制两个同时的事务依次完成。因此，这两个同时的事务在竞态条件下，规避了“写偏”。

但是，如果我们假设根据特定范围的搜索结果采取的操作是CREATE，上述解决方案就是不完整的：不存在开发人员可以用SELECT锁定的对应行，这意味着稍后形成一行。

使用CREATE时，如何解决幻读

  我们将介绍一种实际场景，以便更好地理解使用CREATE时，解决幻读导致的问题。

想象人们可以预订会议室的系统；在有人使用该系统预订房间后，新数据添加到表中。该系统让用户可以根据他们想要预订的时间段查询某个房间是否可以预订。一旦有人创建了新的预订条目，所有其他用户就能避免时段冲突。

然而，当两个人同时预订同一房间时，就会出现问题。两个用户都能够通过最初的SELECT验证，这意味着理论上他们都可以预订同一时段的房间，从而导致时段冲突。比如说，如果有多个需要使用该预订系统的用户通过科学连接到远程SQL服务器，该问题可能更复杂。即使MySQL开发人员添加了独占锁，这个问题也无法避免，因为他们无法在开始的SELECT验证时锁定行。

用唯一约束索引来解决

MySQL开发人员无法通过使用独占锁将并发操作变成顺序操作。因此，他们需要通过为表添加唯一约束让一项操作失败。

开发人员可以使用唯一约束索引来定位与房号和会议开始时间对应的房间预订表的列。该解决方案可以防止有人预订别人已经预订的时间段，开发人员可以做到这一点，实现没有人可以预订超过一小时的房间。

然而，如果两个用户的会议时间重叠，该解决方案也会阻止唯一约束发挥效果。为了正确解决问题，开发人员必须改用物化冲突。

通过物化冲突来解决

解决我们所讨论的幻读的正确方法是，发现表隐藏起来的冲突。开发人员可以使用协调并发操作的数据集，预先填充一个全新的表。如果我们以会议室系统为例，可以想象创建一个新表来指定时间段，并提前显示所有可用的时间段。

有了这个新表，开发人员现在将在指定可用时间段的列上执行SELECT，并添加FOR UPDATE，因为数据已经存在。开发人员需要在初始SELECT之前运行这个SELECT FOR UPDATE。

通过物化上述例子中的冲突，开发人员可以使用独占锁，阻止任何两个预订的时间段彼此重叠，从而迫使一个时间段在另一个时间段之前或之后添加。由于已完成第一个时间段，后面的时间段将立即失效。

结论

虽然物化冲突是一种难度大、不直观的解决方案，但在使用MySQL数据库时，有必要避免性能出现大幅下降。遗憾的是，MySQL的InnoDB隔离级别不可序列化，因此开发人员需要牺牲一定程度的复杂性，以获得可接受的性能级别。

任何使用数据库的人都必须了解相应数据库的功能以及解决方案。否则，将无法预测该数据库的哪些行为可能危及数据库设计和开发工作。

此外，了解如何适当地处理潜在风险同样重要。虽然我们在本文中通过时间预订系统所描述的用例与其他用例不一样，但展示出来的模式非常相似，因此了解如何解决它们有助于将来更容易处理其他情形。

原文标题：​​The Definitive Guide to Solving the Phantom Read in MySQL​​​，作者：Nahla Davies​