数据库的隔离级别

原创于 2025-06-21 17:31:34 发布 · 1.3k 阅读

18 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

1.为什么需要隔离级别

当多个事务同时操作数据库并且没有适当的隔离，可能会出现以下问题：

1.脏读：
- A事务读取了B事务未提交的数据，并且B事务未提交数据回滚，此时A读取的数据就是脏数据。
2.不可重复度：
- 同一个事务两次读取同一行数据得到的结果不一样
- 例子：事务B 第一次读取某条记录的值是100。此时事务A 修改了该记录为200并提交。事务B 再次读取同一记录，值变成了200。
3.幻读：
- 同一个事务中读取多次同一个范围的数据结果不一样。
- 例子：事务B 第一次查询 age > 30 的员工，返回10条记录。此时事务A 插入了一条 age=35 的新员工记录并提交。事务B 再次执行相同的查询，返回了11条记录。

2.事务的隔离级别

1.读未提交

特征：一个事务可以读取到另一个尚未提交的事务所做的修改。
问题：脏读、不可重复读、幻读
原理：事务在读数据的时候并未对数据加锁，事务在修改数据的时候只对数据增加行级共享锁。
现象：当线程A读取数据时不加锁，其他事务也可以读和写本行数据。当线程A更新该行数据时对该行数据加共享锁，其他线程可以读该行数据但是不可以写该行数据。

2.读已提交

特征：一个事务只能读取到另一个事务已经提交的数据。这是大多数主流数据库的默认隔离级别 (如 Oracle, PostgreSQL, SQL Server)。
解决：幻读
问题：不可重复读、幻读
原理：事务对当前被读取的数据加行级共享锁（当读到时才加锁），一旦读完该行，立即释放该行级共享锁；事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁，直到事务结束才释放。
现象：当事务A读取这行数据时，对该行数据加行级共享锁，其他事务可以读不可以改。当事务A修改该行数据时对该行数据加排它锁，其他事务不能访问该行数据

3.可重复读

特征：保证在同一个事务内，多次读取同一行数据的结果是一致的。MySQL 的默认隔离级别。
解决：不可重复读，脏读
问题：幻读
原理：事务在开始读取某行数据时就对该行数据加共享锁直到事务结束才释放锁。事务在开始修改某行数据时就对该行数据加排它锁直到事务结束才释放锁。
现象：在事务A读取某一行数据时事务A对该行数据加共享锁直到事务结束才释放锁，其他事务可读取该行数据，但是不能修改该行数据直到事务A结束才能修改该行数据。事务A在修改某一行数据时对该行数据加排它锁直到事务结束才释放锁，其他事务不可以在此期间读取和修改该行数据，直到A事务结束。

4.可序列化

特征：最高的隔离级别。强制事务串行执行（如同一个接一个地执行，没有并发）。它通过加锁或其他机制（如严格的版本排序）完全避免了脏读、不可重复读和幻读。
解决：脏读、不可重复读、幻读
原理：事务在读取数据时，必须先对其加表级共享锁，直到事务结束才释放；事务在更新数据时，必须先对其加表级排他锁，直到事务结束才释放。
现象：事务1正在读取A表中的记录时，则事务2也能读取A表，但不能对A表做更新、新增、删除，直到事务1结束。(因为事务一对表增加了表级共享锁，其他事务只能增加共享锁读取数据，不能进行其他任何操作）。
事务1正在更新A表中的记录时，则事务2不能读取A表的任意记录，更不可能对A表做更新、新增、删除，直到事务1结束。（事务一对表增加了表级排他锁，其他事务不能对表增加共享锁或排他锁，也就无法进行任何操作）

3.MySQL怎么解决幻读和实现可重复读的

可重复度解决：
MySQL 使用 MVCC（多版本并发控制）来实现可重复读。每个事务在读取数据时，都会看到一个一致的快照版本，这个快照版本是事务开始时数据库的状态。当事务一个读取某行数据时，MySQL 会记录该行的版本号，并在后续读取时始终使用这个版本号，即使其他事务对该行进行了修改。通过这种方式，即使其他事务修改了数据，当前事务仍然可以看到一致的版本，从而保证了可重复读。
幻读解决：
在可重复读隔离级别下，MySQL 通过 Next-Key Lock 来避免幻读。Next-Key Lock 是一种组合锁，它结合了行锁和间隙锁。行锁用于锁定具体的行，而间隙锁用于锁定行之间的间隙，防止其他事务插入新的行。当一个事务读取某个范围的数据时，MySQL 会锁定这个范围内的所有行，以及行之间的间隙，从而防止其他事务插入新的行，避免了幻读。
- 临键锁 (Next-Key Lock): 锁住某个索引记录以及该索引记录之前的间隙。它锁定的是一个左开右闭的区间。
  - 例如，假设表中有索引值 10, 20, 30。
  - 对索引值 20 加临键锁，实际锁定的范围是 (10, 20] (大于10且小于等于20)。
  - 对索引值 30 加临键锁，锁定的范围是 (20, 30] (大于20且小于等于30)。
  - 对于第一个记录之前的“间隙”，临键锁会锁定 (-∞, first_key]。
  - 对于最后一个记录之后的“间隙”，临键锁会锁定 (last_key, +∞) (这是一个特殊的“间隙锁”，称为“supremum”伪记录)。
- 防止幻读的过程:
  - 锁的过程：
    1.InnoDB 根据查询条件（WHERE 子句）扫描索引。
    2.对于扫描到的每一行匹配的记录，加上记录锁。
    3.对于扫描过程中经过的每一个索引间隙，加上间隙锁。实际上，在加锁时，InnoDB 默认使用的就是临键锁，它同时锁定了记录和记录前面的间隙。
  - 例子：
  - 假设有一个表 users，有一个 id 主键索引和一个 age 非唯一索引。当前数据：
    id: 5 (age=20), id: 10 (age=25), id: 15 (age=30), id: 20 (age=35)。

START TRANSACTION;
SELECT * FROM users WHERE age BETWEEN 25 AND 30 FOR UPDATE; -- 使用`age`索引进行范围查询

1.查询找到 id=10 (age=25) 和 id=15 (age=30)。
2.InnoDB 会对 age 索引加临键锁：
对于 age=25 的记录：锁定间隙 (-∞, 25] (假设前一个age是20) 和记录25 -> (20, 25]
对于 age=30 的记录：锁定间隙 (25, 30] 和记录30 -> (25, 30]
因此，锁定的 age 索引范围实际上是 (20, 30] (大于20且小于等于30)。

4.MVCC

MVCC（多版本并发控制）实现可重复读（Repeatable Read）的核心在于：为每个事务提供一份在其开始时确定的、一致的数据库“快照”（Snapshot）。在整个事务期间，所有读操作都基于这个快照，因此即使其他事务修改并提交了数据，该事务看到的始终是开始时的数据状态，从而避免了不可重复读和快照读的幻读问题。
以下是 MVCC 在可重复读级别下实现的具体原理（以 MySQL InnoDB 为例）：
1.隐藏字段:

InnoDB 在每个聚簇索引记录（数据行）中存储两个隐藏的系统字段：
- DB_TRX_ID (6 Bytes)：记录最后一次修改该行数据的事务 ID。当一个事务插入或更新一行时，它会将自己的事务 ID 写入该行的 DB_TRX_ID。
- DB_ROLL_PTR (7 Bytes)：指向该行数据前一个版本的指针（回滚指针）。这个指针指向 undo log 中的一条记录。通过这个指针，可以追溯该行数据的历史版本。
  2.Undo Log:
当数据被修改（UPDATE 或 DELETE）时，InnoDB 会先将数据的旧版本（修改前的数据）写入 undo log 中。
每个旧版本记录都包含：
- 被修改前的数据内容。
- 该版本创建时的事务 ID (DB_TRX_ID)。
- 指向更早版本的 DB_ROLL_PTR 指针（形成版本链）。
INSERT 操作对应的 undo log 在事务回滚时用于删除新插入的行，因此 MVCC 快照读通常不需要访问 INSERT 的 undo log。
UPDATE 和 DELETE 操作对应的 undo log 用于构建数据行的历史版本链，是 MVCC 读取旧版本数据的基础。
3.Read View（读视图）：
这是实现快照的关键数据结构。在事务执行第一个 SELECT 语句时（或显式声明 START TRANSACTION WITH CONSISTENT SNAPSHOT 时），InnoDB 会为该事务创建一个 Read View。这个 Read View 决定了该事务在整个生命周期内能看到哪些版本的数据。
Read View 主要包含以下信息：
- m_ids：创建 Read View 时，系统内活跃（未提交）事务的事务 ID 列表。
- min_trx_id： m_ids 中的最小事务 ID。
- max_trx_id：创建 Read View 时，系统应该分配给下一个新事务的事务 ID（即当前最大事务 ID + 1）。
- creator_trx_id：创建该 Read View 的当前事务的事务 ID（如果当前事务是只读事务，则为 0）。
  4.MVCC 如何工作（快照读 - SELECT）
  当一个事务执行一个普通的 SELECT 语句（快照读）时：
1.访问目标行： InnoDB 找到目标数据行的最新版本（即聚簇索引中存储的当前版本）。
2.版本可见性检查：检查该最新版本的 DB_TRX_ID 字段，判断该版本对当前事务是否可见。判断依据基于当前事务的 Read View：

规则 1：如果 DB_TRX_ID < min_trx_id，说明该版本是在当前事务开始之前就已提交的，可见。

规则 2：如果 DB_TRX_ID >= max_trx_id，说明该版本是由在 Read View 创建之后才开始的事务修改的（即该事务在当前事务开始之后才启动），不可见。

规则 3：如果 min_trx_id <= DB_TRX_ID < max_trx_id，则需要进一步检查：

如果 DB_TRX_ID 在 m_ids 列表中，说明该版本是由创建 Read View 时还活跃（未提交）的事务修改的，不可见。
如果 DB_TRX_ID 不在 m_ids 列表中，说明该版本是由创建 Read View 时已经提交的事务修改的（该事务在创建 Read View 后提交了），可见。

规则 4：如果 DB_TRX_ID == creator_trx_id，说明该版本是当前事务自己修改的，可见（即使还没提交）。

3.沿版本链回溯：
- 如果根据规则判断最新版本不可见，则通过该行的 DB_ROLL_PTR 指针找到它的上一个历史版本（在 undo log 中）。
- 对上一个版本重复执行步骤 2：可见性检查。
- 重复此过程（沿着版本链不断回溯），直到找到一个对该事务可见的版本（符合规则 1 或 3b 或 4），或者回溯到版本链的开头（表示该行对当前事务完全不可见）。
- 返回数据：返回第一个找到的可见版本的数据。
  更详细MVCC可以看这个大佬的文章(我觉得讲的很好）：链接: 全网最详细 MVCC 讲解，一篇看懂