测试可重复读和读已提交在mysql下的区别_隔离级别详解

[ 目的 ]测试可重复读和读已提交在mysql下的区别

[ mysql8 ] 查看全局/会话隔离级别

select @@global.transaction_isolation,@@transaction_isolation;

设置隔离级别 - 读已提交

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 

准备:

SHOW VARIABLES LIKE 'autocommit';  # 查看是否自动提交
set @@autocommit = "OFF";  

# 创建一个测试表 test
drop table if EXISTS test;
CREATE TABLE test (
    id int AUTO_INCREMENT not null,
    num int ,
    PRIMARY KEY(id)
) ENGINE=InnoDB;

测试:

1. update测试

# 事务A 
begin;
select num from test where id= 1;   # 返回1
# 事务B 
begin;
select num from test where id= 1;   # 返回1

# 事务A 
update test set num=11 where id =1 ;
select num from test where id= 1;    # 返回11
# 事务B 
select num from test where id= 1;    # 修改A里面查B, 返回1, 结果不变

# 事务A 
commit ;
select num from test where id= 1;   # 返回11

# 事务B 
select num from test where id= 1;   # 事务A一提交B里面查到结果改变, 返回11
commit ;

2. insert 测试

# 事务A
begin;
select count(*) from test;      # 返回 3
# 事务B
begin;
select count(*) from test;      # 返回 3

# 事务A
insert into test  (value) values (4);   
select count(*) from test;      # 返回 4
# 事务B
select count(*) from test;      # A未提交之前, B查询结果不变 ,返回 3

# 事务A
commit;
# 事务B
select count(*) from test;      # A提交后, B查询到A插入的数据 ,返回 4
commit;

已提交读结论:

在读已提交的隔离级别下 , 另一个事务可以的读到当前事务提交后的变更包括insert , update 操作; 意味着这个级别尽管读不到别的事务未提交的数据(解决了脏读现象), 但是两次读到的结果不一样, 可能是行数也可能是值(出现了不可重复读和幻读)

---

设置隔离级别 - 可重复读

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

测试

1. insert 测试

# 事务A
begin;
select count(*) from test;      # 返回4
# 事务B
begin;
select count(*) from test;      # 返回4

# 事务A
insert into test (num) value (5);
select count(*) from test;      # 返回5
# 事务B
select count(*) from test;      # 事务B查不到未提交事务A的插入, 返回4

# 事务A
commit;
# 事务B
select count(*) from test;      # 事务B查不到已提交事务A的插入, 返回4
commit;

2. update 测试

# 事务A
begin;
select num from test where id = 1;      # 返回11
# 事务B
begin;
select num from test where id = 1;      # 返回11

# 事务A
update test set num = 1 where id = 1;
select num from test where id = 1;      # 返回 1
# 事务B
select num from test where id = 1;      # 事务B查不到未提交事务A的修改, 返回11

# 事务A
commit; 
# 事务B
select num from test where id = 1;      # 事务B查不到已提交事务A的插入, 返回11

可重复读结论:

在可重复读下解决了读已提交的漏洞, 当前事务只会读取创建事务的数据拷贝, 不受到其他事务的影响无论是insert或是update

全部的隔离级别:

读未提交	read uncommitted
读已提交	read committed
可重复读	Repeatable read
串行化	Serializable

隔离级别3种问题和定义:

脏读 : 读到另一个事务未提交的数据 , 如果我使用检索到的数据进行操作但是另一个事务回滚就会造成一致性问题

不可重复读 : 在同一事务中用相同条件检索的结果(值)不同; 可能是另一个事务 update 后的结果在事务中被读到了

幻读 : 在同一事务同一检索条件下, 后执行的检索操作看到先执行的检索结果中没有出现的行, 幻读专指 insert 造成的现象

隔离级别:

隔离级别是数据库的一个标准, 不同的厂家去执行这个标准, 可以有不同的实现方式; 标准的隔离级别如图 :

image.png

mysql 的隔离级别:

mysql很好的实现了4种隔离级别的标准, 其中可重复读在标准中可能出现幻读, 但是以上测试结果并没有出现幻读的现象 , 因为mysql直接避免了幻读的可能, 所以和图片唯一的区别是可重复读下 不出现脏读, 不可重复读以及幻读 .具体原因看下一节 : 版本链机制