本文详细记录了数据库中事务的隔离级别及其定义,以及每个隔离级别下可能会遇到哪些问题及对应的解决方案和原理,以下内容结合为各大平台的知识点加自己的理解进行的总结,希望大家在读完以后能对事务有个全新的认识~~
1. MySQL事务管理
自动提交
- 在MySQL中,默认DML指令的执行时自动提交的,当我们执行一个DML指令之后,自动同
步到数据库中。
事务管理
开启事务,就是关闭自动提交
- 在开始事务第一个操作之前,执行
start transaction
开启事务 。 - 依次执行事务中的每个DML操作
- 如果在执行的过程中的任何位置出现异常,则执行
rollback
回滚事务 。 - 如果事务中所有的DML操作都执行成功,则在最后执行
commit
提交事务。
事务管理示例sql:
create database db_test3;
use db_test3;
# 库存表
create table stock(
id int primary key auto_increment,
name varchar(200),
num int not null
)
# 订单表
create table order_db(
id int primary key auto_increment,
name varchar(200) not null,
price double,
num int
);
insert into stock(name,num) values('鼠标',10);
insert into stock(name,num) values('键盘',20);
insert into stock(name,num) values('耳机',30);
# 开启事务
start transaction;
# 操作1:扣减库存
update stock set num = num-1 where name = '鼠标';
select aaa; # 此处会执行失败
# 操作2:新增订单
insert into order_db(name,price,num) values('鼠标',20.5,1);
# 事务回滚:清除缓存中的操作,撤销当前事务已经执行的操作
rollback;
# 提交事务: 将缓存中的操作写入数据文件
commit;
2. 事务隔离级别
注:可重复读可以解决大部分的幻读现象,但仍有可能发生幻读(先执行快照读,后执行当前读)
数据库在执行DML语句(update、delete、insert)底层会默认执行一遍当前读
查看与设置隔离级别:
mysql默认的隔离级别:可重复读(REPEATABLE READ)。
- 查看当前会话的隔离级别:select @@transaction_isolation;
- 查看全局的隔离级别:select @@gobal.transaction_isolation;
设置事务隔离级别:
- 会话级:set session transaction isolation level read committed(设置当前会话的隔离级别为读已提交);
- 全局级:set global transaction isolation level read committed(设置全局会话的隔离级别为读已提交);
不同的现象
脏读(Dirty Read):事务读取了其他事务中未提交的数据,当其他事务将未提交的数据进行修改,导致另一个事务读取到的数据是不正确的,这种现象就是脏读,会导致数据的不一致性。
不可重复读(Non-Repeatable Read):指在一个事务内,不同时刻多次读取同一行的数据得到的结果不同。通常发生在一个事务内多次读取同一行数据,但在这些读取过程中,另一个事务修改了该行数据并且已经提交。因此,第一个事务两次读取同一行数据时得到的结果不一致,这种现象就是不可重复读。不可重复读可以导致事务逻辑错误或数据不一致的问题。
幻读(Phantom Read):指在同一个事务中,多次查询同一个范围的数据时,得到的结果集不一致。这是因为在查询期间,另一个事务插入或删除了符合查询条件的数据。通常发生在一个事务内查询某个范围的数据,但在这两次查询之间,另一个事务插入了新的符合查询条件的数据并且已经提交。因此,第一个事务两次查询同一范围的数据时得到的结果集不同,这种现象就是幻读。幻读可能会导致查询结果不一致或业务逻辑错误。
MySQL数据库事务隔离级别:
- 读未提交
- 读已提交 (Oracle数据库默认)
- 可重复读 (MySQL默认级别)
- 串行化
读未提交(read uncommitted)
T2可以读取T1执行但未提交的数据;可能会导致出现脏读、不可重复读和幻读。
//设置全局事务隔离级别为读未提交
set global transaction isolation level read uncommitted;
脏读,一个事务读取到了另一个事务中未提交的数据
演示:
T1事务 |
T2事务 |
mysql> use test_transaction |
|
mysql> use test_transaction |
|
mysql> start transaction; |
|
mysql> start transaction; |
|
mysql> select * from a; |
|
|
|
mysql> insert into a values(4); |
|
mysql> select * from a; |
|
|
通过以上测试,可以看到,A事务读取到了B事务还没有提交的数据。这种现象就是脏读。
读已提交(read committed)
T2只能读取T1已经提交的数据;避免了脏读,但可能会导致不可重复度、幻读。
//设置全局事务隔离级别为读已提交
set global transaction isolation level read committed;
演示:
T1事务 |
T2事务 |
mysql> use test_transaction |
|
mysql> use test_transaction |
|
mysql> start transaction; |
|
mysql> start transaction; |
|
mysql> select * from a; |
|
|
|
mysql> insert into a values(4); |
|
mysql> select * from a; |
|
|
|
mysql> commit; |
|
mysql> select * from a; |
|
|
通过以上测试看出,A事务只能读取到B事务提交之后的数据。这种隔离级别解决了脏读问题,但肯定是存在不可重复读和幻读问题。因为只要事务B进行了增删改操作之后并提交了,事务A读取到的数据肯定是不同的。即:不可重复读和幻读都存在。
可重复读(repeatable read)
T2执行第一次查询之后,在事务结束之前其他事务不能修改对应的数据,但可以操作其他数据;避免了不可重复读,解决了大部分幻读问题,但可能会存在少部分幻读现象。
//设置全局事务隔离级别为可重复读
set global transaction isolation level repeatable read;
演示:
A事务 |
B事务 |
mysql> use test_transaction |
|
mysql> use test_transaction |
|
mysql> start transaction; |
|
mysql> start transaction; |
|
mysql> select empno,ename,sal from emp where empno=7369; |
|
|
|
mysql> update emp set ename='SMITH',sal=8000 where empno=7369; |
|
mysql> commit; |
|
mysql> select empno,ename,sal from emp where empno=7369; |
|
|
通过以上测试得知:当事务隔离级别设置为可重复读时,避免了不可重复读问题。
那么在MySQL当中,当事务隔离级别设置为可重复读时,能够避免幻读问题吗?测试一下:
事务A |
事务B |
mysql> use test_transaction |
|
mysql> use test_transaction |
|
mysql> start transaction; |
|
mysql> start transaction; |
|
mysql> select * from a; |
|
|
|
mysql> insert into a values(5); |
|
mysql> commit; |
|
mysql> select * from a; |
|
|
通过以上测试得知:当事务隔离级别设置为可重复读时,也避免了幻读问题。是完全避免了幻读问题吗?并不是。请看以下测试:
事务A |
事务B |
mysql> use test_transaction |
|
mysql> use test_transaction |
|
mysql> start transaction; |
|
mysql> start transaction; |
|
mysql> select * from a; |
|
|
|
mysql> insert into a values(6); |
|
mysql> commit; |
|
mysql> select * from a for update; |
|
|
通过以上测试得知:当事务隔离级别设置为可重复读,MySQL会尽最大努力避免幻读问题,但这种隔离级别无法完全避免幻读问题。
串行化(serializable)
同时只允许一个事务对数据表进行操作;避免了脏读、虚读、幻读问题,缺点是效率低,因为这种隔离级别会导致事务排队处理,不支持并发。
//设置全局事务隔离级别为串行化
set global transaction isolation level serializable;
演示:
事务A |
事务B |
mysql> use test_transaction |
|
mysql> use test_transaction |
|
mysql> start transaction; |
|
mysql> start transaction; |
|
mysql> select * from a; |
|
|
|
mysql> insert into a values(7); |
|
mysql> select * from a; |
|
|
|
mysql> commit; |
|
|
通过以上测试得知:当事务隔离级别设置为串行化时,事务只能排队执行,不支持并发。
3. 可重复读的幻读问题
通过以上的分析,在MySQL中默认的事务隔离级别为可重复读,完全避免了脏读和不可重复读,在很大程度上避免了幻读问题(并不能完全解决),那么可重复读这一隔离级别是如何解决幻读的呢?方案包括两种:
- 针对快照读(普通的select语句,如:select * from test;),是通过MVCC方式解决了幻读。因为在可重复读隔离级别下,通过快照读查询到的数据跟事务刚启动时查询到的数据是一致的,即使中途进行了DML(insert、update、delete)操作,读出来的结果也是一致的,因此解决了幻读问题。
- 针对当前读(select * from test for update;),是通过next-key lock(记录锁+间隙锁)方式解决了幻读,因为当执行select * from test for update操作时,会为当前查询到的数据加上next-key lock锁,如果有其他的事务想在加了next-key lock锁范围内的数据进行插入、更新或删除时,该语句会被阻塞,无法执行,等释放next-key lock锁后才能继续执行该操作,因此解决了幻读问题。
3.1. 快照读是如何解决幻读的
什么是快照读?普通的select语句都是采用的快照读。顾名思义:在整个事务的处理过程中,执行相同的一个select语句时,每次都是读取的快照。(快照指的是固定的某个时刻的数据,是数据库的一个一致性视图)。也就是说,当事务隔离级别是可重复读,并且执行的select语句是一个普通的select语句时,都会采用快照读的方式读取数据,底层实现原理是:
底层由 MVCC(多版本并发控制)实现,实现的方式是开始事务后,在执行第一个查询语句后,会创建一个
Read View
,后续的查询语句利用这个Read View
,通过这个Read View
就可以在undo log
版本链找到事务开始时的数据,所以事务过程中每次查询的数据都是一样的,即使中途有其他事务插入了新纪录,是查询不出来这条数据的,所以就很好的避免了幻读问题。
演示:
test_transaction数据库的事务隔离级别为可重复读
事务A |
事务B |
mysql> use test_transaction |
|
mysql> use test_transaction |
|
mysql> start transaction; |
|
mysql> start transaction; |
|
mysql> select * from a; //快照读 |
|
|
|
mysql> insert into a values(5); |
|
mysql> commit; |
|
mysql> select * from a; //快照读 |
|
|
3.2. 出现幻读的两种情况
在同一个事务处理过程中,如果前后两次都采用快照读,或者都采用当前读,则不会出现幻读问题。如果第一次使用快照读,后面使用了当前读,则会出现幻读问题。
3.2.1. 第一种产生幻读的场景
A事务与B事务。在A事务中第一次查询使用快照读,B事务插入数据。然后在A事务中第二次查询使用当前读。则会产生幻读现象。演示:
事务A |
事务B |
mysql> use test_transaction |
|
mysql> use test_transaction |
|
mysql> start transaction; |
|
mysql> start transaction; |
|
mysql> select * from a; |
|
|
|
mysql> insert into a values(5); |
|
mysql> commit; |
|
mysql> select * from a for update; // 产生了幻读 |
|
|
3.2.2. 第二种产生幻读的场景
事务A与事务B,在事务A中第一次查询使用快照读,在事务B中插入一条数据,然后在事务A中更新事务B插入的那条记录(执行DML操作,底层默认执行一遍当前读),最后在事务A中再次使用快照读(以当前时刻的数据库状态创建Read View
)。则会发生幻读现象。
事务A |
事务B |
mysql> use test_transaction |
|
mysql> use test_transaction |
|
mysql> start transaction; |
|
mysql> start transaction; |
|
mysql> select * from a; |
|
|
|
mysql> insert into a values(6); |
|
mysql> commit; |
|
mysql> update a set id=100 where id=6; //主要是因为这个SQL语句的执行触发了当前读 |
|
mysql> select * from a; // 产生了幻读 |
|
|
3.3. 总结可重复读的幻读问题
MySQL的可重复读隔离级别(默认隔离级),根据不同的查询方式,分别提出了避免幻读的方案:
- 针对快照读(普通 select 语句),是通过 MVCC 方式解决了幻读。
- 针对当前读(select ... for update 等语句),是通过 next-key lock(记录锁+间隙锁)方式解决了幻读。
举例了两个发生幻读场景的例子。
- 第一个例子:如果事务开启后,先执行快照读,然后这期间如果其他事务插入了一条记录,那么事务后续使用当前读进行查询的时候,就会发现两次查询的记录条目就不一样了,所以就发生幻读。
- 第二个例子:如果事务开启后,先执行快照读,事务B往表中插入一条数据,当事务 A 更新了事务 B 的插入操作(DML操作)时,那么事务 A 前后两次查询的记录条目就不一样了,所以就发生幻读。
所以,MySQL 可重复读隔离级别并没有彻底解决幻读,只是很大程度上避免了幻读现象的发生。要避免这类特殊场景下发生幻读的现象的话,就是尽量在开启事务之后,马上执行 select ... for update 这类当前读的语句,因为它会对记录加 next-key lock,从而避免其他事务插入一条新记录。