Mysql幻读测试

Mysql数据库不可重复读幻读部分原理解读

可能有部分理解错误，希望得到批评指正

MVCC关键参数

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

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

快照读生成的Read View 关键参数：
所有在该事务之后执行的事务均不能见到该数据；m_up_limit_id  （下一个分配的事务ID）

中间为链式存储的正在执行的事务ID   m_IDs

所有已经执行结束的事务都可以看到该数据；m_low_limit_id  （正在执行的事务ID的最小的 或者 下一个分配的事务ID）

事务隔离级别 RC 和 RR下不可重复读出现原因解读

不可重复读的现象原因：
	在事务A正在执行时两次相同条件查询的中间，事务B执行完成，插入或者修改了部分事务A正使用的数据
	使得事务A第二次查询到的数据与第一次不同，就是不可重复读的原因。
	读的时候加S锁，写入或者修改的时候加X锁。

	Mysql加入了MVCC解决了部分不可重复读的现象。
	RC 隔离级别下：
	假如事务A开始了  事务ID为 5；  
	此时事务A开始查询某个条件的数据；
		 // RC创建了一个快照 m_IDs[];	m_low_limit_id = 6;  	m_up_limit_id=6;
	此时事务B先查询然后更新了部分满足条件的数据；事务B的事务ID为6； 
		// m_IDs[5]   m_low_limit_id = 5;  m_up_limit_id=7;
	事务B结束；											
	事务A再次查询满足条件的数据；
	 // RC又创建了一个快照  m_IDs[];  m_low_limit_id = 7;  m_up_limit_id=7;
	事务A提交。 		m_IDs[]; m_low_limit_id = 7;  m_up_limit_id=7;

	RR隔离级别下：
	假如事务A开始了  事务ID为 5；	
	//m_low_limit_id = 5;  m_up_limit_id=6;
	此时事务A开始查询某个条件的数据；
	// RR创建了一个快照 	m_IDs[]  m_low_limit_id = 6;  m_up_limit_id=6;
	此时事务B先查询然后更新了部分满足条件的数据；事务B的事务ID为6；
	事务B的快照信息：m_IDs[5] m_low_limit_id = 5;  m_up_limit_id=7;  事务B
	事务B结束；
										
	事务A对查询到的数据进行更新；
	//事务A的快照信息：m_IDs[];  m_low_limit_id = 6;  m_up_limit_id=6;	
	再次查询满足条件的数据；

	提交。 		m_IDs[]; m_low_limit_id = 7;  m_up_limit_id=7;



	幻读：
	RR隔离级别下：
	假如事务A开始了  事务ID为 5；	m_low_limit_id = 6;  m_up_limit_id=6;  
	此时事务A开始查询某个条件的数据；m_IDs[]  m_low_limit_id = 6;  m_up_limit_id=6;快照读
				// RC创建了一个快照
	此时事务B插入部分满足条件的数据；事务B的事务ID为6；
	//事务B的快照信息：m_IDs[5] m_low_limit_id = 5;  m_up_limit_id=7; 
	事务B结束；
	事务A对查询到的数据进行更新；	 事务A改为当前读 失去了MVCC的控制。
	再次查询满足条件的数据；快照读会更新为当前读的数据  出现幻读情况。
	提交。 		m_IDs[]; m_low_limit_id = 7;  m_up_limit_id=7;

幻读为不可重复读的一种特殊情况：可以利用MVCC加next-key lock 锁范围数据防止数据插入
解决幻读的情况，但是查询的时候要加

-- 表结构
CREATE TABLE `stuInfo` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`class_name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
`teacher_id` INT(11) NULL DEFAULT NULL,
INDEX `teacher_id` (`teacher_id`),
INDEX `id` (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=23
;


//查看当前事物级别：
SELECT @@tx_isolation;
//  修改事务的隔离级别
//设置read uncommitted级别：
set session transaction isolation level read uncommitted;

//设置read committed级别：
set session transaction isolation level read committed;

//设置repeatable read级别：
set session transaction isolation level repeatable read;

//设置serializable级别：
set session transaction isolation level serializable;

=================================================================================================
出现幻读的情况展示；
先执行下面这个
START TRANSACTION;   --  开启事务

select * from xssTest.stuInfo where name ='cx';

select sleep(5);

update xssTest.stuInfo set teacher_id =110 where name ='cx';
select * from xssTest.stuInfo where name ='cx';
select sleep(5);
select * from xssTest.stuInfo where name ='cx';
--  查看当前事务版本号
SELECT *
FROM information_schema.innodb_trx tx
WHERE tx.trx_mysql_thread_id = connection_id();

-- 提交事务
commit ; 

然后马上执行这个

START TRANSACTION;   --  开启事务

--  执行事务逻辑
select sleep(1); 
insert into  xssTest.stuInfo (name ,class_name) values ('cx','three');

select sleep(1);   --  让该事务强行等待2S
SELECT tx.*
FROM information_schema.innodb_trx tx
WHERE tx.trx_mysql_thread_id = connection_id();

-- 提交事务
commit ; 

=================================================================================================

可以防止幻读的方法：
START TRANSACTION;   --  开启事务

select * from xssTest.stuInfo where  id >5 lock in share mode;

select sleep(5);

update xssTest.stuInfo set teacher_id =110 where id >5;
select * from xssTest.stuInfo where  id >5 lock in share mode;
select sleep(5);
select * from xssTest.stuInfo where  id >5 lock in share mode;
--  查看当前事务版本号
SELECT *
FROM information_schema.innodb_trx tx
WHERE tx.trx_mysql_thread_id = connection_id();

-- 提交事务
commit ; 


START TRANSACTION;   --  开启事务


--  执行事务逻辑
select sleep(1); 
insert into  xssTest.stuInfo (id,name ,class_name) values (11,'cx','three');

select sleep(1);   --  让该事务强行等待2S
SELECT tx.*
FROM information_schema.innodb_trx tx
WHERE tx.trx_mysql_thread_id = connection_id();

-- 提交事务
commit ; 

=================================================================================================