MySQL的四种事务隔离级别详解

时间:2020-05-01

数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。

查询当前数据库隔离级别

select @@transaction_isolation

默认级别,可重复读(repeatable read)

脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据。

不可重复读:事务 A 多次读取同一数据,事务 B 在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果 不一致。

幻读:系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级,但是系统管理员B就在这个时候插入了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。

开始实验

创建一张表

CREATE TABLE `student` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NOT NULL,
	`age` SMALLINT(5) UNSIGNED NOT NULL,
	PRIMARY KEY (`id`)
)
ENGINE=InnoDB
;

打开2个mysql客户端

读未提交(Read uncommitted)

A终端执行事务级别设置

set session transaction isolation level read uncommitted;

开启事务

start transaction;

查询数据

select * from student;

B终端执行事务级别设置

set session transaction isolation level read uncommitted;

开启事务

start transaction;

更新数据

update student set age=100 where id=1;

注意:此时B终端未提交事务!

打开A终端查询数据

select * from student;

B终端事务未提交,A终端就可以读到数据了。

假设B终端回滚了事务,那么A终端读取到的数据是有问题的,是不可以使用的。

读提交(read committed)

在实验之前,先把2个终端的事务提交一下。

commit;

A终端

修改事务隔离级别

set session transaction isolation level read committed;

开启事务

start transaction;

查询数据

select * from student;

B终端

修改事务隔离级别

set session transaction isolation level read committed;

开启事务

start transaction;

更新数据

update student set age=30 where id=1;

注意:事务未提交!

A终端执行查询

数据没变,因为B终端事务未提交。

B终端提交事务

commit;

在A终端查询数据

数据变了,因为B终端事务已提交。

因为在A终端2次数据读取不一致,所以产生了不可重复读。

可重复读(repeatable read)

实验之前,先把2个终端的事务提交。

A终端、B终端

commit;

A终端

修改事务隔离级别

set session transaction isolation level repeatable read;

开启事务

start transaction;

查询数据

select * from student;

B终端

修改事务隔离级别

set session transaction isolation level repeatable read;

开启事务

start transaction;

更新数据

update student set age=55 where id=1;

A终端查询

select * from student;

B终端提交事务

commit;

A终端查询

select * from student;

2次查询的数据一致,解决了不可重复读问题。

A终端更新数据

update student set age = age -1 where id=1;

查询数据

B终端把age变为55并提交了,然后A终端更新数据后自动使用55来计算了,所以age最后值为54。A终端出现了幻读。

B终端新增一条数据

insert into student values(null,'王五',30);

A终端查询

select * from student;

没有出现新增的数据。

A终端提交事务

查询数据

串行化(Serializable)

A终端

修改事务隔离级别

set session transaction isolation level serializable;

开启事务

start transaction;

查询数据

select * from student;

B终端

修改事务隔离级别

set session transaction isolation level serializable;

开启事务

start transaction;

更新数据

update student set age=88 where id=1;

B终端在等待A终端提交事务,如果A终端一直不提交事务,这个更新语句一直在等待。

A终端提交事务

commit;

B终端超时了

所在解决了幻读、脏读、不可重复读问题。