导 读
今天遇到一个很有趣的问题:跑python代码程序,使用python程序开启两个session连接数据库,先在第一个session里面对t表查询,然后到第二个session往t表插入一行记录,并commit了;在第一个session去查t表,发现t表中查不到刚插入的这条记录。而我们拿相同的python代码程序,在另一套环境上,也开启2个session,重复上面的操作,结果居然可以查到记录。开发人员认为,我在session 2上数据都插入进去了,并且提交了,为什么sessioin 1查询不到?并且相同的代码,连的同一个mysql环境,怎么两次结果都不一样?
环 境
A环境:MySQL5.7.13 +相同的python代码+ Fedora22操作系统
B环境:MySQL5.7.13 +相同的python代码+ RedHat7操作系统
现象描述
环境A的情况下,下面的实验是模拟python代码程序跑的过程,如需使用python代码测试,详见附录3。
结论:在环境A的情况下模拟,在session 1中无法搜到session2提交的变更。
在环境B的情况下,也去跑这个python脚本,但是跑出来的结果如下:
结论:在环境B的情况下模拟,在session 1中可以搜到session2提交的变更。
问题分析
隔离级别问题
看到这样的情况,我们理所当然的猜测,应该是隔离级别不同导致的,环境A的隔离级别应该是REPEATABLE-READ,可重复读,什么是REPEATABLE-READ?就是当你开启一个事务,读一个数据,而后再次读,再读,再读……只要这个事务没结束,那么读取的数据就是一致的。 所以,环境A下面,session1事务开启,反复查,结果是一致的,这很正常。
而环境B的隔离级别应该是READ-COMMITTED,提交读,什么是提交读?就是只要事务提交了,那你就能读到修改的数据。所以,在环境B的情况下,session1第二次读取的时候,就能读到提交的数据。
好了,现在去数据库看一下,是不是我们猜测的情况。
环境A:
环境B:
事实摆在眼前,结果出乎我们的意料。环境A和环境B都是REPEATABLE-READ,啊啊啊,那到底是为什么呢?
auto commit问题
明明是一模一样的python代码,一样的实验环境,一样mysql版本,一样的执行过程,查询到的结果就是不一致的?
唯一的不同,这2套环境的操作系统不一样,环境A是Fedora22,环境B是redhat7,验证中偶然发现,redhat7的autocommit变量居然还是on的。
默认情况下,python连接MySQL默认是自动开启新事务,也就是说autocommit=OFF的。
好,现在要引入一个MySQL参数,autocommit。
什么是autocommit?
autocommit这个变量表示是否开启自动提交事务模式。如果将这个值设置为1,那么所有变更无需手工提交,每次提交SQL语句,事务就会自动提交,马上生效。如果设置为0,那么你必须使用commit来提交事务或者使用rollback来回滚事务,事务不会自动提交。
问题的原因最终确定:环境B是因为自动提交了事务,session1才在第二次搜索的时候,查到了修改的数据。
详细解释一下环境A和环境B的现象:
环境A:
环境A是Fedora22,autocommit设置为0,表示已经关闭,隔离级别是REPEATABLE-READ。所以在session1中第二次搜索t表的时候无法搜索到新插入的数据,因为两次搜索都在一个事务里面,实现了可重复读。如果要搜到新提交的事务必须键入commit才能搜索到。
环境B:
我们现在来分析情况环境B,环境B是RedHat7,autocommit设置为1,表示事务自动提交开启,隔离级别是REPEATABLE-READ。所以对session1来说第二次去搜索已经相当于已经是第二个事务了,当然就能搜索得到session2提交的数据。
总 结
提 问
通过上述的总结,各位看官可以尝试回答几个关于隔离级别的问题,来验证下对隔离级别和自动事务提交参数的理解。
还是针对这2个session
Q1.在auto_commit=on,隔离级别是REPEATABLE-READ,T3时刻,session1能否看到session2在T2时刻提交的数据?
Q2.在auto_commit=off,隔离级别是REPEATABLE-READ,T3时刻,session1能否看到session2在T2时刻提交的数据?
Q3.在auto_commit=on,隔离级别是READ-COMMITTED,T3时刻,session1能否看到session2在T2时刻提交的数据?
Q4.在auto_commit=off,隔离级别是READ-COMMITTED,T3时刻,session1能否看到session2在T2时刻提交的数据?
回 答
Q1.能。
在auto_commit=on,隔离级别是REPEATABLE-READ,session1能看到session2在T2时刻提交的数据。因为auto_commit=on的时候,每一个语句都是一个全新的事物,所以在T3的时候,session3作为一个新的事务是能查询到,session2在T2时刻提交的数据。
Q2.不能。
在auto_commit=off,隔离级别是REPEATABLE-READ,session1不能看到session2在T2时刻提交的数据。因为auto_commit=off的时候,对session1来说,在T3时刻,因为没有提交,所以事务还是t1时候的那个事务,所以无法查到session2在T2时刻提交的数据。
Q3.能
在auto_commit=on,隔离级别是READ-COMMITTED,session1能看到session2在T2时刻提交的数据。因为auto_commit=1的时候,每一个语句都是一个全新的事物,所以在T3的时候,session1作为一个新的事物是能查询到,session2在T2时刻提交的数据。
Q4.能
在auto_commit=off,隔离级别是READ-COMMITTED,session1能看到session2在T2时刻提交的数据。因为auto_commit=off的时候,虽然在T3的时候,session1还是T1时刻的那个事务,但是,READ-COMMITTED的特性就是可以看到已提交的事务。
附录1:测试环境描述
附录2:表结构
附录3:python代码