MySQL数据库事务隔离级别理解
查询与设置事务隔离级别
- 查询当前会话事务隔离级别
# 旧版本
select @@tx_isolation;
# 新版本
select @@transaction_isolation;
- 设置当前会话事务隔离级别(以可重复读为例)
set session transaction isolation level repeatable read;
- 查询系统当前事务隔离级别
# 旧版本
select @@global.tx_isolation;
# 新版本
select @@global.transaction_isolation;
- 设置系统事务隔离级别(以可重复读为例)
set global transaction isolation level repeatable read;
事务隔离级别有哪些?
- read-uncommitted:读未提交
- read-committed:读已提交(不可重复读)
- repeatable-read:可重复读
- serializable:串行化
不同的事务隔离级别会导致哪些问题?
事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
read-uncommitted | 是 | 是 | 是 |
read-committed | 否 | 是 | 是 |
repeatable-read | 否 | 否 | 是 |
serializable | 否 | 否 | 否 |
什么是脏读、不可重复读、幻读?
- 脏读:
某一个 事务A 修改某条记录数据,但未提交。
另一个 事务B 读取该条记录,拿到的是 事务A 修改后的数据。
当 事务A 发生异常回滚后,数据被还原。
那么上述 事务B 获取的数据(事务A 修改后的数据)就是脏数据。即发生了脏读问题! - 不可重复读:
某一个 事务A 修改某条记录数据,暂未提交。
另一个 事务B 读取该条记录(记录1)。
事务A 提交事务。
事务B 再次读取该条记录(记录2)。
此时查看 事务B 读取的 记录1 和 记录2 数据不一致。
上述 事务B 不能重复获取到同一条数据。即产生了不可重复读问题! - 幻读:
某一个 事务A 新增记录,且已提交。
另一 事务B 在 事务A 新增记录前后分别查询所有记录。
两次查询的结果不一致,后一次查询结果集中有新增的记录。
事务B 感觉像是产生幻觉一般(为什么突然多了一条新记录)。即发生了幻读问题!
操作演示
环境搭建及命令解释
# 登录数据库
mysql -uroot -p123455
# 显示所有数据库
show databases;
# 创建数据库
create database transaction_test;
# 进入数据库
use transaction_test;
# 创建表
create table account(
id int auto_increment primary key,
name varchar(15),
balance int)engine=innodb charset=utf8;
# 新增3条记录
insert into account(name,balance) values ('lisi',0);
insert into account(name,balance) values ('wangwu',15900);
insert into account(name,balance) values ('zhangsan',2100);
# 开启事务
start transaction;
# 模拟事务发生异常,回滚命令
rollback;
# 提交事务
commit;
注意。每次演示后都需要把开启的事务回滚或提交!然后开启下一次演示!由于为了方便截图,此处所有的操作并未演示事务的关闭,而是直接进行下一次的事务开启演示。
脏读演示(产生脏读问题)
- 开启两个客户端。分别为客户端A、客户端B。都设置事务隔离级别为 read-uncommitted(读未提交)。客户端A 和 客户端B 同时开启事务并查询两个客户端 account 表的值。
- 在 客户端A 中更新 account 表(此时 客户端A 还没提交)。但是在 客户端B 中就已经可以查询到 客户端A 更新后的数据了。
- 此时 客户端A 模拟发生异常,事务回滚。那么 客户端B 的事务中查询出的数据就是脏数据。
不可重复读演示(产生不可重复读问题)
- 开启两个客户端。分别为客户端A、客户端B。都设置事务隔离级别为 read-committed(读已提交)。客户端A 和 客户端B 同时开启事务并查询两个客户端 account 表的值。
- 在 客户端A 中更新 account 表。这时 客户端A 的事务未提交。客户端B 不能查询 客户端A 更新的数据,解决了脏读问题。
- 客户端A 事务提交。客户端B 执行与上一步相同的查询,结果与上一步不同,即产生了不可重复读问题。
可重复读演示(产生幻读问题)
- 开启两个客户端。分别为客户端A、客户端B。都设置事务隔离级别为 repeatable-read(可重复读)。客户端A 和 客户端B 同时开启事务并查询两个客户端 account 表的值。
- 在 客户端A 中更新 account 表并提交事务。在 客户端B 中查询 account 表记录。与步骤(1)一致。没有出现不可重复读的问题。
- 重新在 客户端A 开启事务,这时,添加一条记录,事务提交。在 客户端B 也同样开启事务进行查询。发现没有 id=4 的记录。于是执行新增却报异常。仿佛发生幻觉,即产生了幻读问题!
串行化演示
- 开启两个客户端。分别为客户端A、客户端B。都设置事务隔离级别为 serializable(读已提交)。客户端A 和 客户端B 同时开启事务并查询两个客户端 account 表的值。
- 在 客户端A 新增一条 id=5 的记录,会发现一直不会执行成功!
此时去 客户端B 提交事务,客户端A才会执行数据插入!解决了幻读问题。
为什么MySQL默认使用repeatable-read而不是serializable?
如果使用 serializable 串行化事务隔离,那么在这个事务没有被提交之前,其他的线程只能等待该事务完成后才能进行操作,这既非常耗时、也非常影响数据库的性能。因此MySQL一般不会用这种事务隔离级别。
总结
不可重复读的和幻读很容易混淆!不可重复读侧重于修改,幻读侧重于新增或删除。InnoDB引擎支持行级锁与表级锁。解决不可重复读的问题只需锁住满足条件的行,解决幻读则需要锁表!