Mysql的REPEATABLE READ能解决幻读吗

vlambda
2022-02-12

Mysql的REPEATABLE READ能解决幻读吗

MySQL版本8.0.27,MySQL的默认隔离级别为：REPEATABLE READ

sql表格横向较长，建议用电脑浏览器阅读。

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27    |
+-----------+
1 row in set (0.00 sec)
mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)
mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

建表语句

create table s1(
  id  bigint unsigned auto_increment,
  k1 varchar(20),
  k2 int,
  k3 varchar(20),
  key_part1 varchar(20),
  key_part2 varchar(20),
  key_part3 varchar(20),
  common_field varchar(20),
   primary key (id),
   INDEX idx_k1 (k1),
   UNIQUE INDEX uk_k2 (k2),
   INDEX idx_k3 (k3),
   INDEX idx_key_part (key_part1,key_part2,key_part3)
)engine =InnoDB charset=utf8;

尝试触发幻读

Session1	Session2
start transaction;	start transaction;
select * from s1;//空表
	insert into s1 (k1,k2)values('eric',18);
	commit;
select * from s1;//空表
commit;
select * from s1;//可以看到Session2插入的数据

说明Mysql的REPEATABLE READ是可以能解决幻读

但是不是完全的解决幻读问题

Session1	Session2
start transaction;	start transaction;
select * from s1 where k2=19;//无
	insert into s1 (k1,k2)values('ericRaw',19);
	commit;
select * from s1 where k2=19;//无
update s1 set k1='eric帅' where k2=19;//成功修改
select * from s1 where k2=19;//能看到成功被修改的记录
commit;

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？以MySQL InnoDB为例：

快照读

快照读：简单的select操作，属于快照读，不加锁。
        select * from table where ?;

如果一个事务中的select * from table where ?需要查看别的事务已经提交的最新记录该怎么做？

官网原话：

If you want to see the “freshest” state of the database, use either the READ COMMITTED isolation level or a locking read:

SELECT * FROM t FOR SHARE;

//如果想在事务中看到别的事务已经提交的最新的记录使用READ COMMITTED或者把使用

SELECT * FROM t where FOR SHARE;

当前读

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。
 
        select * from table where ? lock in share mode;
        select * from table where ? for update;
        insert into table values (…);
        update table set ? where ?;
        delete from table where ?;
 
所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

验证SELECT * FROM t FOR SHARE能看到别的事务已经提交的事务。

Session1	Session2
start transaction;	start transaction;
select * from s1;//快照读
	insert into s1 (k1,k2)values('hy',20);
	commit;
select * from s1;//看不到k2=20的结果
select * from s1 for share;//看到k2=20的结果
commit;

由此可见oracle,SQL server,PostgreSQL默认的事务隔离级别是read committed的原因，避免在事务中，看不到别的事务已经提交的记录，而删改了别的事务已经提交的记录。某些公司也因此把MySQL的隔离级别改为read committed

参考文献：

https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html 官网的innodb一致的非锁定读
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html 官网的innodb的锁定读