咦~ MySQL怎么出现幻读啦!

数据库 MySQL
当前读可以通过锁机制完全避免幻读问题,快照读如果中间对其他事务已提交的插入或更新的数据进行了更新,则会出现幻读的问题。

1 背景

InnoDB默认的事务隔离级别是REPEATABLE-READ,它为了解决该隔离级别并发情况下的幻读问题,使用了LBCC(基于锁的并发控制)和MVCC(多版本的并发控制)两种方案。其中LBCC解决的是当前读情况下的幻读问题,MVCC解决的是快照读情况下的幻读问题,那既然如此,该隔离级别下是否仍然还存在幻读的问题呢?幻读问题到底有没有完全解决呢?基于这样的疑问,下面我们来进行验证下吧。

2 验证

2.1 验证准备

2.1.1 环境信息

MySQL版本:5.6.36
存储引擎:InnoDB
隔离级别:REPEATABLE-READ

2.1.2 数据准备

为了进行验证,在测试库建立了一张测试使用的用户信息表,并且插入了3条初始数据。

CREATE TABLE `user_info` (
 `id` BIGINT ( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键id',
 `name` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '姓名',
 `gender` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '性别',
 `email` VARCHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '邮箱',
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '用户信息表';

INSERT INTO `user_info` (`id`, `name`, `gender`, `email`) VALUES (1, 'Curry', '男', 'curry@163.com');
INSERT INTO `user_info` (`id`, `name`, `gender`, `email`) VALUES (2, 'Wade', '男', 'wade@163.com');
INSERT INTO `user_info` (`id`, `name`, `gender`, `email`) VALUES (3, 'James', '男', 'james@163.com');
commit;

2.2 当前读场景验证

首先我们先来看看当前读的场景下会不会出现幻读的问题。

2.2.1 前言

  • 什么是当前读

当前读(Locking Read)也称为锁定读,读取的是数据当前的最新版本,而且读取到这个数据之后会对这个数据加锁,防止别的事务进行更改,即通过next-key锁(唯一索引next-key锁会退化为记录锁)来解决当前读中的脏读,幻读,不可重复读问题,也就是LBCC的方式。在进行写操作的时候也需要进行“当前读”,读取数据记录的最新版本。当前读包含以下SQL类型:select ... lock in share mode 、select ... for update、update 、delete 、insert。

  • 什么是临键锁

图片图片

我们将数据库中存储的每一行数据称为记录。如上图中1、5、9、12分别代表id为当前数的记录。对于键值在条件范围内但不存在的记录,叫做间隙(GAP)。则上图中的(-∞,1)、(1,5)...(12,+∞)为数据库中存在的间隙。而(-∞,1]、(1,5]...(12,+∞)我们称之为临键,即左开右闭的集合。当我们对上面的记录和间隙共同加锁时,添加的便是临键锁。

2.2.2 场景验证

触发当前读的方式有很多种,这里仅使用select lock in share mode这种方式来进行当前读幻读问题验证。

  • 场景:
  1. 开启事务1
  2. 在事务1中通过select lock in share mode进行当前读查询用户信息
  3. 开启事务2
  4. 在事务2中插入一条新数据
  5. 提交事务2
  6. 在事务1中再次查询用户信息
  7. 提交事务1
mysql> START TRANSACTION; -- 1.开启事务1
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user_info lock in share mode; -- 2.读锁方式查询用户信息
+----+-------+--------+---------------+
| id | name  | gender | email         |
+----+-------+--------+---------------+
|  1 | Curry | 男     | curry@163.com |
|  2 | Wade  | 男     | wade@163.com  |
|  3 | James | 男     | james@163.com |
+----+-------+--------+---------------+
3 rows in set (0.00 sec)
mysql> START TRANSACTION; -- 3.开启事务2
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `user_info` (`id`, `name`, `gender`, `email`) VALUES (4, 'White', '男', 'white@163.com'); -- 4.在事务2中插入一条新数据

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction  -- 因锁等待插入未成功,最终等待超时,事务回滚终止
  • 场景验证结果

可以看到在事务1中开始事务执行了当前读后,事务2在进行插入新数据时进入了锁等待,最后发生了锁等待超时,导致事务终止回滚。插入数据因锁的原因是不会成功的,因此事务1第二次查询时也不会查询到新记录,所以此场景下不会产生幻读的问题。

2.2.3 小结

由场景验证结果可以看到,由于临键锁的存在,会阻塞其他事务对加锁间隙的数据插入,所以当前读场景下通过LBCC是可以完全解决幻读的问题。

2.3.快照读场景验证

那接下来我们再看看快照读场景下是怎么样的。

2.3.1 前言

  • 什么是快照读

由于当前读是通过LBCC基于锁的方式来进行并发控制,是悲观锁的实现,同时也会因为锁的原因,造成锁冲突的概率变大,也会导致性能的下降,因此基于提高并发性能的考虑,引入了快照读,快照读顾名思义即读取的是数据的快照版本,快照读的实现是基于MVCC多版本并发控制,它在很多情况下,避免了加锁操作,降低了性能开销。

2.3.2 场景验证

  • 场景一
  1. 开启事务1
  2. 在事务1中查询用户信息
  3. 开启事务2
  4. 在事务2中插入一条新数据
  5. 提交事务2
  6. 在事务1中再次查询用户信息
  7. 提交事务1
mysql> START TRANSACTION; -- 1.开启事务1
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user_info; -- 2.在事务1中查询用户信息
+----+-------+--------+---------------+
| id | name  | gender | email         |
+----+-------+--------+---------------+
|  1 | Curry | 男     | curry@163.com |
|  2 | Wade  | 男     | wade@163.com  |
|  3 | James | 男     | james@163.com |
+----+-------+--------+---------------+
3 rows in set (0.00 sec)
mysql> select * from user_info; 6.在事务1中再次查询用户信息
+----+-------+--------+---------------+
| id | name  | gender | email         |
+----+-------+--------+---------------+
|  1 | Curry | 男     | curry@163.com |
|  2 | Wade  | 男     | wade@163.com  |
|  3 | James | 男     | james@163.com |
+----+-------+--------+---------------+
3 rows in set (0.00 sec)
mysql> commit; -- 7.提交事务1
Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION; -- 3.开启事务2
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `user_info` (`id`, `name`, `gender`, `email`) VALUES (4, 'White', '男', 'white@163.com'); -- 4.在事务2中插入一条新数据
Query OK, 1 row affected (0.00 sec)

mysql> commit; -- 5.提交事务2
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user_info;
+----+-------+--------+---------------+
| id | name  | gender | email         |
+----+-------+--------+---------------+
|  1 | Curry | 男     | curry@163.com |
|  2 | Wade  | 男     | wade@163.com  |
|  3 | James | 男     | james@163.com |
|  4 | White | 男     | white@163.com |
+----+-------+--------+---------------+
4 rows in set (0.00 sec)
  • 场景验证结果

从场景一来看RR级别下是可以避免幻读的问题,在意料之中。那如果我们在事务1中两次查询之间进行了当前读更新操作呢,那会不会出现幻读的问题呢,那接下来我们来看一看场景二。

  • 场景二
  1. 开启事务1
  2. 在事务1中查询用户信息
  3. 开启事务2
  4. 在事务2中插入一条新数据
  5. 提交事务2
  6. 在事务1中将ID为1的数据的用户姓名修改为Iversen
  7. 在事务1中再次查询用户信息
  8. 提交事务1
mysql> START TRANSACTION; -- 1. 开启事务1
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user_info; -- 2. 在事务1中查询用户信息
+----+-------+--------+---------------+
| id | name  | gender | email         |
+----+-------+--------+---------------+
|  1 | Curry | 男     | curry@163.com |
|  2 | Wade  | 男     | wade@163.com  |
|  3 | James | 男     | james@163.com |
+----+-------+--------+---------------+
3 rows in set (0.00 sec)
mysql> update user_info set name = 'Iversen' where id = 1; -- 在事务1中将ID为1的数据的用户姓名修改为Iversen
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from user_info; -- 7. 在事务1中再次查询用户信息
+----+---------+--------+---------------+
| id | name    | gender | email         |
+----+---------+--------+---------------+
|  1 | Iversen | 男     | curry@163.com |
|  2 | Wade    | 男     | wade@163.com  |
|  3 | James   | 男     | james@163.com |
+----+---------+--------+---------------+
3 rows in set (0.00 sec)
mysql> commit; -- 8. 提交事务1
Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION; -- 3.开启事务2
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `user_info` (`id`, `name`, `gender`, `email`) VALUES (4, 'White', '男', 'white@163.com'); -- 4.在事务2中插入一条新数据
Query OK, 1 row affected (0.00 sec)

mysql> commit; -- 5.提交事务2
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user_info;
+----+-------+--------+---------------+
| id | name  | gender | email         |
+----+-------+--------+---------------+
|  1 | Curry | 男     | curry@163.com |
|  2 | Wade  | 男     | wade@163.com  |
|  3 | James | 男     | james@163.com |
|  4 | White | 男     | white@163.com |
+----+-------+--------+---------------+
4 rows in set (0.00 sec)
  • 场景验证结果

从场景二来看RR级别下仍然是可以避免幻读的问题,那是不是就可以确定RR级别下已经完全解决了幻读的问题呢。那我们再换一种更新方式来看看吧。

  • 场景三
  1. 开启事务1
  2. 在事务1中查询用户信息
  3. 开启事务2
  4. 在事务2中插入一条新数据
  5. 提交事务2
  6. 在事务1中将所有用户的邮箱信息的后缀更换为@gmail.com
  7. 在事务1中再次查询用户信息
  8. 提交事务1
mysql> START TRANSACTION; -- 1. 开启事务1
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user_info; -- 2. 在事务1中查询用户信息
+----+-------+--------+---------------+
| id | name  | gender | email         |
+----+-------+--------+---------------+
|  1 | Curry | 男     | curry@163.com |
|  2 | Wade  | 男     | wade@163.com  |
|  3 | James | 男     | james@163.com |
+----+-------+--------+---------------+
3 rows in set (0.00 sec)
mysql> update user_info set email = REPLACE(email, '@163.com', '@gmail.com'); -- 6. 在事务1中将所有用户的邮箱信息的后缀更换为@gmail.com
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0
mysql> select * from user_info;  -- 7. 在事务1中再次查询用户信息
+----+-------+--------+-----------------+
| id | name  | gender | email           |
+----+-------+--------+-------------- --+
|  1 | Curry | 男     | curry@gmail.com |
|  2 | Wade  | 男     | wade@gmail.com  |
|  3 | James | 男     | james@gmail.com |
|  4 | White | 男     | white@gmail.com |
+----+-------+--------+-----------------+
4 rows in set (0.00 sec)
mysql> commit;  -- 8. 提交事务1
Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION; -- 3.开启事务2
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `user_info` (`id`, `name`, `gender`, `email`) VALUES (4, 'White', '男', 'white@163.com'); -- 4.在事务2中插入一条新数据
Query OK, 1 row affected (0.00 sec)

mysql> commit; -- 5.提交事务2
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user_info;
+----+-------+--------+---------------+
| id | name  | gender | email         |
+----+-------+--------+---------------+
|  1 | Curry | 男     | curry@163.com |
|  2 | Wade  | 男     | wade@163.com  |
|  3 | James | 男     | james@163.com |
|  4 | White | 男     | white@163.com |
+----+-------+--------+---------------+
4 rows in set (0.00 sec)
  • 场景验证结果

事务1在进行更新之后再次查询读取到了事务2新插入到数据,出现了幻读。

2.3.3 小结

看来RR级别的确没有完全解决幻读问题,那为什么还会存在幻读的问题呢,为什么更新的方式不同,会出现不同的结果,什么情况下还会出现幻读问题呢。带着这样的疑问,我们来探索下~

3 分析

从验证结果来看,当前读是可以完全避免幻读的问题,而对于快照读如果在两次读取之间进行了当前读,在某些情况下是会触发幻读的问题。那么下面我们可以从当前读的实现(MVCC)的角度来分析幻读问题的产生原因。

我们应该知道MVCC实现原理主要是依赖记录中的3个隐式字段,undo日志,Read View来实现的,好,那么我们基于产生幻读的场景结合MVCC的实现原理来一步步进行分析。

1.产生幻读的场景 

  • 初始三条测试数据
  • 开启事务1
  • 在事务1中查询用户信息
  • 开启事务2
  • 在事务2中插入一条新数据
  • 提交事务2
  • 在事务1中将所有用户的邮箱信息更换为@gmail.com
  • 在事务1中再次查询用户信息
  • 提交事务1

1.1 执行步骤0:初始三条测试数据

在初始化三条数据后三条初始数据分别会有三个隐式字段值,

DB_TRX_ID(事务id),DB_ROLL_PTR(回滚指针),DB_ROW_ID(隐式主键)。

如下:因为是新插入的数据,回滚指针字段的值均为NULL。

+----+-------+--------+--------------+-----------+-----------+---------+
| id | name  | gender | email        | DB_TRX_ID |DB_ROLL_PTR|DB_ROW_ID|
+----+-------+--------+--------------+-----------+-----------+---------+
|  1 | Curry | 男     | curry@163.com|    2334   |    NULL   |    1    |
|  2 | Wade  | 男     | wade@163.com |    2334   |    NULL   |    2    |
|  3 | James | 男     | james@163.com|    2334   |    NULL   |    3    |
+----+-------+--------+--------------+-----------+-----------+---------+

1.2 执行步骤1:开启事务1

在开启事务1后会为事务1分配一个唯一的事务id

mysql> SELECT trx_id,trx_state,trx_started  FROM INFORMATION_SCHEMA.INNODB_TRX;
+-----------------+-----------+---------------------+
| trx_id          | trx_state | trx_started         |
+-----------------+-----------+---------------------+
| 2335            | RUNNING   | 2024-07-28 21:31:52 |
+-----------------+-----------+---------------------+
1 row in set (0.00 sec)

1.3 执行步骤2:在事务1中查询用户信息

因为是开启事务后的首次查询,所以此时会生成一张Read Veaw读视图,此时trx_list,up_limit_id,low_limit_id的值分别为:

trx_list:因为是测试验证,无其他并发事务参与,所以活跃事务列表中只有当前的事务id[2335];

up_limit_id:活跃事务列表中最小的事务id,即当前事务id:2335;

low_limit_id:下一个未开始的事务id,即当前事务id+1为:2336;

图片

此时查询数据会使用当前生成的Read View并依据可见性算法来进行查询,因为数据库中数据的事务id均小于up_limit_id所以对当前事务均是可见的,所以三条初始数据会全部被查询出来。

注: 可见性算法

  • 首先比较 DB_TRX_ID < up_limit_id , 如果小于,则当前事务能看到 DB_TRX_ID 所在的记录,如果大于等于进入下一个判断
  • 接下来判断 DB_TRX_ID >= low_limit_id , 如果大于等于则代表 DB_TRX_ID 所在的记录在 Read View 生成后才出现的,那对当前事务肯定不可见,如果小于则进入下一个判断
  • 判断 DB_TRX_ID 是否在活跃事务之中,trx_list.contains (DB_TRX_ID),如果在,则代表Read View 生成时刻,这个事务仍处于活跃中,还没有commit,如果DB_TRX_ID=creator_trx_id,则说明是当前事务自己产生的数据,是可见的,如果不等于,则为其他事务修改的数据,当前事务也是看不见的;如果不在活跃事务之中,则说明,你这个事务在Read View生成之前就已经commit了,修改的结果,当前事务是能够看见的。

1.4 执行步骤3:开启事务2

在开启事务2后会为事务2分配一个唯一的事务id。

事务id的分配是递增的,因此事务2的事务id一定是大于事务1。

mysql> SELECT trx_id,trx_state,trx_started  FROM INFORMATION_SCHEMA.INNODB_TRX;
+-----------------+-----------+---------------------+
| trx_id          | trx_state | trx_started         |
+-----------------+-----------+---------------------+
| 2336            | RUNNING   | 2024-07-28 21:35:52 |
+-----------------+-----------+---------------------+
1 row in set (0.00 sec)

1.5 执行步骤4:在事务2中插入一条新数据

此时会产生一条新插入数据的insert undolog日志

1.6 执行步骤5:提交事务2

由于事务提交插入的数据会实际生效,insert undolog日志会被删除,此时表的数据情况如下:

+----+-------+--------+--------------+-----------+-----------+---------+
| id | name  | gender | email        | DB_TRX_ID |DB_ROLL_PTR|DB_ROW_ID|
+----+-------+--------+--------------+-----------+-----------+---------+
|  1 | Curry | 男     | curry@163.com|    2334   |    NULL   |    1    |
|  2 | Wade  | 男     | wade@163.com |    2334   |    NULL   |    2    |
|  3 | James | 男     | james@163.com|    2334   |    NULL   |    3    |
|  4 | White | 男     | white@163.com|    2336   |    NULL   |    4    |
+----+-------+--------+--------------+-----------+-----------+---------+

1.7 执行步骤6:在事务1中将所有用户的邮箱信息的后缀更换为@gmail.com

因为是更新操作,所以是当前读会将所有的符合条件的数据都读取出来,进行更新。更新后的数据表中的数据如下:

+----+-------+--------+----------------+-----------+-----------+---------+
| id | name  | gender | email         | DB_TRX_ID |DB_ROLL_PTR|DB_ROW_ID|
+----+-------+--------+----------------+-----------+-----------+---------+
|  1 | Curry | 男     |curry@gmail.com |    2335   |  0x123825 |    1    |
|  2 | Wade  | 男     |wade@gmail.com  |    2335   |  0x153125 |    2    |
|  3 | James | 男     |james@gmail.com |    2335   |  0x115725 |    3    |
|  4 | White | 男     |white@gmail.com |    2335   |  0x163225 |    4    |
+----+-------+--------+----------------+-----------+-----------+---------+

undolog情况如下:

图片图片

1.8 执行步骤7:在事务1中再次查询用户信息

  • 当前是RR的隔离级别,所以此时使用的Read View读视图仍然是首次查询生成的读视图。
  • 依据Read View的可见性算法分析,分别对四条数据的undolog版本链从尾部至头部逐一进行可见性判断是否可见进行追溯,会看到四条数据的尾部版本就可对当前事务可见。所以四条数据是会在此次查询中全部被查询得到。

由此可以推断产生幻读的原因啦,因为事务1中的更新操作,对事务2中的新插入的数据也进行了更新,更新后新数据的undolog日志中会追加此次更新的回滚日志,并指向新插入数据的undolog记录,此时根据MVCC的可见性算法,事务2新插入的数据此时对于事务1也变成了可见的,因此产生了幻读的问题。

  • 那同样是更新场景二为什么没有产生幻读的问题呢?

在场景二中,更新语句更新的是事务1第一次查询可见的数据,而对事务2中新插入的数据没有进行任何操作,新插入数据的版本链中是不存在当前事务产生的版本数据的,因此新插入的数据对与事务1仍然不可见,所以没有产生幻读问题。图片

4 总结

  • 当前读可以通过锁机制完全避免幻读问题,快照读如果中间对其他事务已提交的插入或更新的数据进行了更新,则会出现幻读的问题。
  • 如何进行避免呢?

采用串行化的隔离级别(不建议);

开发时注意考虑这种产生幻读的场景,尽量通过调整代码逻辑规避幻读问题的发生(建议);

  • 若不能通过调整代码逻辑规避,可以考虑采用当前读的方式避免(建议);
责任编辑:武晓燕 来源: 转转技术
相关推荐

2019-05-28 13:50:27

MySQL幻读数据库

2022-06-30 08:00:00

MySQL关系数据库开发

2023-08-09 17:22:30

MVCCMySQL数据

2024-04-19 08:18:47

MySQLSQL隔离

2021-04-20 19:21:50

脏读MySQL幻读

2023-06-05 00:28:24

MySQL数据库非锁定读

2021-11-30 06:56:59

MySQL幻读查询

2023-11-01 14:13:00

MySQL事务隔离级别

2024-05-13 11:46:33

MySQL数据库

2021-04-27 08:25:52

MVCC数据MySQL

2022-04-27 07:32:02

脏读幻读不可重复读

2019-03-21 09:06:00

数据库复读幻读

2024-04-24 08:26:35

事务数据InnoDB

2022-06-29 11:01:05

MySQL事务隔离级别

2019-12-24 14:50:01

MySQL可重复读数据库

2022-01-03 07:18:05

脏读幻读 MySQL

2021-08-26 06:58:15

Innodb RR隔离级别

2023-02-02 07:06:10

2022-09-21 09:00:10

MySQL幻读隔离级别

2019-03-12 15:41:09

Facebook微信马化腾
点赞
收藏

51CTO技术栈公众号