MySQL 事务

事务（Transaction）是数据库管理系统（DBMS）中的一个重要概念，用于确保数据库中数据的一致性、可靠性和完整性。它是由一系列数据库操作组成的逻辑单位，这些操作要么全部执行成功，要么全部不执行，不允许部分执行。如果事务中的任何操作失败，整个事务将回滚（Rollback）到最初的状态，就像没有执行过一样，保持数据的一致性

事务的特性

事务通常有以下四个标准特性，通常称为 ACID 特性：

  1. 原子性（Atomicity）：事务是原子的，要么全部执行，要么全部不执行。如果事务中的任何操作失败，整个事务将被回滚，保持数据库的一致性

    就好比买一件商品，购买成功时，则给商家付了钱，商品到手；购买失败时，则商品在商家手中，消费者的钱也没花出去

  2. 一致性（Consistency）：事务开始之前和结束之后，数据库的状态应该保持一致。这意味着事务的执行不会破坏数据库的完整性约束，如唯一性、外键约束等

    比如，用户 A 和用户 B 在银行分别有 800 元和 600 元，总共 1400 元，用户 A 给用户 B 转账 200 元，分为两个步骤，从 A 的账户扣除 200 元和对 B 的账户增加 200 元。一致性就是要求上述步骤操作后，最后的结果是用户 A 还有 600 元，用户 B 有 800 元，总共 1400 元，而不会出现用户 A 扣除了 200 元，但用户 B 未增加的情况（该情况，用户 A 和 B 均为 600 元，总共 1200 元）

  3. 隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，因为多个事务同时使用相同的数据时，不会相互干扰，每个事务都有一个完整的数据空间，对其他并发事务是隔离的

    也就是说，消费者购买商品这个事务，是不影响其他消费者购买的

  4. 持久性（Durability）：一旦事务提交成功，其所做的修改将永久保存在数据库中，即使发生系统故障或崩溃，也不会丢失提交的数据

先写在前面

InnoDB 引擎通过什么技术来保证事务的这四个特性的呢？

  • 持久性是通过 redo log （重做日志）来保证的
  • 原子性是通过 undo log（回滚日志） 来保证的
  • 隔离性是通过 MVCC（多版本并发控制） 或锁机制来保证的
  • 一致性则是通过持久性 + 原子性 + 隔离性来保证

并发事务

MySQL 服务端是允许多个客户端连接的，这意味着 MySQL 会出现同时处理多个事务的情况

那么在同时处理多个事务的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题

脏读：脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读

不可重复读：对比可重复读，不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据更新操作

幻读：幻读是针对数据插入操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，让用户感觉感觉出现了幻觉，这就叫幻读

可重复读：可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的

事务隔离级别

事务的隔离级别是数据库管理系统中用于控制多个并发事务之间相互影响程度的一种机制。由于数据库系统常常有多个用户同时访问和修改数据，为了保证事务的隔离性和数据的一致性，引入了不同的隔离级别。数据库管理系统通常支持以下四个标准的隔离级别：

  1. 读未提交（Read Uncommitted）：这是最低的隔离级别。在该级别下，一个事务可以读取到另一个事务尚未提交的修改。这可能导致脏读（Dirty Read），即读取到未经确认的数据，可能是无效的数据

  2. 读提交（Read Committed）：在该级别下，一个事务只能读取到已经提交的数据。这避免了脏读，但可能会导致不可重复读（Non-repeatable Read）。不可重复读指的是，在同一个事务中，由于其他事务的修改，同一数据行的值可能会发生变化

  3. 可重复读（Repeatable Read）：在该级别下，一个事务在执行期间多次读取同一数据，将看到一致的结果。其他事务修改数据时，不会影响当前事务的读取结果。但是，仍然可能发生幻读（Phantom Read）。幻读指的是在同一个事务中，由于其他事务插入新数据，导致之前的查询结果发生变化

  4. 串行化（Serializable）：这是最高的隔离级别。在该级别下，所有事务按照顺序逐个执行，相当于将并发执行转换为串行执行。这样可以避免脏读、不可重复读和幻读，但并发性能会受到极大影响，通常只在特殊情况下使用

需要注意的是，隔离级别越高，事务的隔离性越好，但并发性能也会降低。因此，在选择隔离级别时，需要根据具体应用场景的要求进行权衡

不同的事务隔离级别下会有不同的现象

事务隔离级别 脏读 不可重复读 幻读
读未提交 是 是 是
不可重复读 否 是 是
可重复读 否 否 是
串行化 否 否 否

MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象，解决的方案有两种：

  • 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题
  • 针对当前读（select … for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题

并发事务控制

  • 读-读：即并发事务相继读取同一记录
  • 写-写：即并发事务相继对同一记录做出修改
  • 写-读或读-写：即两个并发事务对同一记录分别进行读操作和写操作
读-读

因为读取记录并不会对记录造成任何影响，所以同个事务并发读取同一记录也就不存在任何安全问题，所以允许这种操作。

写-写

如果允许并发事务都读取同一记录，并相继基于旧值对这一记录做出修改，那么就会出现前一个事务所做的修改被后面事务的修改覆盖，即出现提交覆盖的问题。

另外一种情况，并发事务相继对同一记录做出修改，其中一个事务提交之后之后另一个事务发生回滚，这样就会出现已提交的修改因为回滚而丢失的问题，即回滚覆盖问题。

这两种问题都造成丢失更新，其中回滚覆盖称为第一类丢失更新问题，提交覆盖称为第二类丢失更新问题

写-读或读-写

这种情况较为复杂，也最容易出现问题

如果一个事务读取了另一个事务尚未提交的修改记录，那么就出现了脏读的问题；

如果我们加以控制使得一个事务只能读取其他已提交事务的修改的数据，那么这个事务在另一事物提交修改前后读取到的数据是不一样的，这就意味着发生了不可重复读；

如果一个事务根据一些条件查询到一些记录，之后另一事物向表中插入了一些记录，原先的事务以相同条件再次查询时发现得到的结果跟第一次查询得到的结果不一致，这就意味着发生了幻读。

传统隔离级别的实现

锁 控制方式下会通过锁来显示控制共享资源而不是通过调度手段，MySQL 中主要是通过 读写锁 来实现并发控制。

  • 共享锁（Shared Locks）：简称 S锁，事务对一条记录进行读操作时，需要先获取该记录的共享锁
  • 排他锁（Exclusive Locks）：简称 X锁，事务对一条记录进行写操作时，需要先获取该记录的排他锁

读写锁可以做到读读并行，但是无法做到写读、写写并行。另外，根据根据锁粒度的不同，又被分为 表级锁 (table-level locking) 和 行级锁 (row-level locking) 。InnoDB 不光支持表级锁，还支持行级锁，默认为行级锁。行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类

在基于锁的实现方式下，四种隔离级别的区别就在于加锁方式的区别：

  • 读未提交：读操作不加锁，读读，读写，写读并行；写操作加X锁且直到事务提交后才释放
  • 读已提交：读操作加S锁，写操作加X锁且直到事务提交后才释放；读操作不会阻塞其他事务读或写，写操作会阻塞其他事务写和读，因此可以防止脏读问题
  • 可重复读：读操作加S锁且直到事务提交后才释放，写操作加X锁且直到事务提交后才释放；读操作不会阻塞其他事务读但会阻塞其他事务写，写操作会阻塞其他事务读和写，因此可以防止脏读、不可重复读
  • 串行化：读操作和写操作都加X锁且直到事务提交后才释放，粒度为表锁，也就是严格串行

值得注意的点

  • 如果锁获取之后直到事务提交后才释放，这种锁称为长锁；如果锁在操作完成之后就被释放，这种锁称为短锁。例如，在读已提交隔离级别下，读操作所加S锁为短锁，写操作所加X锁为长锁。
  • 对于可重复读和串行化隔离级别，读操作所加S锁和写操作所加X锁均为长锁，即事务获取锁之后直到事务提交后才能释放，这种把获取锁和释放锁分为两个不同的阶段的协议称为两阶段锁协议（2-phase locking）。两阶段锁协议规定在加锁阶段，一个事务可以获得锁但是不能释放锁；而在解锁阶段事务只可以释放锁，并不能获得新的锁。两阶段锁协议能够保证事务串行化执行，解决事务并发问题，但也会导致死锁发生的概率大大提升

MySQL innodb隔离级别的实现

不同数据库对于 SQL 标准中规定的隔离级别支持是不一样的，数据库引擎实现隔离级别的方式虽然都在尽可能地贴近标准的隔离级别规范，但和标准的预期还是有些不一样的地方。

MySQL（innodb）支持的4种隔离级别，与标准的各级隔离级别允许出现的问题有些出入，比如MySQL在可重复读隔离级别下可以防止幻读的问题出现，但也会出现提交覆盖的问题

相对于传统隔离级别基于锁的实现方式，MySQL 是通过MVCC（多版本并发控制）来实现读-写并发控制，又是通过两阶段锁来实现写-写并发控制的。MVCC是一种无锁方案，用以解决事务读-写并发的问题，能够极大提升读-写并发操作的性能

MVCC的实现原理

为了方便描述，首先我们创建一个表 book，就三个字段，分别是主键 book_id, 名称 book_name, 库存 stock。然后向表中插入一些数据：

1
2
3
INSERT INTO book VALUES(1, '数据结构', 100);
INSERT INTO book VALUES(2, 'C++指南', 100);
INSERT INTO book VALUES(3, '精通Java', 100);
版本链

对于使用 InnoDB 存储引擎的表，其聚簇索引记录中包含了两个重要的隐藏列：

  • 事务ID（DB_TRX_ID）：每当事务对聚簇索引中的记录进行修改时，都会把当前事务的事务id记录到 DB_TRX_ID 中
  • 回滚指针（DB_ROLL_PTR）：每当事务对聚簇索引中的记录进行修改时，都会把该记录的旧版本记录到 undo 日志中，通过 DB_ROLL_PTR 这个指针可以用来获取该记录旧版本的信息

如果在一个事务中多次对记录进行修改，则每次修改都会生成 undo 日志，并且这些 undo 日志通过 DB_ROLL_PTR 指针串联成一个版本链，版本链的头结点是该记录最新的值，尾结点是事务开始时的初始值

例如，我们在表 book 中做以下修改：

1
2
3
4
5
BEGIN;

UPDATE book SET stock = 200 WHERE id = 1;

UPDATE book SET stock = 300 WHERE id = 1;

那么 id=1 的记录此时的版本链就如下图所示：

img

ReadView

对于使用 Read Uncommitted 隔离级别的事务来说，只需要读取版本链上最新版本的记录即可；对于使用 Serializable 隔离级别的事务来说，InnoDB 使用加锁的方式来访问记录。而 Read Committed 和 Repeatable Read 隔离级别来说，都需要读取已经提交的事务所修改的记录，也就是说如果版本链中某个版本的修改没有提交，那么该版本的记录时不能被读取的。所以需要确定在 Read Committed 和 Repeatable Read 隔离级别下，版本链中哪个版本是能被当前事务读取的。于是 ReadView 的概念被提出以解决这个问题

ReadView 相当于某个时刻表记录的一个快照，在这个快照中我们能获取到与当前记录相关的事务中，哪些事务是已提交的稳定事务，哪些是正在活跃的事务，哪些是生成快照之后才开启的事务。由此我们就能根据可见性比较算法判断出版本链中能被读取的最新版本记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
class ReadView {
  /* ... */
private:
  trx_id_t m_low_limit_id;      /* 大于等于这个 ID 的事务均不可见 */

  trx_id_t m_up_limit_id;       /* 小于这个 ID 的事务均可见 */

  trx_id_t m_creator_trx_id;    /* 创建该 Read View 的事务ID */

  trx_id_t m_low_limit_no;      /* 事务 Number, 小于该 Number 的 Undo Logs 均可以被 Purge */

  ids_t m_ids;                  /* 创建 Read View 时的活跃事务列表 */

  m_closed;                     /* 标记 Read View 是否 close */
}

可见性比较算法是基于事务ID的比较算法。首先我们需要知道的一个事实是：事务 id 是递增分配的。从 ReadView 中我们能获取到生成快照时刻系统中活跃的事务中最小和最大的事务 id（最大的事务id实际上是系统中将要分配给下一个事务的 id 值），这样我们就得到了一个活跃事务 id 的范围，我们可称之为 ACTIVE_TRX_ID_RANGE。那么小于这个范围的事务id对应的事务都是已提交的稳定事务，大于这个范围的事务都是在快照生成之后才开启的事务，而在 ACTIVE_TRX_ID_RANGE 范围内的事务中除了正在活跃的事务，也都是已提交的稳定事务

有了以上信息之后，我们顺着版本链从头结点开始查找最新的可被读取的版本记录：

1、首先判断版本记录的 DB_TRX_ID 字段与生成 ReadView 的事务对应的事务ID是否相等。如果相等，那就说明该版本的记录是在当前事务中生成的，自然也就能够被当前事务读取；否则进行第2步

2、如果版本记录的 DB_TRX_ID 字段小于范围 ACTIVE_TRX_ID_RANGE，表明该版本记录是已提交事务修改的记录，即对当前事务可见；否则进行下一步

3、如果版本记录的 DB_TRX_ID 字段位于范围 ACTIVE_TRX_ID_RANGE 内，如果该事务ID对应的不是活跃事务，表明该版本记录是已提交事务修改的记录，即对当前事务可见；如果该事务ID对应的是活跃事务，那么对当前事务不可见，则读取版本链中下一个版本记录，重复以上步骤，直到找到对当前事务可见的版本

如果某个版本记录经过以上步骤判断确定其对当前事务可见，则查询结果返回此版本记录；否则读取下一个版本记录继续按照上述步骤进行判断，直到版本链的尾结点。如果遍历完版本链没有找到对当前事务可见的版本，则查询结果为空

在 MySQL 中，Read Committed 和 Repeatable Read 隔离级别下的区别就是它们生成 ReadView 的时机不同

MVCC实现不同隔离级别

之前说到 ReadView 的机制只在 Read Committed 和 Repeatable Read 隔离级别下生效，所以只有这两种隔离级别才有 MVCC。
在 Read Committed 隔离级别下，每次读取数据时都会生成 ReadView；而在 Repeatable Read 隔离级别下只会在事务首次读取数据时生成 ReadView，之后的读操作都会沿用此 ReadView。

下面我们通过例子来看看 Read Committed 和 Repeatable Read 隔离级别下 MVCC 的不同表现。我们继续以表 book 为例进行演示。

Read Committed 隔离级别分析

假设在 Read Committed 隔离级别下，有如下事务在执行，事务 id 为10：

1
2
3
4
5
BEGIN; // 开启Transaction 10

UPDATE book SET stock = 200 WHERE id = 2;

UPDATE book SET stock = 300 WHERE id = 2;

此时该事务尚未提交，id为2的记录版本链如下图所示：

img

然后我们开启一个事务对id为2的记录进行查询：

1
2
3
BEGIN;

SELECT * FROM book WHERE id = 2;

当执行 SELECT 语句时会生成一个 ReadView，该 ReadView 中的 ACTIVE_TRX_ID_RANGE 为 [10, 11)，当前事务ID creator_trx_id 为0（因为事务中当执行写操作时才会分配一个单独的事务 id，否则事务id为0）。按照我们之前所述 ReadView 的工作原理，我们查询到的版本记录为

1
2
3
4
5
+----------+-----------+-------+
| book_id  | book_name | stock |
+----------+-----------+-------+
| 2        | C++指南    |  100  |
+----------+-----------+-------+

然后我们将事务id为10的事务提交：

1
2
3
4
5
6
7
BEGIN; // 开启Transaction 10

UPDATE book SET stock = 200 WHERE id = 2;

UPDATE book SET stock = 300 WHERE id = 2;

COMMIT;

同时开启执行另一事务id为11的事务，但不提交：

1
2
3
BEGIN; // 开启Transaction 11

UPDATE book SET stock = 400 WHERE id = 2;

此时id为2的记录版本链如下图所示：

img

然后我们回到刚才的查询事务中再次查询id为2的记录：

1
2
3
4
5
BEGIN;

SELECT * FROM book WHERE id = 2; // 此时Transaction 10 未提交

SELECT * FROM book WHERE id = 2; // 此时Transaction 10 已提交

当第二次执行 SELECT 语句时会再次生成一个 ReadView，该 ReadView 中的 ACTIVE_TRX_ID_RANGE 为[11, 12)，当前事务ID creator_trx_id 依然为0。按照 ReadView 的工作原理进行分析，我们查询到的版本记录为

1
2
3
4
5
+----------+-----------+-------+
| book_id  | book_name | stock |
+----------+-----------+-------+
| 2        | C++指南    | 300   |
+----------+-----------+-------+

从上述分析可以发现，因为每次执行查询语句都会生成新的 ReadView，所以在 Read Committed 隔离级别下的事务读取到的是查询时刻表中已提交事务修改之后的数据。

Repeatable Read隔离级别分析

我们在 Repeatable Read 隔离级别下重复上面的事务操作：

1
2
3
4
5
BEGIN; // 开启Transaction 20

UPDATE book SET stock = 200 WHERE id = 2;

UPDATE book SET stock = 300 WHERE id = 2;

此时该事务尚未提交，然后我们开启一个事务对id为2的记录进行查询：

1
2
3
BEGIN;

SELECT * FROM book WHERE id = 2;

当事务第一次执行 SELECT 语句时会生成一个 ReadView，该 ReadView 中的 ACTIVE_TRX_ID_RANGE 为 [10, 11) ，当前事务ID creator_trx_id 为0。根据 ReadView 的工作原理，我们查询到的版本记录为

1
2
3
4
5
+----------+-----------+-------+
| book_id  | book_name | stock |
+----------+-----------+-------+
| 2        | C++指南    |  100  |
+----------+-----------+-------+

然后我们将事务 id 为20的事务提交：

1
2
3
4
5
6
7
BEGIN; // 开启Transaction 20

UPDATE book SET stock = 200 WHERE id = 2;

UPDATE book SET stock = 300 WHERE id = 2;

COMMIT;

同时开启执行另一事务id为21的事务，但不提交：

1
2
3
BEGIN; // 开启Transaction 21

UPDATE book SET stock = 400 WHERE id = 2;

然后我们回到刚才的查询事务中再次查询id为2的记录：

1
2
3
4
5
BEGIN;

SELECT * FROM book WHERE id = 2; // 此时Transaction 10 未提交

SELECT * FROM book WHERE id = 2; // 此时Transaction 10 已提交

当第二次执行 SELECT 语句时不会生成新的 ReadView，依然会使用第一次查询时生成 ReadView。因此我们查询到的版本记录跟第一次查询到的结果是一样的：

1
2
3
4
5
+----------+-----------+-------+
| book_id  | book_name | stock |
+----------+-----------+-------+
| 2        | C++指南    |  100  |
+----------+-----------+-------+

从上述分析可以发现，因为在 Repeatable Read 隔离级别下的事务只会在第一次执行查询时生成 ReadView，该事务中后续的查询操作都会沿用这个 ReadView，因此此隔离级别下一个事务中多次执行同样的查询，其结果都是一样的，这样就实现了可重复读。

快照读和当前读
快照读

在 Read Committed 和 Repeatable Read 隔离级别下，普通的 SELECT 查询都是读取 MVCC 版本链中的一个版本，相当于读取一个快照，因此称为快照读。这种读取方式不会加锁，因此读操作时非阻塞的，因此也叫非阻塞读。

在标准的 Repeatable Read 隔离级别下读操作会加 S锁，直到事务结束，因此可以阻止其他事务的写操作；但在 MySQL 的 Repeatable Read 隔离级别下读操作没有加锁，不会阻止其他事务对相同记录的写操作，因此在后续进行写操作时就有可能写入基于版本链中的旧数据计算得到的结果，这就导致了提交覆盖的问题。想要避免此问题，就需要另外加锁来实现。

当前读

之前提到 MySQL 有两种锁定读的方式：

1
2
3
SELECT ... LOCK IN SHARE MODE; // 读取时对记录加S锁，直到事务结束

SELECT ... FOR UPDATE; // 读取时对记录加X锁，直到事务结束

这种读取方式读取的是记录的当前最新版本，称为当前读。另外对于INSERT、DELETE、UPDATE操作，也是需要先读取记录，获取记录的X锁，这个过程也是一个当前读。由于需要对记录进行加锁，会阻塞其他事务的写操作，因此也叫加锁读或阻塞读。

当前读不仅会对当前记录加行记录锁，还会对查询范围空间的数据加间隙锁（GAP LOCK），因此可以阻止幻读问题的出现

快照读是如何避免幻读的？

可重复读隔离级是由 MVCC（多版本并发控制）实现的，实现的方式是开始事务后（执行 begin 语句后），在执行第一个查询语句后，会创建一个 Read View，后续的查询语句利用这个 Read View，通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的，即使中途有其他事务插入了新纪录，是查询不出来这条数据的，所以就很好了避免幻读问题。

做个实验，数据库表 t_stu 如下，其中 id 为主键。

img

然后在可重复读隔离级别下，有两个事务的执行顺序如下：

在这里插入图片描述

从这个实验结果可以看到，即使事务 B 中途插入了一条记录，事务 A 前后两次查询的结果集都是一样的，并没有出现所谓的幻读现象。

当前读是如何避免幻读的？

MySQL 里除了普通查询是快照读，其他都是当前读，比如 update、insert、delete，这些语句执行前都会查询最新版本的数据，然后再做进一步的操作。

这很好理解，假设你要 update 一个记录，另一个事务已经 delete 这条记录并且提交事务了，这样不是会产生冲突吗，所以 update 的时候肯定要知道最新的数据。

另外，select ... for update 这种查询语句是当前读，每次执行的时候都是读取最新的数据。

接下来，我们假设select ... for update当前读是不会加锁的（实际上是会加锁的），在做一遍实验。

img

这时候，事务 B 插入的记录，就会被事务 A 的第二条查询语句查询到（因为是当前读），这样就会出现前后两次查询的结果集合不一样，这就出现了幻读

所以，Innodb 引擎为了解决「可重复读」隔离级别使用「当前读」而造成的幻读问题，就引出了间隙锁

假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。

img

举个具体例子，场景如下：

img

事务 A 执行了这面这条锁定读语句后，就在对表中的记录加上 id 范围为 (2, +∞] 的 next-key lock（next-key lock 是间隙锁+记录锁的组合）

然后，事务 B 在执行插入语句的时候，判断到插入的位置被事务 A 加了 next-key lock，于是事物 B 会生成一个插入意向锁，同时进入等待状态，直到事务 A 提交了事务。这就避免了由于事务 B 插入新记录而导致事务 A 发生幻读的现象

幻读被完全解决了吗？

可重复读隔离级别下虽然很大程度上避免了幻读，但是还是没有能完全解决幻读。

我举例一个可重复读隔离级别发生幻读现象的场景。

第一个发生幻读现象的场景

还是以这张表作为例子：

img

事务 A 执行查询 id = 5 的记录，此时表中是没有该记录的，所以查询不出来。

1
2
3
4
5
6
# 事务 A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_stu where id = 5;
Empty set (0.01 sec)

然后事务 B 插入一条 id = 5 的记录，并且提交了事务。

1
2
3
4
5
6
7
8
9
# 事务 B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_stu values(5, '小美', 18);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

此时，事务 A 更新 id = 5 这条记录，对没错，事务 A 看不到 id = 5 这条记录，但是他去更新了这条记录，这场景确实很违和，然后再次查询 id = 5 的记录，事务 A 就能看到事务 B 插入的纪录了，幻读就是发生在这种违和的场景。

1
2
3
4
5
6
7
8
9
10
11
12
# 事务 A
mysql> update t_stu set name = '小林coding' where id = 5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_stu where id = 5;
+----+--------------+------+
| id | name         | age  |
+----+--------------+------+
|  5 | 小林coding   |   18 |
+----+--------------+------+
1 row in set (0.00 sec)

整个发生幻读的时序图如下：

img

在可重复读隔离级别下，事务 A 第一次执行普通的 select 语句时生成了一个 ReadView，之后事务 B 向表中新插入了一条 id = 5 的记录并提交。接着，事务 A 对 id = 5 这条记录进行了更新操作，在这个时刻，这条新记录的 trx_id 隐藏列的值就变成了事务 A 的事务 id，之后事务 A 再使用普通 select 语句去查询这条记录时就可以看到这条记录了，于是就发生了幻读。

因为这种特殊现象的存在，所以我们认为 MySQL Innodb 中的 MVCC 并不能完全避免幻读现象。

第二个发生幻读现象的场景

除了上面这一种场景会发生幻读现象之外，还有下面这个场景也会发生幻读现象。

  • T1 时刻：事务 A 先执行「快照读语句」：select * from t_test where id > 100 得到了 3 条记录。
  • T2 时刻：事务 B 往插入一个 id= 200 的记录并提交；
  • T3 时刻：事务 A 再执行「当前读语句」 select * from t_test where id > 100 for update 就会得到 4 条记录，此时也发生了幻读现象。

要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select … for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录

总结

MySQL InnoDB 引擎的可重复读隔离级别（默认隔离级），根据不同的查询方式，分别提出了避免幻读的方案：

  • 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读
  • 针对当前读（select … for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读

我举例了两个发生幻读场景的例子。

第一个例子：对于快照读， MVCC 并不能完全避免幻读现象。因为当事务 A 更新了一条事务 B 插入的记录，那么事务 A 前后两次查询的记录条目就不一样了，所以就发生幻读

第二个例子：对于当前读，如果事务开启后，并没有执行当前读，而是先快照读，然后这期间如果其他事务插入了一条记录，那么事务后续使用当前读进行查询的时候，就会发现两次查询的记录条目就不一样了，所以就发生幻读

所以，MySQL 可重复读隔离级别并没有彻底解决幻读，只是很大程度上避免了幻读现象的发生

要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select … for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录

Mysql > Transaction
#Mysql #Transaction
MySQL 事务
https://sugayoiya.github.io/posts/35975.html
作者
Sugayoiya
发布于
2021年10月25日
许可协议