10_有了行级锁，RR级别下为什么还会幻读

最新推荐文章于 2024-09-30 17:19:22 发布

大王只是带我巡了个山

最新推荐文章于 2024-09-30 17:19:22 发布

阅读量855

点赞数 10

分类专栏： MySQL#锁机制文章标签： MySQL 行级锁幻读 RR 可重复读隔离级别数据库

本文链接：https://blog.csdn.net/Youth_Mr6/article/details/142554352

版权

10 篇文章 0 订阅

订阅专栏

通过了解间隙锁、临键锁以及临键锁，我们知道行级锁的加锁基本单位是临键锁，而临键锁又是间隙锁和临键锁的组合，这三种锁都可以用来避免幻读的问题，但是 MySQL 在 Repeatable Read（可重复读）这个事务隔离级别下，还是会出现幻读现象，为什么？

幻读是指一个事务在两次读取相同范围的记录时，发现范围内有新的记录被插入，这会导致两次读取结果不一致。为了防止幻读，InnoDB 在 Repeatable Read 隔离级别下采用了以下机制：

尽管 Repeatable Read 使用了这些锁机制，仍然有一些情况可能导致幻读现象：

非索引列的更新：
- 间隙锁和临键锁主要作用于索引列。如果查询条件中包含非索引列，InnoDB 可能无法有效地使用间隙锁和临键锁，从而导致幻读。
- 例如，假设有一个表 example
```
CREATE TABLE example (
    id INT PRIMARY KEY,
    value VARCHAR(50),
    category VARCHAR(50)
);
```
  如果在非索引列 category 上进行查询，InnoDB 不能对非索引列的范围进行锁定，从而可能出现幻读。
覆盖索引的情况：
- 如果一个查询使用了覆盖索引，即查询只访问索引而不访问数据页，锁机制可能不会锁定所有相关的间隙，从而可能导致幻读。
间隙锁的不完全覆盖：
- 在某些复杂的查询情况下，间隙锁可能不会完全覆盖所有可能的插入点。
- 例如，范围查询时，如果查询范围不包括某些索引值，其他事务可能在这些未锁定的间隙中插入新记录。
自增主键导致的幻读：
- 当表使用自增主键时，如果一个事务在一个特定范围内进行查询，另一个事务在这个范围之外插入新记录，自增主键的生成可能会引发幻读。

假设有一个表 example，包含以下数据：

CREATE TABLE example (
    id INT PRIMARY KEY,
    value VARCHAR(50)
);
INSERT INTO example (id, value) VALUES (5, 'A'), (10, 'B'), (15, 'C');

事务A：

START TRANSACTION;
SELECT * FROM example WHERE value = 'B' FOR UPDATE;

事务B：

START TRANSACTION;
INSERT INTO example (id, value) VALUES (20, 'B');
COMMIT;

在这种情况下，事务A的查询是在非索引列value上进行的，所以InnoDB不能对非索引列value的范围进行有效的间隙锁和临键锁，因此可能会出现幻读。

事务A：

START TRANSACTION;
SELECT * FROM example WHERE id BETWEEN 5 AND 15 FOR UPDATE;

事务B：

START TRANSACTION;
INSERT INTO example (id, value) VALUES (16, 'D');
COMMIT;

虽然事务A对id范围进行了锁定，但是由于事务B在锁定范围之外插入了新的记录，这可能导致事务A在后续的查询中看到新的记录，从而出现幻读。

为了防止幻读，可以考虑以下方法：

使用更高的隔离级别：将隔离级别提升为 Serializable，它会对每一个读取的行加锁，从而完全避免幻读。
```
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
```
使用索引：确保查询条件中的列都建立了索引，以便InnoDB能够有效地使用间隙锁和临键锁。
适当的表设计：在设计表结构时，考虑到可能的并发访问模式，避免过多依赖非索引列的查询。