实例演示mysql的四种事务隔离级别
说明
| 事务隔离级别 |
说明 |
问题 |
|---|---|---|
| READ UNCOMMITTED |
读未提交 |
脏读 |
| READ COMMITTED |
读已提交 |
不可重复读 |
| REPEATABLE READ |
可重复读 |
幻读 |
| SERIALIZABLE | 序列化/串行化 | 阻塞 |
mysql的默认事务隔离级别是REPEATABLE-READ，查看当前数据库的事务级别可以用指令：show variables like '%isolation%';，如图1结果。实验环境系统为win10,mysql 版本为：Server version: 8.0.11 MySQL Community Server - GPL，操作工具为win下的cmd窗口。
图1 查看本地mysql的默认事务隔离级别
操作步骤
| 1.win下打开两个cmd窗口，模拟连接mysql的两个客户端，之所以说模拟，因为描述为两个相互独立的mysql session(会话)会合适些，登录mysql指令:mysql -h localhost -u root -ptgy123 。 2.使用test库，操作指令:use test;创建实验用表以及插入数据，具体见下文。 3.修改两个客户端的数据库事务隔离级别，进行实验验证。 |
表建语句
#建表语句 create table t_student( id bigint primary key comment '学生主键ID', num varchar(32) comment '学号', stuName varchar(100) comment '学生姓名' )engine=innodb default charset=utf8; #插入语句 insert into t_student(id,num,stuName) values(1000,'No.2014001','唐僧'); insert into t_student(id,num,stuName) values(2000,'No.2014002','孙悟空'); insert into t_student(id,num,stuName) values(3000,'No.2014003','猪八戒'); |
设置事务隔离级别为READ UNCOMMITTED
a.在两个客户端设置事务隔离级别为读未提交。操作指令为：
set session transaction isolation level read uncommitted;
b.在客户端1进行事务操作、插入操作、查询操作、但是不提交，操作指令:
图2-1 客户端1操作
c.在客户端2开始事务、进行查询操作，观察结果。
图2-2 客户端2操作
总结：从图2-2我们可以看到对于客户端1未提交的记录“白龙马”，客户端2查询到了，产生了脏读(例如客户端1回滚了插入操作)。实验完毕，两个客户端执行提交指令：commit;结束事务。
设置事务隔离级别为READ COMMITTED
a.设置两个客户端的事务隔离级别为读提交，操作指令:set session transaction isolation level read committed; 。
b.客户端1开始事务、插入数据、查询操作、不提交，客户端2开始事务，查询操作；客户端1提交事务、查询操作，客户端2查询操作。操作执行见图3-1，图3-2：
图3-1 客户端1的操作未提交，客户端2查询结果
图3-2 客户端1的操作提交，客户端2查询结果
总结：客户端1未提交时，客户端2没有查询到“白骨精”这条记录；当客户端1提交后，客户端2查询到了“白骨精”这条记录。由于客户端2在没有进行事务提交的情况下，两次查询到了不同的结果集，故认为是不可重复读。
设置事务隔离级别为REPEATABLE READ
a.设置两个客户端的事务隔离级别为可重复读，操作指令: set session transaction isolation level repeatable read; 。
b.客户端1开始事务、插入操作、查询操作、不提交，客户端2开始事务，查询操作；客户端1提交事务，查询操作，客户端2不提交事务，查询操作；客户端2提交事务，查询操作。操作执行见图4-1，图4-2，图4-3。
图4-1 客户端1未提交，客户端2查询
图4-2 客户端1提交，客户端2查询
图4-3 客户端2提交，查询
总结：可重复读能够保障客户端2的事务在客户端1提交前后读取一致的行为：客户端1事务未提交，客户端2未读取到新增记录“蜘蛛精”；客户端1提交，客户端2未读取到新增记录“蜘蛛精”；客户端1提交，客户端2提交，读取到新增记录“蜘蛛精”。但是可重复读存在幻读问题，比如客户端1提交了新增记录“蜘蛛精”，客户端2未提交事务，且同样插入一条蜘蛛精记录，但是由于主键是唯一的，客户端2是无法插入蜘蛛精记录的，但是在客户端2确实又看不到数据库里的新增记录蜘蛛精，于是就产生了类似“幻觉”的行为。
设置事务隔离级别为SERIALIZABLE
a.设置两个客户端的隔离级别为序列化/串行化，操作指令： set session transaction isolation level serializable; 。
b.客户端1开始事务、执行更新操作、查询操作，客户端2开始事务，查询操作。操作执行见图5-1,5-2：
图5-1 客户端1未提交，客户端2查询在等待中锁超时报错了
图5-2 客户端1提交，客户端2查询到了更新结果“美女大蜘蛛精”
总结：序列化、串行化读是最安全的事务隔离级别，但是由于串行执行，会阻塞报错锁超时，效率较低。