如何理解SQL的可重复读和幻读之间的区别

在数据库事务的世界中，事务隔离级别对于确保并发操作的可靠性至关重要。可重复读和幻读是两种隔离级别，它们对并发操作的影响有所不同。理解这两者之间的区别对于有效地设计和管理数据库应用程序非常重要。

可重复读的含义：

可重复读隔离级别保证在一个事务的持续时间内，即使其他并发事务修改了数据，读取操作也会返回相同的结果。换句话说，在事务期间，数据库状态对该事务是冻结的。

更详细地说，以下规则适用于可重复读隔离级别：

• 基于数据的快照：事务在开始时获取数据库的一个快照，并在事务期间保持该快照不变。
• 脏读保护：事务不会看到其他正在进行的事务对同一数据的未提交修改。
• 不可重复读保护：事务不会看到其他已提交的事务对同一数据的修改，但它可以多次读取相同数据的相同版本。

幻读的含义：

幻读隔离级别为可重复读隔离级别提供了进一步的保护，它防止了以下情况：

• 幻读：在一个事务期间，其他已提交的事务对数据进行了修改，导致在事务的后续读取操作中出现了先前不存在的新行。

与可重复读隔离级别相比，幻读隔离级别通过执行以下附加规则来防止幻读：

• 基于意向的锁定：事务在对其感兴趣的数据进行读取或修改时会获取意向锁。
• 幻读保护：事务不会看到其他已提交的事务对同一数据进行的插入或删除操作。

可重复读与幻读的区别：

虽然可重复读和幻读隔离级别都提供事务隔离，但它们之间的关键区别在于幻读保护：

• 可重复读：防止脏读和不可重复读，但允许幻读。
• 幻读：防止脏读、不可重复读和幻读。

选择合适的隔离级别：

选择合适的隔离级别取决于应用程序对数据一致性和并发性的要求：

• 需要最大并发性：可重复读隔离级别提供了一个很好的折衷方案，平衡了并发性与一致性。
• 需要严格一致性（没有幻读）：幻读隔离级别确保数据的绝对一致性，但可能会降低并发性。

性能影响：

幻读隔离级别比可重复读隔离级别更严格，因为它需要额外的意向锁定。因此，幻读隔离级别通常会对性能产生更大的影响，尤其是在并发写入操作频繁的情况下。

总结：

可重复读和幻读隔离级别是 SQL 中重要的概念，它们对并发操作的行为有重大影响。可重复读防止脏读和不可重复读，而幻读提供进一步的保护以防止幻读。选择合适的隔离级别对于确保数据库应用程序的可靠性和性能至关重要。

在深入探讨可重复读和幻读之间区别之前，让我们先回顾一下基础。当我们执行一个SQL查询时，数据库会生成一个快照视图，代表查询运行时数据库中的数据状态。此快照用于查询持续期间内，即使其他事务正在修改数据。

可重复读

当隔离级别设置为可重复读时，数据库保证在一个事务内，任何对同一数据的后续查询都会看到该事务开始时数据的相同快照。这意味着，其他事务的所有修改都对当前事务不可见。

优点：

• 一致性：事务内始终读取相同的数据快照，避免了读取脏数据的风险。
• 避免并发问题：当前事务不会受到其他事务修改的影响，从而消除了死锁和数据竞争。

缺点：

• 性能开销：保持一致性需要额外的资源和锁机制，可能会导致性能下降。

幻读

当隔离级别设置为可重复读时，仍然可能发生幻读。幻读是指在一个事务中，对同一查询重复执行两次，但第二次执行的结果集包含了在第一次执行后插入的新行。这是因为可重复读隔离只保证当前事务不会看到其他事务的修改，但不保证其他事务不会在当前事务执行期间插入新数据。

示例：

考虑以下两个事务：

事务A：

1. SELECT * FROM users WHERE name LIKE ‘John%’
2. UPDATE users SET age = 30 WHERE name = ‘John Doe’

事务B：

1. INSERT INTO users (name, age) VALUES (‘John Smith’, 25)

事务B在事务A的第一个查询之后和第二个更新之前插入了一条新记录。因此，当事务A第二次执行查询时，它将包含’John Smith’作为结果，即使在事务A开始时该记录不存在。

避免幻读：

避免幻读的唯一方法是使用更高级别的隔离级别，例如串行化。串行化确保事务按照一个接一个的顺序执行，没有并发。这消除了幻读，但也带来了更高的性能开销。

权衡选择：

可重复读和幻读之间的选择取决于应用程序的需要和对一致性和性能的权衡。对于需要高一致性的应用程序，可重复读是一个不错的选择。对于需要高性能且可以容忍幻读的应用程序，可重复读可能会更合适。

结论：

可重复读和幻读是SQL隔离级别中的两个关键概念。了解它们的差异对于设计和实施高性能和一致的数据库应用程序至关重要。通过仔细权衡每种隔离级别的优点和缺点，我们可以选择最适合特定应用程序需求的隔离级别。

在数据库的世界里，并发性是家常便饭。当多个用户同时访问同一份数据时，就需要某种机制来确保数据的一致性。SQL 引入了两个关键的隔离级别——可重复读和幻读——来应对这些挑战。

可重复读

可重复读是一种隔离级别，它保证在一个事务期间，同一个查询将始终返回相同的结果。换句话说，它防止了其他事务在你的事务过程中修改数据，从而导致你看到不同的数据。

可重复读隔离级别一般通过使用行锁来实现。当一个事务读取一行数据时，它会在该行上放置一个排他锁。这可防止其他事务同时修改或删除该行，从而确保了该事务在整个过程中看到相同的数据。

幻读

幻读是一种隔离级别，它保证在一个事务期间，查询将始终返回在事务开始时已经存在的所有行。换句话说，它防止了其他事务在你的事务过程中插入新行，从而导致你看到虚构的“幻影行”。

幻读隔离级别通常通过使用范围锁来实现。当一个事务读取一行数据时，它会在数据表中对应行的范围上放置一个共享锁。这可防止其他事务在该范围内插入新行，从而确保了该事务在整个过程中看到相同的数据集。

可重复读与幻读的区别

虽然可重复读和幻读都旨在确保并发访问时的数据一致性，但它们之间有一些关键区别：

• 范围：可重复读防止了数据修改，而幻读防止了数据插入。
• 锁定机制：可重复读使用行锁，而幻读使用范围锁。
• 性能：可重复读通常比幻读性能稍差，因为它需要在行级别进行锁定。

何时选择可重复读或幻读

选择最合适的隔离级别取决于应用程序的需求。对于需要高度一致性的应用程序，例如银行交易处理，可重复读隔离级别是最佳选择。对于允许一定程度的不一致性但需要较高性能的应用程序，幻读隔离级别可能是更好的选择。

示例

为了更好地理解这两个隔离级别的区别，让我们考虑一个示例：

有一个银行账户表，其中包含两列：account_id 和 balance。

• 可重复读：当一个事务读取一个账户的余额时，它会在该行上放置一个排他锁。如果另一个事务在此期间尝试修改该余额，它将被阻止，直到第一个事务完成。这样可以确保第一个事务始终看到相同的余额值。
• 幻读：当一个事务读取所有账户的余额时，它会在该表上放置一个共享锁。如果在此期间另一个事务向表中插入一个新账户，则第一个事务将无法看到该新账户。这可能会导致第一个事务对数据库中账户总数的计算不正确。

总结

可重复读和幻读隔离级别提供了不同的方式来处理并发数据访问时的数据一致性。理解它们的差异对于选择最适合特定应用程序需求的级别至关重要。可重复读对于需要高一致性的应用程序很有效，而幻读对于允许一定程度不一致性但需要更高性能的应用程序更合适。