MySQL技术|事务已提交另外会话查询不到的问题解析

开发人员遇到一个问题:跑python代码程序,使用python程序开启两个session连接数据库,在第二个session往t表插入一行记录,并commit了;在第一个session去查t表,发现t表中查询不到刚插入的这条记录记录。

导读

开发人员遇到一个问题:跑python代码程序,使用python程序开启两个session连接数据库,在第二个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时刻提交的数据。

Q3.能
在auto_commit=off,隔离级别是READ-COMMITTED,session1能看到session2在T2时刻提交的数据。因为auto_commit=off的时候,虽然在T3的时候,session1还是T1时刻的那个事务,但是,READ-COMMITTED的特性就是可以看到已提交的事务。

附录1:测试表结构

CREATE TABLE `qb_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(24) NOT NULL DEFAULT '',
  `auth` int(11) NOT NULL,
  `createtime` datetime NOT NULL,
  `password` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

附录2:测试python代码

import MySQLdb

ip = '127.0.0.1'
passwd = '111'
user = 'test'
db = 'test'

conn_1 = MySQLdb.connect(host=ip, user=user, passwd=passwd, db=db)
cursor_1 = conn_1.cursor(cursorclass=MySQLdb.cursors.DictCursor)

conn_2 = MySQLdb.connect(host=ip, user=user, passwd=passwd, db=db)
cursor_2 = conn_2.cursor(cursorclass=MySQLdb.cursors.DictCursor)

# Important
cursor_1.execute('SELECT * FROM qb_users')

cursor_2.execute(
    "INSERT INTO qb_users(username, password, auth, createtime) VALUES ('111','222',1,'2016-01-01 00:00:00');")
user_id = cursor_2.lastrowid
conn_2.commit()

print user_id

cursor_1.execute('SELECT * FROM qb_users where id=%d' % user_id)
print cursor_1.fetchall()

发表评论

电子邮件地址不会被公开。 必填项已用*标注