记一次使用 MySQLdb 事务时，select 出现的 bug

场景还原

为了能够清楚的描述这个问题，我们写一个简单的服务，语言使用 python，框架使用 tornado，数据库使用 mysql，数据库连接使用 MySQLdb。

在数据库里有这样一张表：

1
2
3
4
5

drop table if exists singers;
CREATE TABLE singers(
    id int PRIMARY KEY AUTO_INCREMENT,
    name char(20) NOT NULL
);

而我们的服务器代码也是很简单的, 根本没什么服务可言，就是收到访问，就读取 id 为 1 的歌手姓名，并打印：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

import MySQLdb
import tornado.ioloop
import tornado.web

host = '127.0.0.1'
user = 'root'
passwd = '627'
port = 3306
db = 'test'

conn = MySQLdb.connect(host=host, user=user, passwd=passwd, port=port, db=db)

class MainHandler(tornado.web.RequestHandler):
    def get(self):
        cur = conn.cursor()
        cur.execute("select name from singers Where `id` = 1")
        res = cur.fetchone()
        print res
        self.write("ok")

application = tornado.web.Application([
    (r"/", MainHandler),
])

if __name__ == "__main__":
    application.listen(8888)
    tornado.ioloop.IOLoop.instance().start()

并且我们的数据库中会有这样一条记录：

+----+------------+
| id | name       |
+----+------------+
|  1 | jay        |
+----+------------+

在 shell 请求此服务和打印的结果：

curl http://localhost:8888
(jay,)

现在我们保持服务器开启，在 mysql 中输入如下命令：

update singers set name = 'leehom' where id = 1;

再在 shell 请求此服务和打印的结果：

curl http://localhost:8888
(jay,)

好的，bug 出现了！那么有两种方式解决它，第一种是重启服务器，这种方法可以忽略了。第二种就是将代码修改成这种方式：

1
2
3
4

cur = conn.cursor()
cur.execute("select name from singers Where `id` = 1")
res = cur.fetchone()
print res

1
2
3
4
5

cur = conn.cursor()
cur.execute("select name from singers Where `id` = 1")
res = cur.fetchone()
conn.commit()
print res

注意那条 commit 语句，本文其余部分解读此问题原因。

什么是事务

事务（Transaction）是并发控制的基本单位。是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

比如一个表在更新插入语句中，会经历 a，b，c，d，e 五个状态，那么如果我将 b->c->d 的操作放到一个事务中，那么外界看到的就是 a 状态或者是 e 状态，再不会看见其它状态。

a -> b -> c -> d
 -> e

事务支持

首先要了解到，Innodb 支持事务， MyISAM 不支持事务。

而且 mysql 的事务是默认是打开 autocommit 的，但是我们使用的 python 库，mysqldb 却是默认关闭 autocommit，所以你需要自己去 commit，自己去 rollback。

回到问题

其实最主要的原因是我们相当然的认为这是一个读（查询）操作，我们没有必要提交。

但是首先，我们的代码在不停的复用同一条连接，这是问题的起因之一。

MySQL 事务的默认隔离级别是 repeatable read（重复读），而这却会引起 phantom read（幻像读）。

幻像读（phantom read）：在同一事务中，同一查询多次进行时候，由于其他插入操作（insert）的事务提交，导致每次返回不同的结果集。

而 innodb 为了防止幻读，会在一个事务中，使所有 select 的结果保持与第一次 select 时的 snapshot 一致，这样其他事务的插入是不会被感知到的。

所以对于一个只有读的事务，我们也应该及时提交后再读，使 snapshot 刷新。

http://www.pulpcode.cn/2015/05/09/bug-for-mysql-python-cursor-commit/index.html

sohu-dba