MySQL的脏读、幻读、不可重复读
脏读
现在有两个事务在操作table表,事务B修改了id=2的name字段为李老四,但是没有提交,事务A查询id=2的数据,得到name为李老四;事务B发生回滚,id=2的数据的name又变回李四,这时候事务A得到的name的值还是李老四,也就是说事务A读取到了别的事务未提交的数据,这就是脏读。
幻读
事务A,查询table表,获取到一条id=2,name='李四’的数据,紧接着,事务Adelete掉了table的数据,这时候table应该为空;在A处理其他任务时,事务B向table表中添加了一条id=3,name='王五’的数据,等事务A再次查询table表时,发现多出来一条数据,一次事务前后数据量发生变化,这就是幻读。
不可重复读
事务A查询table表中id=2的数据,得到name的值为李四,接着事务A处理其他任务,期间事务B修改了id=2的数据的name为李老四,并且提交。等事务A再一次查询table中id=2的数据,发现name的值变了为李老四。一个事务前后两次查询同一条数据,值不相同,这就是不可重复读。
小结
脏读:指读取到了其他事务正在处理还未提交的数据
幻读:指在并发情况下,新增、删除这种会产生数据量变化的操作时,另一个事务前后查询相同数据时不符合预期
不可重复读:指并发更新时,另一个事务前后查询相同数据时,数据不符合预期
事务隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 | 加锁读 |
---|---|---|---|---|
READ UNCOMMITTED | 是 | 是 | 是 | 否 |
READ COMMITTED | 否 | 是 | 是 | 否 |
REPEATABLE READ | 否 | 否 | 是(InnoDB除外) | 否 |
SERIALIZABLE | 否 | 否 | 否 | 是 |
如何修改隔离级别
# 获取当前事务隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';
# 设置当前会话事务隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL 级别;
MVCC
在MySQL InnoDB存储引擎下READ COMMITTED、REPEATABLE READ(多版本并发控制)进行并发事务控制,MVCC是基于“数据版本”对并发事务进行访问。
- RR隔离级别
- 结果:select 1,name=‘王五’;select 2,name=‘王五’
- 过程:select1在查询时事务A已经提交,事务B未提交,所以name为王五,select2查询时,即便事务B已提交,由于隔离级别为可重复读,所以name还是王五
- RC隔离级别
- 结果:select1,name=‘王五’;select2,name=‘王老五’
- 过程: select1在查询时事务A已经提交,事务B未提交,所以name为王五,select2查询时,事务B已提交,隔离级别又是读已提交,所以结果为name=‘王老五’
基于UNDO_LOG版本链
每一条数据链都会保存修改这条数据时的事务的id(自增),以及上一版本的数据链的地址和本次修改后的表数据的值。
ReadView
ReadView是“快照读”,SQL执行时MVCC提取数据的依据。快照读就是普通的select查询,当前读指在执行insert、update、delete、select…for…update、select…lock in share mode等语句时进行数据读取的方式。
ReadView结构
Read View是一个数据结构:
字段名 | 含义 |
---|---|
trx_list | 当前活跃的事务id集合 |
up_limit_id | 最小活跃事务id |
low_limit_id | 预分配事务id,当前最大事务id+1 |
版本链数据访问规则:
- 判断DB_TRX_ID < up_limit_id,如果成立,则当前事务可以看到DB_TRX_ID所在的记录,反之则进行下一个判断
- 判断DB_TRX_ID >= low_limit_id,如果成立,则代表DB_TRX_ID所在的记录在ReadView生成后才出现的,那么对于当前事务肯定是不允许访问,如果不成立,进入下一步判断
- 判断DB_TRX_ID是否存在与活跃事务集合trx_list中,如果在,说明ReadView生成时,这个事务还是活跃的,还没有commit提交修改的数据,当前事务不允许访问,如果不在,则说明ReadView生成时,已经提交了修改结果,可以访问。
读已提交(RC)
在每一次执行快照读时生成新的ReadView。
分析事务D两次查询时的ReadView
- select1
trx_list={2,3,4},
up_limit_id=2,
low_limit_id=5
- select2
trx_list={3,4},
up_limit_id=3,
low_limit_id=5
在select1时,当前事务DB_TRX_ID=3,up_limit_id=2,DB_TRX_ID < up_limit_id不成立,进行下一步判断;low_limit_id=5,DB_TRX_ID >= low_limit_id不成立,所以进行第三步判断;DB_TRX_ID存在于trx_list中,是活跃的,不允许访问,接着同样的方法分析DB_TRX_ID=2。当DB_TRX_ID=1时,DB_TRX_ID < up_limit_id成立,所以可以访问。所以查到的数据是name=‘王五’。
在select2时,当前事务DB_TRX_ID=3,up_limit_id=2,DB_TRX_ID < up_limit_id不成立,进行下一步判断;low_limit_id=5,DB_TRX_ID >= low_limit_id不成立,所以进行第三步判断;DB_TRX_ID存在于trx_list中,是活跃的,不允许访问,接着同样的方法分析DB_TRX_ID=2。DB_TRX_ID=2时,前两步判断不成立,第三步时,DB_TRX_ID=2不存在于trx_list中,说明DB_TRX_ID=2的数据已经是commit的,所以可以访问,所以结果是name=‘王老五’。
可重复读(RR)
和上述RC过程一样,不同点在于,RR隔离级别,在一次事务中只在第一次快照读的时候生成ReadView,后面的快照读都会服用第一次生成的ReadView,所以,select1和select2的结果都是name=‘王五’。(特例:如果在两次快照读之间,存在了当前读,那么ReadView会重新生成,导致产生幻读)