企业宣传,产品推广,广告招商,广告投放联系seowdb

咦~ MySQL怎样出现幻读啦!

InnoDB自动的事务隔离级别是REPEATABLE-READ,它为了处置该隔离级别并发状况下的幻读疑问,经常使用了LBCC(基于锁的并发控制)和MVCC(多版本的并发控制)两种打算。其中LBCC处置的是读状况下的幻读疑问,MVCC处置的是快照读状况下的幻读疑问,那既然如此,该隔离级别下能否依然还存在幻读的疑问呢?幻读疑问究竟有没有齐全处置呢?基于这样的不懂,上方咱们来启动验证下吧。

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

为了启动验证,在测试库建设了一张测试经常使用的用户消息表,并且拔出了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;

首先咱们先来看看读的场景下会不会出现幻读的疑问。

读(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,+∞)咱们称之为临键,即左开右闭的汇合。当咱们对上方的记载和间隙独特加锁时,减少的便是临键锁。

触发读的模式有很多种,这里仅经常使用select lock in share mode这种模式来启动读幻读疑问验证。

mysql> START TRANSACTION; -- 1.开启事务1Query 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.开启事务2Query 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第二次查问时也不会查问到新记载,所以此场景下不会发生幻读的疑问。

由场景验证结果可以看到,由于临键锁的存在,会阻塞其余事务对加锁间隙的数据拔出,所以读场景下经过LBCC是可以齐全处置幻读的疑问。

那接上去咱们再看看快照读场景下是怎样样的。

由于读是经过LBCC基于锁的模式来启动并发控制,是乐观锁的成功,同时也会由于锁的要素,形成锁抵触的概率变大,也会造成功能的降低,因此基于提高并发功能的思索,引入了快照读,快照读望文生义即读取的是数据的快照版本,快照读的成功是基于MVCC多版本并发控制,它在很多状况下,防止了加锁操作,降低了功能开支。

mysql> START TRANSACTION; -- 1.开启事务1Query 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.提交事务1Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION; -- 3.开启事务2Query 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.提交事务2Query 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中两次查问之间启动了读降级操作呢,那会不会出现幻读的疑问呢,那接上去咱们来看一看场景二。

mysql> START TRANSACTION; -- 1. 开启事务1Query 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的数据的用户姓名修正为IversenQuery OK, 1 row affected (0.00 sec)Rows matched: 1Changed: 1Warnings: 0mysql> 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. 提交事务1Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION; -- 3.开启事务2Query 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.提交事务2Query 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级别下曾经齐全处置了幻读的疑问呢。那咱们再换一种降级模式来看看吧。

mysql> START TRANSACTION; -- 1. 开启事务1Query 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.comQuery OK, 4 rows affected (0.00 sec)Rows matched: 4Changed: 4Warnings: 0mysql> 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. 提交事务1Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION; -- 3.开启事务2Query 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.提交事务2Query 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新拔出到数据,出现了幻读。

看来RR级别确实没有齐全处置幻读疑问,那为什么还会存在幻读的疑问呢,为什么降级的模式不同,会出现不同的结果,什么状况下还会出现幻读疑问呢。带着这样的不懂,咱们来探求下~

从验证结果来看,读是可以齐全防止幻读的疑问,而关于快照读假设在两次读取之间启动了读,在某些状况下是会触发幻读的疑问。那么上方咱们可以从读的成功(MVCC)的角度来剖析幻读疑问的发生要素。

咱们应该知道MVCC成功原理重要是依赖记载中的3个隐式字段,undo日志,Read View来成功的,好,那么咱们基于发生幻读的场景联合MVCC的成功原理来一步步启动剖析。

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_startedFROM 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.4 口头步骤3:开启事务2

在开启事务2后会为事务2调配一个惟一的事务id。

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

mysql> SELECT trx_id,trx_state,trx_startedFROM 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中再次查问用户消息

由此可以推断发生幻读的要素啦,由于事务1中的降级操作,对事务2中的新拔出的数据也启动了降级,降级后新数据的undolog日志中会追加此次降级的回滚日志,并指向新拔出数据的undolog记载,此时依据MVCC的可见性算法,事务2新拔出的数据此时关于事务1也变成了可见的,因此发生了幻读的疑问。

在场景二中,降级语句降级的是事务1第一次性查问可见的数据,而对事务2中新拔出的数据没有启动任何操作,新拔出数据的版本链中是不存在事务发生的版本数据的,因此新拔出的数据对与事务1依然无法见,所以没有发生幻读疑问。

驳回串行化的隔离级别(不倡导);

开发时留意思索这种发生幻读的场景,尽量经过调整代码逻辑规避幻读疑问的出现(倡导);

© 版权声明
评论 抢沙发
加载中~
每日一言
不怕万人阻挡,只怕自己投降
Not afraid of people blocking, I'm afraid their surrender