文章目录
概念
MySQL四种隔离级别:
- READ UNCOMMITTED (未提交读)
- READ COMMITTED (提交读)
- REPEATABLE READ (可重复读)
- SERIALIZABLE (可串行化)
脏读、不可重复读、幻读
- 脏读:事务可读取其他事务未提交的数据
- 不可重复读:事务A读取事务B修改的数据,在事务B提交前和提交后事务A读取的数据可能不一致
- 幻读:当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入新的记录,当之前的事务再次读取该范围内的记录时,会产生幻行。
一、环境准备
- MySQL8.0
- 数据表
- sql语句如下(示例):
select @@transaction_isolation; // 查看当前的隔离级别
CREATE DATABASE test;
USE test;
CREATE TABLE user(
id int primary key auto_increment,
age int);
二、隔离级别
1.READ UNCOMMITTED (未提交读)
存在脏读情况
脏读情况如下(示例):
事务1 | 事务2 |
---|---|
truncate user; | |
set session transaction isolation level READ UNCOMMITTED; | set session transaction isolation level READ UNCOMMITTED; |
insert into user values(1,10),(2,10),(3,10),(4,10),(5,10); | |
start transaction; | |
start transaction; | |
select * from user where id=1; | |
update user set age=age+1 where id=1; | |
select * from user where id=1; | |
commit; | |
commit; |
2.READ COMMITTED (提交读)
解决了脏读问题,存在不可重复读情况
不可重复读情况如下(示例):
事务1 | 事务2 |
---|---|
truncate user; | |
set session transaction isolation level READ COMMITTED; | set session transaction isolation level READ COMMITTED; |
insert into user values(1,10),(2,10),(3,10),(4,10),(5,10); | |
start transaction; | |
start transaction; | |
select * from user where id=1; | |
update user set age=age+1 where id=1; | |
select * from user where id=1; | |
commit; | |
select * from user where id=1; | |
commit; | |
commit; |
3.REPEATABLE READ (可重复读)
解决了脏读和不可重复读。存在幻读情况。
幻读情况如下(示例):
事务1 | 事务2 |
---|---|
truncate user; | |
set session transaction isolation level REPEATABLE READ; | set session transaction isolation level REPEATABLE READ; |
insert into user values(1,10),(2,10),(3,10),(4,10),(5,10); | |
start transaction; | |
start transaction; | |
select * from user where id<10; select * from user where id=8; | |
insert into user value(8,10); commit; | |
select * from user where id=8; update user set age=age+1; select * from user where id=8; select * from user where id<10; | |
commit; |
4.SERIALIZABLE (可串行化)
最严格的隔离级别,它通过强制事务串行执行,避免上述的幻读问题。上述例子中的事务2的insert into将会一直锁住,直到事务1提交或回滚。
总结
隔离级别 | 脏读可能性 | 不可重复读可能性 | 幻读可能性 | 加锁读 |
---|---|---|---|---|
READ UNCOMMITTED | Yes | Yes | Yes | No |
READ COMMITTED | No | Yes | Yes | No |
REPEATABLE READ | No | No | Yes | No |
SERIALIZABLE | No | No | No | Yes |
参考资料
- 《高性能MySQL第三版》