MySQL的四种隔离级别
MySQL的四种隔离级别
SQL标准定义的四个隔离级别为:
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
READ UNCOMMITTED | 可能 | 可能 | 可能 |
READ COMMITTED | 不可能 | 可能 | 可能 |
REPEATABLE READ | 不可能 | 不可能 | 可能 |
SERIALIZABLE | 不可能 | 不可能 | 不可能 |
查看当前的事务隔离级别:
SELECT @@global.tx_isolation;
修改当前事务隔离级别:
set global transaction isolation level read committed;
my.inf文件的[mysqld]节里类似如下设置该选项:
transaction-isolation = READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE
1、 脏读:A,B两事务,A事务会读取到B事务未提交的数据,然后B因为某些原因回滚数据,所以A就读取了B没有提交的数据,也称脏数据。
2、 不可重复读:在A事务中对同一数据两次查询不一致,可能原因是在A事务提交之前B事务对该数据进行了操作
3、 幻读:类似于不可重复读,都是在一个事务周期内读的数据不一致,区别在于幻读是侧重于插入操作带来的影响,而不可重复读是编辑或者删除带来的影响
通过一个例子模拟脏读:
以下例子没有声明都是设置当前事务隔离为READ-UNCOMMITTED。
事务1:开启事务并且新插入一条数据,但是并没有提交
事务2读取到的数据:
如上设置autocommit为0需要手动开启和提交事务,在事务1中我们新插入一条数据,但是并没有提交,在事务2中却读到了这条数据
不可重复读问题模拟:
事务2开启事务并且修改一条记录,提交事务:
看看事务1整个周期内两次读取的数据不一致:
最后模拟一下幻读问题:
事务2:
事务1:
下面看看innodb默认的事务隔离级别REPEATABLE READ能防止什么:
首先看看脏读问题:
设置隔离级别为REPEATABLE READ
事务2开启一个事务并且往表t1新增一条记录:
事务1读取的记录:
如上显示,并没有发生脏读问题
下面看看不可重复读问题:
如上并没有出现不可重复读现象
在来看看幻读现象:
事务1 | 事务2 |
---|---|
set autocommit=0; | set autocommit=0; |
start transaction; | start transaction; |
select * from t1; | select * form t1; |
” | insert into t1 values(5,’lanco3’); |
select * from t1 | ” |
” | commit |
select * from t1 | ” |
insert into t1 values(5,’lanco3’); ERROR 1062 (23000): Duplicate entry ‘5’ for key ‘PRIMARY’ | ” |
如上发现innodb的默认事务隔离级别并不能防止出现幻读,在事务2插入新记录并且提交之后,在事务1的周期上虽然没有查询出该条记录,但是事务1在插入新纪录时候报错有该条记录
如上出现的问题其实和锁有关,关于锁的问题,可以参考我的这篇文章
例如上面为什么阻止幻读的隔离级别比不可重复读的高?因为innodb是行级锁,不可重复读是针对正在修改或删除的数据行加锁,但是并不是表锁,我还可以对这个表进行插入操作,所以会出现幻读
以上,不同事务隔离级别,其实是一致性和并发性的一种权衡与折衷