MySQL

时间：2024-12-12 20:23:18

MySQL事务隔离级别详解

SQL 标准定义了四个隔离级别：

  • READ-UNCOMMITTED(读取未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
  • READ-COMMITTED(读取已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
  • REPEATABLE-READ(可重复读) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
  • SERIALIZABLE(可串行化) ：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看，MySQL 8.0 该命令改为SELECT @@transaction_isolation;

MySQL> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

从上面对 SQL 标准定义了四个隔离级别的介绍可以看出，标准的 SQL 隔离级别定义里，REPEATABLE-READ(可重复读)是不可以防止幻读的。

但是！InnoDB 实现的 REPEATABLE-READ 隔离级别其实是可以解决幻读问题发生的，主要有下面两种情况：

  • 快照读：由 MVCC 机制来保证不出现幻读。
  • 当前读：使用 Next-Key Lock 进行加锁来保证不出现幻读，Next-Key Lock 是行锁（Record Lock）和间隙锁（Gap Lock）的结合，行锁只能锁住已经存在的行，为了避免插入新行，需要依赖间隙锁。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 READ-COMMITTED ，但是你要知道的是 InnoDB 存储引擎默认使用 REPEATABLE-READ 并不会有任何性能损失。

InnoDB 存储引擎在分布式事务的情况下一般会用到 SERIALIZABLE 隔离级别。

在下面我会使用 2 个命令行 MySQL ，模拟多线程（多事务）对同一份数据的脏读问题。

MySQL 命令行的默认配置中事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。如果要显式地开启一个事务需要使用命令：START TRANSACTION。

我们可以通过下面的命令来设置隔离级别。

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]

我们再来看一下我们在下面实际操作中使用到的一些并发控制语句:

  • START TRANSACTION |BEGIN：显式地开启一个事务。
  • COMMIT：提交事务，使得对数据库做的所有修改成为永久性。
  • ROLLBACK：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。

MySQL事务隔离级别用于控制一个事务对其他并发事务的可见性。它确保了数据库在多用户环境下的一致性和正确性。MySQL提供了四种标准的事务隔离级别，它们分别是：

1. **读未提交（Read Uncommitted）**
   - 这是最低的隔离级别，在这种级别下，事务中的修改，即使还没有提交，对其他事务也都是可见的。
   - 问题：会导致脏读（Dirty Read），即一个事务可以读取到另一个事务未提交的数据。

2. **读已提交（Read Committed）**
   - 在这种隔离级别下，一个事务只能看到其他事务已经提交的数据。
   - 优点：避免了脏读。
   - 问题：仍然可能发生不可重复读（Non-repeatable Read），即同一个事务中两次读取同样的记录，结果可能不同，因为在这两次读取之间，另一个事务可能修改了这条记录并提交了更改。

3. **可重复读（Repeatable Read）**
   - 这是MySQL默认的隔离级别。在这种级别下，保证了在一个事务内多次读取同一数据的结果是一致的，即使另一个事务在此期间修改了该数据并提交。
   - 优点：解决了不可重复读的问题。
   - 问题：幻读（Phantom Read）仍然可能发生，即当事务执行相同的查询时，可能会得到不同的结果集，因为在两次查询之间，另一个事务可能插入或删除了一些行。

4. **可序列化（Serializable）**
   - 这是最高的隔离级别，在这个级别下，事务被完全串行化执行，即所有事务依次执行，不会发生并发。
   - 优点：完全避免了脏读、不可重复读和幻读。
   - 问题：性能开销最大，因为这通常需要锁定整个表来防止其他事务的并发访问，降低了系统的并发处理能力。

### 设置隔离级别

你可以使用以下SQL语句来设置当前会话或全局的事务隔离级别：

```sql
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};
```

例如，要将当前会话的隔离级别设置为`REPEATABLE READ`，你可以执行如下命令：

```sql
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
```

如果你想要查看当前的隔离级别，可以使用以下命令：

```sql
SELECT @@session.tx_isolation;
-- 或者
SELECT @@global.tx_isolation;
```

这些命令分别返回当前会话和全局的事务隔离级别。请注意，不同的存储引擎可能有不同的默认隔离级别。例如，InnoDB默认使用`REPEATABLE READ`，而MyISAM不支持事务，因此没有隔离级别的概念。

InnoDB存储引擎对MVCC的实现

MVCC 是一种并发控制机制，用于在多个并发事务同时读写数据库时保持数据的一致性和隔离性。它是通过在每个数据行上维护多个版本的数据来实现的。当一个事务要对数据库中的数据进行修改时，MVCC 会为该事务创建一个数据快照，而不是直接修改实际的数据行。

1、读操作（SELECT）：

当一个事务执行读操作时，它会使用快照读取。快照读取是基于事务开始时数据库中的状态创建的，因此事务不会读取其他事务尚未提交的修改。具体工作情况如下：

  • 对于读取操作，事务会查找符合条件的数据行，并选择符合其事务开始时间的数据版本进行读取。
  • 如果某个数据行有多个版本，事务会选择不晚于其开始时间的最新版本，确保事务只读取在它开始之前已经存在的数据。
  • 事务读取的是快照数据，因此其他并发事务对数据行的修改不会影响当前事务的读取操作。

2、写操作（INSERT、UPDATE、DELETE）：

当一个事务执行写操作时，它会生成一个新的数据版本，并将修改后的数据写入数据库。具体工作情况如下：

  • 对于写操作，事务会为要修改的数据行创建一个新的版本，并将修改后的数据写入新版本。
  • 新版本的数据会带有当前事务的版本号，以便其他事务能够正确读取相应版本的数据。
  • 原始版本的数据仍然存在，供其他事务使用快照读取，这保证了其他事务不受当前事务的写操作影响。

3、事务提交和回滚：

  • 当一个事务提交时，它所做的修改将成为数据库的最新版本，并且对其他事务可见。
  • 当一个事务回滚时，它所做的修改将被撤销，对其他事务不可见。

4、版本的回收：

为了防止数据库中的版本无限增长，MVCC 会定期进行版本的回收。回收机制会删除已经不再需要的旧版本数据，从而释放空间。

MVCC 通过创建数据的多个版本和使用快照读取来实现并发控制。读操作使用旧版本数据的快照，写操作创建新版本，并确保原始版本仍然可用。这样，不同的事务可以在一定程度上并发执行，而不会相互干扰，从而提高了数据库的并发性能和数据一致性。

一致性非锁定读和锁定读

对于 一致性非锁定读（Consistent Nonlocking Reads）的实现，通常做法是加一个版本号或者时间戳字段，在更新数据的同时版本号 + 1 或者更新时间戳。查询时，将当前可见的版本号与对应记录的版本号进行比对，如果记录的版本小于可见版本，则表示该记录可见

在 InnoDB 存储引擎中，多版本控制 (multi versioning) 就是对非锁定读的实现。如果读取的行正在执行 DELETE 或 UPDATE 操作，这时读取操作不会去等待行上锁的释放。相反地，InnoDB 存储引擎会去读取行的一个快照数据，对于这种读取历史数据的方式，我们叫它快照读 (snapshot read)

在 Repeatable Read 和 Read Committed 两个隔离级别下，如果是执行普通的 select 语句（不包括 select ... lock in share mode ,select ... for update）则会使用 一致性非锁定读（MVCC）。并且在 Repeatable Read 下 MVCC 实现了可重复读和防止部分幻读

如果执行的是下列语句，就是 锁定读（Locking Reads）

  • select ... lock in share mode
  • select ... for update
  • insert、update、delete 操作

在锁定读下，读取的是数据的最新版本，这种读也被称为 当前读（current read）。锁定读会对读取到的记录加锁：

  • select ... lock in share mode：对记录加 S 锁，其它事务也可以加S锁，如果加 x 锁则会被阻塞

  • select ... for update、insert、update、delete：对记录加 X 锁，且其它事务不能加任何锁

在一致性非锁定读下，即使读取的记录已被其它事务加上 X 锁，这时记录也是可以被读取的，即读取的快照数据。上面说了，在 Repeatable Read 下 MVCC 防止了部分幻读，这边的 “部分” 是指在 一致性非锁定读 情况下，只能读取到第一次查询之前所插入的数据（根据 Read View 判断数据可见性，Read View 在第一次查询时生成）。但是！如果是 当前读 ，每次读取的都是最新数据，这时如果两次查询中间有其它事务插入数据，就会产生幻读。所以， InnoDB 在实现Repeatable Read 时，如果执行的是当前读，则会对读取的记录使用 Next-key Lock ，来防止其它事务在间隙间插入数据

MVCC 的实现依赖于：隐藏字段、Read View、undo log。在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改

在内部，InnoDB 存储引擎为每行数据添加了三个 隐藏字段：

  • DB_TRX_ID（6字节）：表示最后一次插入或更新该行的事务 id。此外，delete 操作在内部被视为更新，只不过会在记录头 Record header 中的 deleted_flag 字段将其标记为已删除
  • DB_ROLL_PTR（7字节） 回滚指针，指向该行的 undo log 。如果该行未被更新，则为空
  • DB_ROW_ID（6字节）：如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索引

相关文章