6.1 文章目的

在mysql中创建数据，依据该数据，对mysql各个事务隔离级别进行实验。

6.2 打开mysql命令行，关闭自动提交

--查看是否是自动提交 1表示开启，0表示关闭
select @@autocommit;
--设置关闭
set autocommit = 0;

6.3 数据准备

--创建数据库
create database tran;
--切换数据库
use tran;
--准备数据
 create table psn(id int primary key,name varchar(10)) engine=innodb;
--插入数据
insert into psn values(1,'zhangsan');
insert into psn values(2,'lisi');
insert into psn values(3,'wangwu');
commit;

6.4 脏读测试

开启两个mysql连接
窗口A sql操作:

use tran;
--查看隔离级别
show variables like "%isolation%";

--设置隔离级别
set session transaction isolation level read uncommitted;
--set session transaction isolation level read committed;
--set session transaction isolation level repeatable read;
--set session transaction isolation level seariable;

--开启事务，查看数据
start transaction;
select * from psn;

窗口B sql操作：

use tran;
start transaction;
select * from psn;
update psn set name='xxx' where id =1;
select * from psn;

窗口A sql操作:

select * from psn;

窗口B sql操作：

rollback;

6.4 不可重复读测试

窗口A：

--设置隔离级别
--set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
--set session transaction isolation level repeatable read;
--set session transaction isolation level seariable;

start transaction;
select * from psn;

窗口B：

start transaction;
select * from psn;
update psn set name ='zhangsan2' where id = 1;
select * from psn;

窗口A：

select * from psn;

窗口B：

commit;

窗口A：

select * from psn;

6.5 幻读测试

窗口A：

--设置隔离级别
--set session transaction isolation level read uncommitted;
--set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
--set session transaction isolation level seariable;

start transaction;
select * from psn;

窗口B：

start transaction;
select * from psn;
insert into psn values(4,'sisi');
select * from psn;

窗口A：

select * from psn; --看不到数据
insert into psn values(4,'sisi'); --插入报错