本文主要验证下mysql的集中隔离级别下产生的问题,使用的版本是8.0.18。
四种隔离级别
Mysql的innodb引擎支持事务,默认有如下四个隔离级别,隔离程度由低到高。
- read-uncommitted :未提交读
- read-committed :已提交读
- repeatable-read :可重复读
- serializable :串行化,要求两个事务串行执行
不同的隔离级别下会产生不同的问题,脏读,不可重复读,幻读,对应关系具体如下:
脏读 | 不可重复读 | 幻读 | |
---|---|---|---|
未提交读 | 是 | 是 | 是 |
已提交读 | 否 | 是 | 是 |
可重复读 | 否 | 否 | 是/否 |
串行化 | 否 | 否 | 否 |
- 脏读 :事务B读取了事务A没有提交的数据,叫做脏读;比如AB两个事务都在操作数据库,事务A修改了一条数据值由10改为5,但是没提交事务,此时还事务B去查询,能查到这个5;如果此时事务A回滚回10,那么事务B插到的5就是脏数据,叫做脏读。
- 不可重复读:一个事务开始后,在提交之前,多次查询同一条数据,值不相同,叫做不可重复读。比如,事务B查询一条数据值为10,还没提交事务;此时事务A修改值为5,然后事务A再次查询得到5;上一次是10,下一次是5,产生了不可重复读的问题。
- 幻读:一个事务开始后,在提交之前,多次查询同一张表的数据,多次查询数目不一致,产生幻读;比如,事务B第一次查询表得到2条数据,此时事务A插入一条数据,然后事务B再次查询表,回得到3条数据;第一次2条,第二次变成3条,产生了幻读。
注意:之前很多博客都说repeatable-read 会产生幻读问题,但经过实际验证和查阅资料,目前mysql版本已经在repeatable-read 这个级别部分解决了幻读的问题。 为什么是部分呢?因为事务B如果只是多次查询,则不会出现幻读问题,但是当事务B中间发生了update等当前读的操作,后续再select就会发生幻读。
下面针对脏读,幻读,不可重复读进行演示。
准备:
初始化一张表user,插入几条数据;
在查询sql的时候,sql语句末尾使用分号,这时数据库回自动提交事务,为了演示,需要关闭自动提交;
可使用如下命令查看自动提交关闭情况:
show global variables like ‘autocommit’;
使用如下命令关闭自定提交:
set autocommit=0;
然后,修改数据库默认隔离级别,innodb搜索引擎默认隔离级别是可重复读(repeatable-read),可使用如下命令查询:
select @@transaction_isolation; (对于比较老的版本是 select @@tx_isolation;)
使用如下命令修改隔离级别:
set transaction_isolation=‘read-uncommitted’;
set transaction_isolation=‘read-committed’;
set transaction_isolation=‘repeatable-read’;
set transaction_isolation=‘serializable’;
下面正式开始,先说演示脏读,
我们开两个会话窗口,左边记为事务A,右边记为事务B;整体上在事务A中做修改,B中做查询,看看B中产生的脏读,不可重复读,幻读等问题。
脏读
先设置隔离级别为未提交读read-uncommitted,
开始数据库表第1条数据的age值为27,在事务A中更新为17,但是事务A不提交;此时事务B去查询这条记录,得到了A没提交的17;
如果此时A回滚,或者将age改为别的值,那么B读取到的这个17就是脏数据,产生了脏读。
解决脏读
要解决脏读很简单,将隔离级别设置为已提交读即可,因为在事务B中查询验证,只要将B的隔离级别设置为read-committed:
此时事务A再次更新age,由17改为37,然后B去查询,得到的是更新之前的数据17,避免了脏读;
如果B要读取更新之后的数据,只能等A提交事务,也就是commit;
下面把A提交,然后B就查到了更新后的37 。
不可重复读
紧接着上面,虽然避免了脏读,但是我们发现,事务B在一次事务期间(因为B始终没有运行commit命令,所以一直处于同一个事务没结束),查询同一条数据,得到了多个个不同的值,比如我在A中再次更新age为47,
事务B一开始是37,后来再次查询,得到了47,这就是不可重复读。
解决不可重复读
要解决这种不可重复读,需要将事务隔离级别设置为可重复读 repeatable-read ,
我们将事务B的隔离级别设置为了可重复读,
然后事务A更新age为57,然后开启事务查询,得到57;
然后事务A更新age为67,此时B再次查询,得到的依然是57,避免了不可重复读。
幻读
查询期间 ,其他事务插入操作产生的幻读,但是innodb在可重复读隔离级别已经解决,证明如下:
事务B开始后,先查询到了所有的8条数据,
然后事务A插入一条新数据“冯大臣”,commit事务,
然后事务B再次查询,并没有查到新插入的数据,说名mysql的innodb引用的可重复读已经解决了幻读的问题。具体使用的是间隙锁解决,就是在事物B开始了范围查询后,mqsql就锁住了0到10之间的行数,事物A往这个范围插入数据就会失败。
那么把隔离级别改回已提交读,这个隔离级别会产生幻读,如下:(这里删除了“冯大臣”这条数据),
事务B开始查询到了2条数据,事务还没提交,
然后A插入一条新数据,提交事务,
然后事务B再次查询,得到了3条数据,这就是幻读。
串行化
串行化的隔离级别,要求两个事务串行执行,这里也演示下:
两个会话都要设置为’serializable’;
首先,事务B先开始事务,进行了范围查询,范围是id<10,
然后事务A更新id=1的数据,这时发现,跟新的动作被阻塞,这就是串行化,A必须等到B提交事务后,才能继续;
下面我们提交事务B,
然后就会发现A的更新成功了,耗时39.75s,因为中间被阻塞了。
本文就先验证到这里,对于mysql如何实现这几种隔离级别,也就是MVCC原理,后续在分析。