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

这里给出 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 的机制中读取的，插入数据也是要先读取一下有没有主键冲突才能决定是否执行插入。

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

下面给出幻读的例子：

设置隔离级别为 Read Repeatable，开启两个事务 t1和t2

原始表如下：

在t1中，首先查看id=C的数据，为空：

mysql> select * from amount where id = 'C';
Empty set (0.00 sec)

然后在t2中插入id='C'的数据，此时我们发现插入成功了

mysql> insert into  amount values('C',1000);
Query OK, 1 row affected (0.01 sec)

 然后，我们在t1中插入id='C'的数据，可以看到一直处于等待状态，直到t2的事务被提交或者超时。

当t2的事务被提交后，t1中会报主键重复的错误：

mysql> insert into  amount values('C',1000);
ERROR 1062 (23000): Duplicate entry 'C' for key 'PRIMARY'

 这就是幻读现象，我们在t1中查询id='C'的数据时显示数据不存在，但是由于t2中插入了id=C的数据，导致了t1再想插入时出现了主键重复的错误，t2成功扰乱了t1的事务。

我们看看再Serializable的级别下是如何的：

在t1中，首先查看id=C的数据，为空：

mysql> select * from amount where id = 'C';
Empty set (0.00 sec)

t2插入数据，发现跟上面不同的是，t2阻塞了。

这时在t1中插入数据，成功插入：

mysql>  insert into  amount values('C',1000);
Query OK, 1 row affected (0.02 sec)

当t1提交以后，这次轮到t2出现了主键重复的错误。

从结果可以知道，t1的事务并没有受到t2事务的扰乱，即在Serializable的隔离级别下没有出现幻读。

在上面两个实验中我们发现，repeatable read是无法避免幻读的，但是，在某种情况下，它却能解决幻读问题。

下面看例子1，查询的记录不存在的情况

t1	t2
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
start transaction;	start transaction;
mysql> select * from amount where id = 'E' for update; Empty set (0.00 sec)
	mysql> select * from amount; +----+-------+ | id | money | +----+-------+ | A | 100 | | B | 600 | | C | 1000 | | D | 1000 | +----+-------+ 4 rows in set (0.00 sec)
	锁住了。
mysql> insert into amount values('E',1000); Query OK, 1 row affected (0.01 sec)
commit
	mysql> insert into amount values('E',1000); ERROR 1062 (23000): Duplicate entry 'E' for key 'PRIMARY'
	commit;

  

使用select .. for update锁住，然后再insert，可以避免幻读。

其实，即使在t2中，插入id不为"E"的记录，也是会阻塞的（锁住），依然要等待t1提交后才能轮到t2工作。 

例2 ，当查询的结果已经存在

t1	t2
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
start transaction;	start transaction;
mysql> select * from amount; +----+-------+ | id | money | +----+-------+ | A | 100 | | B | 600 | +----+-------+	mysql> select * from amount; +----+-------+ | id | money | +----+-------+ | A | 100 | | B | 600 | +----+-------+
mysql> select * from amount where id = 'A' for update; +----+-------+ | id | money | +----+-------+ | A | 100 | +----+-------+
	mysql> select * from amount; +----+-------+ | id | money | +----+-------+ | A | 100 | | B | 600 | +----+-------+
	mysql> insert into amount values('C',1000); Query OK, 1 row affected (0.01 sec)
commit;	commit;

以上例子说明，for update时候，id为主键，RR策略时候，锁住了的条件符合的行，但是如果条件找不到任何列，锁住的是整个表，因此当t1查询到的记录为空时，在t2想插入该主键记录时是阻塞的；当t1查询到的记录非空时，除了该主键记录之外，可以在其他事务插入任何不存在的主键记录而不阻塞。

例3，范围查询的情况

t1	t2
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
start transaction;	start transaction;
mysql> select * from amount where id < 'M' for update; +----+-------+ | id | money | +----+-------+ | A | 100 | | B | 600 | | C | 1000 | | D | 1000 | +----+-------+
	mysql> insert into amount values('X',1000); Query OK, 1 row affected (0.01 sec)
mysql> select * from amount ; +----+-------+ | id | money | +----+-------+ | A | 100 | | B | 600 | | C | 1000 | | D | 1000 | | M | 1000 | | N | 1000 | +----+-------+
	锁住了
mysql> select * from amount ; +----+-------+ | id | money | +----+-------+ | A | 100 | | B | 600 | | C | 1000 | | D | 1000 | | M | 1000 | | N | 1000 | +----+-------+
	commit;
mysql> select * from amount ; +----+-------+ | id | money | +----+-------+ | A | 100 | | B | 600 | | C | 1000 | | D | 1000 | | M | 1000 | | N | 1000 | +----+-------+ 可重复读

可以看到，用 id<'M' 加的锁，只锁住了 id< 'M' 的范围，可以成功添加id为X的记录，添加id为'G'的记录时就会等待锁的释放。

例4

t1	t2
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
start transaction;	start transaction;
mysql> select * from amount; +----+-------+ | id | money | +----+-------+ | A | 100 | | B | 600 | | C | 1000 | +----+-------+
	mysql> insert into amount values('D',1000); Query OK, 1 row affected (0.01 sec) mysql> commit;
mysql> select * from amount; +----+-------+ | id | money | +----+-------+ | A | 100 | | B | 600 | | C | 1000 | +----+-------+  可重复读
mysql> select * from amount lock in share mode; +----+-------+ | id | money | +----+-------+ | A | 100 | | B | 600 | | C | 1000 | | D | 1000 | +----+-------+  加锁，读取的是最新值，当前读
mysql> select * from amount for update; +----+-------+ | id | money | +----+-------+ | A | 100 | | B | 600 | | C | 1000 | | D | 1000 | +----+-------+   加锁，读取的是最新值，当前读

如果使用普通的读，会得到一致性的结果，如果使用了加锁的读，就会读到“最新的”“提交”读的结果。

本身，可重复读和提交读是矛盾的。在同一个事务里，如果保证了可重复读，就会看不到其他事务的提交，违背了提交读；如果保证了提交读，就会导致前后两次读到的结果不一致，违背了可重复读。

可以这么讲，InnoDB提供了这样的机制，在默认的可重复读的隔离级别里，可以使用加锁读去查询最新的数据。

结论：

MySQL InnoDB的可重复读并不保证避免幻读，需要应用使用加锁读来保证。而这个加锁度使用到的机制就是next-key locks。

mysql 的重复读解决了幻读的现象，但是需要 加上 select for update/lock in share mode 变成当前读避免幻读，普通读select存在幻读