记一次pymysql查询不到表中最新插入的数据的问题

问题

本周工作中遇到一个问题，同事用Python和公司的db客户端在团队的测试框架上实现连接池功能，db连接用的是pymysql。但在跑demo时，出现了诡异的现象：

• 多次select查询表中数据
  所有的查询结果都是准确无误的。eg：（以下sql脱敏处理过，和真实的sql不一致）

select * from pay p where p.pay_id = '1111111111'

• 先写库（调服务接口去下单），再去db读刚刚写入的订单数据，eg：（'<#pay_id>'为接口返回的刚刚写入库中的订单id）

select * from pay p where p.pay_id = '<#pay_id>'

诡异的事发生了。连接池中的连接只有在第1次去查询的时候能查到刚刚落库的订单数据，之后就查不到了（查询结果为空）。比如连接池大小设置为2。那么只有前2次查询能查到最新写入的数据，第3次开始就查不到最新的数据了。

解决

之前自己对连接池和mysql也没有很深入的理解，仅仅是简单会用，书本上学过的基础理论读完就忘记了。所以，一开始我们怀疑是连接池的实现有问题（无知啊...）。但经过反复打断点，看连接池底层源码后，发现连接池的功能是没问题的。然后怀疑是不是从池中取连接时的线程锁没有生效，但是demo中的case都是顺序执行的，只有1个线程，可以排除线程不安全的问题，即问题不在线程锁上。再然后怀疑是公司的db主-从同步有延迟，因为写操作走的主库，读操作是走的从库。同事联系公司的dba同学，查了半天，没发现有问题。而且理论上，即使有延迟，也不可能是每次必现，否则公司的rd们早就炸翻天了。很诡异的问题...

然后自己就回想，为什么连续的只读操作就没问题，但凡涉及到写-读操作就有问题呢？把sql改一下试试，不用接口返回的订单id作为查询条件去查库，而是通过时间去查库中的最新订单，看与刚刚下过的订单数据是否一致。

select * from pay p order by p.create_time desc limit 1

结果诡异的现象出现了，比如连接池大小设置为2，现在有5条下单用例，结果如下：

第1条用例（使用的是池中的第1个mysql连接）：
下单的订单id为：1111111111
查得库中最新一条订单的id为：1111111111
第2条用例（使用的是池中的第2个mysql连接）：
下单的订单id为：222222222
查得库中最新一条订单的id为：222222222
第3条用例（使用的是池中的第1个mysql连接）：
下单的订单id为：333333333
查得库中最新一条订单的id为：1111111111
第4条用例（使用的是池中的第2个mysql连接）：
下单的订单id为：444444444
查得库中最新一条订单的id为：222222222
第5条用例（使用的是池中的第1个mysql连接）：
下单的订单id为：555555555
查得库中最新一条订单的id为：1111111111

每个连接，在第2次开始，查到的数据都和第1次使用时一样，好像有“缓存”一般。于是乎谷歌"pymysql query cached"。结果找到答案了：
pymysql-apparently-returning-old-snapshot-values-not-rerunning-query
Without autocommit SELECT returns old data. #390
初始化新连接时，要将autocommit置为True，默认为False。自己试了下，果然置为True后，就能查到最新写入的数据了。

db_conf = {
  'jdbcref': res.jdbcref,  # 公司的jdbc url，等效于host, port, user, password, db
  'pool_size': MYSQL_POOL_SIZE,
  'pool_reset_session': False,
  'autocommit': True
}

原理

以往自己的认知上，跟事务相关的dml操作才涉及到commit，为啥select也要commit呢？
看到上面两个链接中都提到了REPEATABLE READ的概念，自己深挖了下后，终于解决了自己的困惑，现总结如下。

基本概念

首先了解下几个概念：

事务

事务是可以提交或回滚的原子操作单元。MySQL中只有Innodb（MySQL默认的引擎）数据库引擎才支持事务，事务具有ACID特征：

• atomicity（原子性）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

• consistency（一致性）：在每次提交或回滚之后，以及在事务进行过程中，数据库始终处于一致状态。如果跨多个表更新相关数据，则查询将看到所有表中的旧值或新值，而不是旧值和新值的混合。

• isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括：
  read uncommitted（读未提交内容）->read committed（读已提交内容）->repeatable read（可重复读）->serializable（串行）。
  隔离级别依次增强，但是导致的问题是并发能力的减弱。

• durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

脏读、幻读、不可重复读

• 脏读(dirty read) - 读取未提交数据
  事务A读取事务B尚未提交的数据，此时如果事务B发生错误并执行回滚操作，那么事务A读取到的数据就是脏数据。
  脏读在read uncommitted隔离级别出现。

• 不可重复读（non repeatable read）- 前后多次读取，数据内容不一致
  不可重复读是指同一个事务中对同一笔数据进行读取，每次读取结果都不同。比如事务A在事务B的更新操作之前读取一次数据，在事务B更新操作提交后再读取同一笔数据，两次查询的结果不同，即数据和之前不一样了。
  不可重复读在read uncommitted，read committed隔离级别中出现。

• 幻读（phantom）- 前后多次读取，数据总量不一致
  事务A在执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，此时事务B执行了新增数据的操作并提交后，这时事务A再次读取的数据总量和之前统计的不一样，就像产生了幻觉一样，平白无故的多了几条数据，称为幻读。
  幻读在read uncommitted，read committed和repeatable read隔离级别中出现。

不可重复读和幻读的区别：
不可重复读是指事务A前后两次读取同一行数据的过程中，因为事务B对该行数据做了update的提交，导致事务A前后两次查询结果不一致。
幻读是指事务A前后两次条件相同的查询过程中，因为事务B的insert/delete提交，导致事务A第2次查询结果相对第1次结果出现了新行或旧行丢失。
即另一个事务的update操作会导致不可重复读，insert/delete会导致幻读。

事务隔离级别

事务隔离分以下级别：

隔离级别	概念	脏读	不可重复读	幻读
read uncommitted（读未提交内容）	最弱的隔离级别，一个事务能看到其他事务未提交的数据.	yes	yes	yes
read committed（读已提交内容）	一个事务只能看到其他事务已提交的数据	no	yes	yes
repeatable read（可重复读）	InnoDB的默认隔离级别。 一个事务的查询不会受到另一个事务update操作的影响，事务的所有查询都读取该事务启动时刻的数据快照，即两次读取的同一行数据是一致的。 可以避免不可重复读，但仍会出现幻读	no	no	yes
serializable（串行）	最严格的隔离级别。 事务都是串行执行，读数据也会加锁，读会阻塞写，写也会阻塞读。 可能导致大量的超时现象和锁竞争	no	no	no

快照（snapshot）

某一时刻的数据，即使后续其他事务更新了数据，该时刻的数据快照仍保持不变。被特定的隔离级别使用，以允许一致性读。

一致性读（consistent read）

也称为快照读。InnoDB确保并发时，事务A不会读取由事务B更新的信息，即使事务B已提交。原因是一致读使用基于某个时间点的数据快照。如果查询的数据已被另一个事务更改，则根据撤消日志（undo log）的内容重建原始数据。
数据快照的时间选取：

隔离级别	快照时间
repeatable read	事务中第一次读操作的时刻
read committed	事务中每次快照读时重置快照时间

一致性读是InnoDB在read committed和repeatable read隔离级别中select语句的默认模式。

解析

ok，根据以上概念，查了下数据库的事务隔离级别，果然是repeatable read：

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

现在回到最初的问题：

基于InnoDB默认的repeatable read事务隔离和select为快照读可知，连接池中的连接在初始化时，没有设置autocommit为True，会导致复用同一个连接的多次select查询其实都在一个事务内，且都为快照读，这样，每个select查的数据源并不是表中的最新数据，而是第1个查询时的快照，因此导致查不到表中其他事务（下单接口的写入操作）写入的最新数据。
将autocommit设置为True后，每个事务中只有1个查询，下一个查询属于一个全新的事务，这样就能读到新事务开始前的最新数据了。

最后

最开始在网上搜autocommit的那两个链接提到repeatable read后，我在网上搜了些repeatable read的中文资料（里面并没有提到快照，百度的资料果然不靠谱...），就解答了自己为啥每次查询的结果好似有缓存一样的困惑。但是又产生了新的困惑，repeatable read不能避免幻读，那理论上，我的问题应该是符合幻读的。后来看官方文档，才发现repeatable read中提及了快照和一致性读的概念，才解答了自己的疑惑。
关于一致性读背后的原理，又涉及到mvcc的概念，为什么我的问题不属于幻读，感兴趣的同学可以参考下面2篇文章：
MYSQL MVCC实现原理
MVCC 能解决幻读吗？