事务并发访问引起的问题有如下常见的:
- 更新丢失——mysql所有事务隔离级别在数据库层面上均可避免
- 脏读——脏读就是允许读取其他事务未提交的数据,READ-COMMITTED事务隔离级别以上可避免(RC级别)。
- 不可重复读——不可重复读就是事务A多次读取事务B,过程中事务B有更新操作,导致事务A读取的数据不一样,REPEATABLE-READ事务隔离级别以上可避免(RR级别)。
- 幻读——指的是一个事务在前后两次查询同一个范围的时候,后一次查询看到了前一次查询没有看到过的数据行,就好像发生了幻觉一样,破坏了事务的一致性。SERIALIZABLE事务隔离级别可避免,但是会降低事务并发能力。在可重复读隔离级别下(RR),也是可以解决幻读的(但是他的根本职能是解决不可重复读)
一、更新丢失
注:文中的事务就是会话,也是查询,两个事务就开两个查询即可
对于更新丢失的情况我们用一张图表示
可以看到事务A期望的结果应该是1100元,但是实际结果却是1000元,也就是说事务B的更新丢失了。
但是MySQL所有的隔离级别都能避免更新丢失情况。我们用一个测试说明,建表如下:
开启两个查询模拟两个事务,这两个事务在操作前需要关闭自动提交:set autocommit = 0;
;将隔离级别开到最低的RU级别:set session transaction isolation level read uncommitted;
两个都要开始事务:start TRANSACTION;
先是事务1的操作:
# 查询一号的余额,结果为1000
SELECT balance FROM account_innodb where id = 1;
然后事务2开始查询,并操作余额
# 查询一号的余额,结果为1000
SELECT balance FROM account_innodb where id = 1;
# 将一号的余额+100
update account_innodb set balance=1000+100 where id = 1;
# 再次查询余额为1100
SELECT balance FROM account_innodb where id = 1;
# 提交事务
COMMIT;
然后事务1进行操作:
update account_innodb set balance=balance-100 where id = 1;
结果:
此时并没有提交事务,我们撤销操作,进行回滚,然后查询1号的余额
# 事务回滚,即关闭事务
ROLLBACK;
# 查询余额
SELECT balance FROM account_innodb where id = 1;
可以看到结果是事务2存入钱后的余额,其他的隔离级别下的更新丢失不再演示
二、脏读
- 开头我们仍然以上边的两个会话设置为基础,即隔离级别最低,我们看看是不是会发生读取到未提交的事务数据
- 首先之前两个会话都要commit,保证事务已经结束。下边开始
两个会话开启事务:start TRANSACTION;
然后update account_innodb set balance = balance -100 where id = 1;
,更改后的结果为1000,但是我们没有提交,数据库里面并没有真正进行更改,未提交的数据存在redo log文件中,另一个事务查询的话,我们期望的是还是数据库中那个没有变动的数据,我们来测试会话2:
# start TRANSACTION;没有开启事务的记得开启
SELECT balance FROM account_innodb where id = 1;
可以看到结果为1000,发生脏读了。
如何避免呢?开启RC级别及以上就可以了
# 设置成能防止脏读的read COMMITTED,再按照之前步骤进行测试
set session transaction isolation level read committed;
按照之前来一次
结果显示正常了
三、不可重复读
- mysql的默认隔离级别为 repeatable read(RR),该级别就能避免不可重复读。事务1对某一行记录会多次读取,在这些过程中,事务2进来修改了该记录,但是事务1并没有结束事务,然后,事务1再次读取,读到的是事务2提交的结果,这明显不利于当前事务的执行。
我们先基于脏读测试的基础上进行测试,即隔离级别为RC,之前的事务都提交,然后重新开启事务
事务1开启 事务之后执行以下语句
SELECT balance FROM account_innodb where id = 1;
然后事务2执行如下
update account_innodb set balance = balance +200 where id = 1;
# 并提交
commit;
回到事务1
# 再次查询,期望为1100
SELECT balance FROM account_innodb where id = 1;
结果
说明RC及以下无法避免不可重复读
我们将隔离级别切换到repeatable read级别,将两个会话都切换成RR级别:
set session transaction isolation level repeatable read;
然后再按照上边的步骤测试,上边的结果是1300,那么这次事务1最终结果应该是1300,测试结果为:1300
说明避免了
四、幻读
基于上边的测试的基础,隔离级别改变为 read uncommitted,保证事务都已经提交
事务1
# 开启事务后执行范围操作,并上共享锁,锁住查询到的记录行
SELECT balance FROM account_innodb lock in share mode;
事务2在其之后插入一条数据(可以插入,锁住4行都是行锁,不影响新添数据行)
# 开启事务后执行
insert into account_innodb values ('test5',1200);
# 提交
commit;
查看account_innodb表
然后我们用事务1进行全范围更新
# 将工资全部改为1000
update account_innodb set balance=1000;
# 查询是否是4条1000的记录
SELECT balance FROM account_innodb;
很匪夷所思吧,五条记录都被更新(对于事务1来说,不是应该是4条吗?),跟产生幻觉一样,说明在RC级别下产生了幻读。
4.1、RR级别下测试
我们将两个会话隔离级别切换为RR:set session transaction isolation level read COMMITTED;
然后按照之前的方法测试,也是事务1线进行查询,然后事务2进行添加操作,奇怪的事发生了
被阻塞了,说明RR级别可以避免幻读,这是因为mysql采用了gap lock,详情请见数据库之InnoDB可重复读隔离级别下如何避免幻读
4.2、SERIALIZABLE级别下测试
切换级别set session transaction isolation level SERIALIZABLE;
,回滚之前事务ROLLBACK;
,按照RR级别下的测试来一遍,到了事务2进行插入的时候:
五、具体对比图
如有问题,请及时指出