注册    登录
创作新主题
社区所有版块导航
Python
python开源   Django   Python   DjangoApp   pycharm  
DATA
docker   Elasticsearch  
分享
问与答   闲聊   招聘   翻译   创业   分享发现   分享创造   求职   区块链   支付之战  
aigc
aigc   chatgpt  
WEB开发
linux   MongoDB   Redis   DATABASE   NGINX   其他Web框架   web工具   zookeeper   tornado   NoSql   Bootstrap   js   peewee   Git   bottle   IE   MQ   Jquery  
机器学习
机器学习算法  
Python88.com
反馈   公告   社区推广  
产品
短视频  
印度
印度  
关注
Py学习  »  DATABASE

啊～ MySQL居然出现幻读了？

架构师 • 5 月前 • 190 次点击  
架构师（JiaGouX）
我们都是架构师！
架构未来，你来不来？



  • 1 背景

  • 2 验证

    • 2.1 验证准备

    • 2.2 当前读场景验证

    • 2.3.快照读场景验证

  • 3 分析

  • 4 总结


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. 初始三条测试数据
  2. 开启事务1
  3. 在事务1中查询用户信息
  4. 开启事务2
  5. 在事务2中插入一条新数据
  6. 提交事务2
  7. 在事务1中将所有用户的邮箱信息更换为@gmail.com
  8. 在事务1中再次查询用户信息
  9. 提交事务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所以对当前事务均是可见的，所以三条初始数据会全部被查询出来。

注: 可见性算法

  1. 首先比较 DB_TRX_ID < up_limit_id , 如果小于，则当前事务能看到 DB_TRX_ID 所在的记录，如果大于等于进入下一个判断
  2. 接下来判断 DB_TRX_ID >= low_limit_id , 如果大于等于则代表 DB_TRX_ID 所在的记录在 Read View 生成后才出现的，那对当前事务肯定不可见，如果小于则进入下一个判断
  3. 判断 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 总结

  1. 当前读可以通过锁机制完全避免幻读问题，快照读如果中间对其他事务已提交的插入或更新的数据进行了更新，则会出现幻读的问题。
  2. 如何进行避免呢？
  • 采用串行化的隔离级别（不建议）；
  • 开发时注意考虑这种产生幻读的场景，尽量通过调整代码逻辑规避幻读问题的发生（建议）；
  • 若不能通过调整代码逻辑规避，可以考虑采用当前读的方式避免（建议）；

如喜欢本文，请点击右上角，把文章分享到朋友圈
如有想了解学习的技术点，请留言给若飞安排分享

因公众号更改推送规则，请点“在看”并加“星标”第一时间获取精彩技术分享

·END·

相关阅读：

作者：孔德志

来源：转转技术

版权申明：内容来源网络，仅供学习研究，版权归原创者所有。如有侵权烦请告知，我们会立即删除并表示歉意。谢谢!

架构师

我们都是架构师！



关注架构师(JiaGouX)，添加“星标”

获取每天技术干货，一起成为牛逼架构师

技术群请加若飞：1321113940 进架构师群

投稿、合作、版权等邮箱：admin@137x.com

Python社区是高质量的Python/Django开发社区
本文地址：http://www.python88.com/topic/174329
Reply
 
190 次点击  
登录后回复
关于   移动版
Py学习 - 专注于Python技术发展的社区(原Django社区)
沪ICP备11025650号