Mysql加锁过程详解（2）-关于mysql 幻读理解

Mysql加锁过程详解（1）-基本知识

Mysql加锁过程详解（2）-关于mysql 幻读理解

Mysql加锁过程详解（3）-关于mysql 幻读理解

Mysql加锁过程详解（4）-select for update/lock in share mode 对事务并发性影响

Mysql加锁过程详解（5）-innodb 多版本并发控制原理详解

Mysql加锁过程详解（6）-数据库隔离级别（1）

Mysql加锁过程详解（6）-数据库隔离级别（2）-通过例子理解事务的4种隔离级别

Mysql加锁过程详解（7）-初步理解MySQL的gap锁

Mysql加锁过程详解（8）-理解innodb的锁(record,gap,Next-Key lock)

首先，mysql 幻读并非是”一个事务内进行两次相同操作居然得到了不一样的结果”，因为它根本不可能发生在使用了 read view / MVCC 的 RR 隔离级别下，这种幻读的定义更适合给 Oracle，Oracle 的事务隔离只有两级，RC 和 Serializable。然后还有很多人辩解说不可重复读是针对某条记录的，幻读是针对记录集合的，这是在自我安慰么？

这里给出 mysql 幻读的比较形象的场景：

users： id 主键

1、T1：select * from users where id = 1;

2、T2：insert into `users`(`id`, `name`) values (1, 'big cat');

3、T1：insert into `users`(`id`, `name`) values (1, 'big cat');

T1 ：主事务，检测表中是否有 id 为 1 的记录，没有则插入，这是我们期望的正常业务逻辑。

T2 ：干扰事务，目的在于扰乱 T1 的正常的事务执行。

在 RR 隔离级别下，1、2 是会正常执行的，3 则会报错主键冲突，对于 T1 的业务来说是执行失败的，这里 T1 就是发生了幻读，因为T1读取的数据状态并不能支持他的下一步的业务，见鬼了一样。

在 Serializable 隔离级别下，1 执行时是会隐式的添加 gap 共享锁的，从而 2 会被阻塞，3 会正常执行，对于 T1 来说业务是正确的，成功的扼杀了扰乱业务的T2，对于T1来说他读取的状态是可以拿来支持业务的。

所以 mysql 的幻读并非什么读取两次返回结果集不同，而是事务在插入事先检测不存在的记录时，惊奇的发现这些数据已经存在了，之前的检测读获取到的数据如同鬼影一般。

这里要灵活的理解读取的意思，第一次select是读取，第二次的 insert 其实也属于隐式的读取，只不过是在 mysql 的机制中读取的，插入数据也是要先读取一下有没有主键冲突才能决定是否执行插入。

不可重复读侧重表达读-读，幻读则是说读-写，用写来证实读的是鬼影。

下面例子版本

SELECT VERSION();

例子1，读提交

a	b
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET AUTOCOMMIT=0;
1.不可重复读
begin	begin
INSERT test VALUES(1,1);
SELECT * FROM test;	SELECT * FROM test;
commit
	SELECT * FROM test;

	COMMIT
B在一个事务的查询的结果变了，不可重复读
2.锁
begin	begin
INSERT test VALUES(2,2);
	SELECT * FROM test;
	INSERT test VALUES(2,2);
	Lock wait timeout exceeded; try restarting transaction
COMMIT	COMMIT

	begin
	INSERT test VALUES(3,3);
	INSERT test VALUES(4,4);
	COMMIT

BEGIN	BEGIN
SELECT COUNT(*) FROM test WHERE a>2;	SELECT COUNT(*) FROM test WHERE a>2;

INSERT test VALUES(5,5);
SELECT COUNT(*) FROM test WHERE a>2;	SELECT COUNT(*) FROM test WHERE a>2;

COMMIT
SELECT COUNT(*) FROM test WHERE a>2;	SELECT COUNT(*) FROM test WHERE a>2;

例子2：重复读

a	b
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET AUTOCOMMIT=0;
1．可重复读
begin	begin
INSERT test VALUES(1,1);
SELECT * FROM test;	SELECT * FROM test;
commit
	SELECT * FROM test;

	COMMIT
	BEGIN
	SELECT * FROM test;

	COMMIT
B在一个事务的查询的没变
2锁
begin	begin
INSERT test VALUES(2,2);
	SELECT * FROM test;
	INSERT test VALUES(2,2);
	Lock wait timeout exceeded; try restarting transaction
COMMIT	COMMIT
3(幻读)
BEGIN	BEGIN
INSERT test VALUES(3,3);
SELECT * FROM test;	SELECT * FROM test;
COMMIT
	SELECT * FROM test;
	INSERT test VALUES(3,3);
	Duplicate entry '3' for key 'PRIMARY'
	COMMIT
	BEGIN
	SELECT * FROM test;
	COMMIT
幻读，b明明查到没有，插入时候提示主键冲突，刚刚查询没有，出现幻觉？


	begin
	INSERT test VALUES(4,4);
	COMMIT
4．可重复读
BEGIN	BEGIN
SELECT COUNT(*) FROM test WHERE a>2;	SELECT COUNT(*) FROM test WHERE a>2;

INSERT test VALUES(5,5);
SELECT COUNT(*) FROM test WHERE a>2;	SELECT COUNT(*) FROM test WHERE a>2;

COMMIT
BEGIN
SELECT COUNT(*) FROM test WHERE a>2;	SELECT COUNT(*) FROM test WHERE a>2;

COMMIT	COMMIT