問(wèn)題

本周工作中遇到一個(gè)問(wèn)題，同事用Python和公司的db客戶端在團(tuán)隊(duì)的測(cè)試框架上實(shí)現(xiàn)連接池功能，db連接用的是pymysql。但在跑demo時(shí)，出現(xiàn)了詭異的現(xiàn)象：

• 多次select查詢表中數(shù)據(jù)
  所有的查詢結(jié)果都是準(zhǔn)確無(wú)誤的。eg：（以下sql脫敏處理過(guò)，和真實(shí)的sql不一致）

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

• 先寫(xiě)庫(kù)（調(diào)服務(wù)接口去下單），再去db讀剛剛寫(xiě)入的訂單數(shù)據(jù)，eg：（'<#pay_id>'為接口返回的剛剛寫(xiě)入庫(kù)中的訂單id）

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

詭異的事發(fā)生了。連接池中的連接只有在第1次去查詢的時(shí)候能查到剛剛落庫(kù)的訂單數(shù)據(jù)，之后就查不到了（查詢結(jié)果為空）。比如連接池大小設(shè)置為2。那么只有前2次查詢能查到最新寫(xiě)入的數(shù)據(jù)，第3次開(kāi)始就查不到最新的數(shù)據(jù)了。

解決

之前自己對(duì)連接池和mysql也沒(méi)有很深入的理解，僅僅是簡(jiǎn)單會(huì)用，書(shū)本上學(xué)過(guò)的基礎(chǔ)理論讀完就忘記了。所以，一開(kāi)始我們懷疑是連接池的實(shí)現(xiàn)有問(wèn)題（無(wú)知啊...）。但經(jīng)過(guò)反復(fù)打斷點(diǎn)，看連接池底層源碼后，發(fā)現(xiàn)連接池的功能是沒(méi)問(wèn)題的。然后懷疑是不是從池中取連接時(shí)的線程鎖沒(méi)有生效，但是demo中的case都是順序執(zhí)行的，只有1個(gè)線程，可以排除線程不安全的問(wèn)題，即問(wèn)題不在線程鎖上。再然后懷疑是公司的db主-從同步有延遲，因?yàn)閷?xiě)操作走的主庫(kù)，讀操作是走的從庫(kù)。同事聯(lián)系公司的dba同學(xué)，查了半天，沒(méi)發(fā)現(xiàn)有問(wèn)題。而且理論上，即使有延遲，也不可能是每次必現(xiàn)，否則公司的rd們?cè)缇驼ǚ炝?。很詭異的?wèn)題...

然后自己就回想，為什么連續(xù)的只讀操作就沒(méi)問(wèn)題，但凡涉及到寫(xiě)-讀操作就有問(wèn)題呢？把sql改一下試試，不用接口返回的訂單id作為查詢條件去查庫(kù)，而是通過(guò)時(shí)間去查庫(kù)中的最新訂單，看與剛剛下過(guò)的訂單數(shù)據(jù)是否一致。

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

結(jié)果詭異的現(xiàn)象出現(xiàn)了，比如連接池大小設(shè)置為2，現(xiàn)在有5條下單用例，結(jié)果如下：

第1條用例（使用的是池中的第1個(gè)mysql連接）：
下單的訂單id為：1111111111
查得庫(kù)中最新一條訂單的id為：1111111111
第2條用例（使用的是池中的第2個(gè)mysql連接）：
下單的訂單id為：222222222
查得庫(kù)中最新一條訂單的id為：222222222
第3條用例（使用的是池中的第1個(gè)mysql連接）：
下單的訂單id為：333333333
查得庫(kù)中最新一條訂單的id為：1111111111
第4條用例（使用的是池中的第2個(gè)mysql連接）：
下單的訂單id為：444444444
查得庫(kù)中最新一條訂單的id為：222222222
第5條用例（使用的是池中的第1個(gè)mysql連接）：
下單的訂單id為：555555555
查得庫(kù)中最新一條訂單的id為：1111111111

每個(gè)連接，在第2次開(kāi)始，查到的數(shù)據(jù)都和第1次使用時(shí)一樣，好像有“緩存”一般。于是乎谷歌"pymysql query cached"。結(jié)果找到答案了：
pymysql-apparently-returning-old-snapshot-values-not-rerunning-query
Without autocommit SELECT returns old data. #390
初始化新連接時(shí)，要將autocommit置為T(mén)rue，默認(rèn)為False。自己試了下，果然置為T(mén)rue后，就能查到最新寫(xiě)入的數(shù)據(jù)了。

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

原理

以往自己的認(rèn)知上，跟事務(wù)相關(guān)的dml操作才涉及到commit，為啥select也要commit呢？
看到上面兩個(gè)鏈接中都提到了REPEATABLE READ的概念，自己深挖了下后，終于解決了自己的困惑，現(xiàn)總結(jié)如下。

基本概念

首先了解下幾個(gè)概念：

事務(wù)

事務(wù)是可以提交或回滾的原子操作單元。MySQL中只有Innodb（MySQL默認(rèn)的引擎）數(shù)據(jù)庫(kù)引擎才支持事務(wù)，事務(wù)具有ACID特征：

• atomicity（原子性）：一個(gè)事務(wù)（transaction）中的所有操作，要么全部完成，要么全部不完成，不會(huì)結(jié)束在中間某個(gè)環(huán)節(jié)。事務(wù)在執(zhí)行過(guò)程中發(fā)生錯(cuò)誤，會(huì)被回滾（Rollback）到事務(wù)開(kāi)始前的狀態(tài)，就像這個(gè)事務(wù)從來(lái)沒(méi)有執(zhí)行過(guò)一樣。

• consistency（一致性）：在每次提交或回滾之后，以及在事務(wù)進(jìn)行過(guò)程中，數(shù)據(jù)庫(kù)始終處于一致?tīng)顟B(tài)。如果跨多個(gè)表更新相關(guān)數(shù)據(jù)，則查詢將看到所有表中的舊值或新值，而不是舊值和新值的混合。

• isolation（隔離性）：數(shù)據(jù)庫(kù)允許多個(gè)并發(fā)事務(wù)同時(shí)對(duì)其數(shù)據(jù)進(jìn)行讀寫(xiě)和修改的能力，隔離性可以防止多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)由于交叉執(zhí)行而導(dǎo)致數(shù)據(jù)的不一致。事務(wù)隔離分為不同級(jí)別，包括：
  read uncommitted（讀未提交內(nèi)容）->read committed（讀已提交內(nèi)容）->repeatable read（可重復(fù)讀）->serializable（串行）。
  隔離級(jí)別依次增強(qiáng)，但是導(dǎo)致的問(wèn)題是并發(fā)能力的減弱。

• durability（持久性）：事務(wù)處理結(jié)束后，對(duì)數(shù)據(jù)的修改就是永久的，即便系統(tǒng)故障也不會(huì)丟失。

臟讀、幻讀、不可重復(fù)讀

• 臟讀(dirty read) - 讀取未提交數(shù)據(jù)
  事務(wù)A讀取事務(wù)B尚未提交的數(shù)據(jù)，此時(shí)如果事務(wù)B發(fā)生錯(cuò)誤并執(zhí)行回滾操作，那么事務(wù)A讀取到的數(shù)據(jù)就是臟數(shù)據(jù)。
  臟讀在read uncommitted隔離級(jí)別出現(xiàn)。

• 不可重復(fù)讀（non repeatable read）- 前后多次讀取，數(shù)據(jù)內(nèi)容不一致
  不可重復(fù)讀是指同一個(gè)事務(wù)中對(duì)同一筆數(shù)據(jù)進(jìn)行讀取，每次讀取結(jié)果都不同。比如事務(wù)A在事務(wù)B的更新操作之前讀取一次數(shù)據(jù)，在事務(wù)B更新操作提交后再讀取同一筆數(shù)據(jù)，兩次查詢的結(jié)果不同，即數(shù)據(jù)和之前不一樣了。
  不可重復(fù)讀在read uncommitted，read committed隔離級(jí)別中出現(xiàn)。

• 幻讀（phantom）- 前后多次讀取，數(shù)據(jù)總量不一致
  事務(wù)A在執(zhí)行讀取操作，需要兩次統(tǒng)計(jì)數(shù)據(jù)的總量，前一次查詢數(shù)據(jù)總量后，此時(shí)事務(wù)B執(zhí)行了新增數(shù)據(jù)的操作并提交后，這時(shí)事務(wù)A再次讀取的數(shù)據(jù)總量和之前統(tǒng)計(jì)的不一樣，就像產(chǎn)生了幻覺(jué)一樣，平白無(wú)故的多了幾條數(shù)據(jù)，稱為幻讀。
  幻讀在read uncommitted，read committed和repeatable read隔離級(jí)別中出現(xiàn)。

不可重復(fù)讀和幻讀的區(qū)別：
不可重復(fù)讀是指事務(wù)A前后兩次讀取同一行數(shù)據(jù)的過(guò)程中，因?yàn)槭聞?wù)B對(duì)該行數(shù)據(jù)做了update的提交，導(dǎo)致事務(wù)A前后兩次查詢結(jié)果不一致。
幻讀是指事務(wù)A前后兩次條件相同的查詢過(guò)程中，因?yàn)槭聞?wù)B的insert/delete提交，導(dǎo)致事務(wù)A第2次查詢結(jié)果相對(duì)第1次結(jié)果出現(xiàn)了新行或舊行丟失。
即另一個(gè)事務(wù)的update操作會(huì)導(dǎo)致不可重復(fù)讀，insert/delete會(huì)導(dǎo)致幻讀。

事務(wù)隔離級(jí)別

事務(wù)隔離分以下級(jí)別：

快照（snapshot）

某一時(shí)刻的數(shù)據(jù)，即使后續(xù)其他事務(wù)更新了數(shù)據(jù)，該時(shí)刻的數(shù)據(jù)快照仍保持不變。被特定的隔離級(jí)別使用，以允許一致性讀。

一致性讀（consistent read）

也稱為快照讀。InnoDB確保并發(fā)時(shí)，事務(wù)A不會(huì)讀取由事務(wù)B更新的信息，即使事務(wù)B已提交。原因是一致讀使用基于某個(gè)時(shí)間點(diǎn)的數(shù)據(jù)快照。如果查詢的數(shù)據(jù)已被另一個(gè)事務(wù)更改，則根據(jù)撤消日志（undo log）的內(nèi)容重建原始數(shù)據(jù)。
數(shù)據(jù)快照的時(shí)間選取：

一致性讀是InnoDB在read committed和repeatable read隔離級(jí)別中select語(yǔ)句的默認(rèn)模式。

解析

ok，根據(jù)以上概念，查了下數(shù)據(jù)庫(kù)的事務(wù)隔離級(jí)別，果然是repeatable read：

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

現(xiàn)在回到最初的問(wèn)題：

基于InnoDB默認(rèn)的repeatable read事務(wù)隔離和select為快照讀可知，連接池中的連接在初始化時(shí)，沒(méi)有設(shè)置autocommit為T(mén)rue，會(huì)導(dǎo)致復(fù)用同一個(gè)連接的多次select查詢其實(shí)都在一個(gè)事務(wù)內(nèi)，且都為快照讀，這樣，每個(gè)select查的數(shù)據(jù)源并不是表中的最新數(shù)據(jù)，而是第1個(gè)查詢時(shí)的快照，因此導(dǎo)致查不到表中其他事務(wù)（下單接口的寫(xiě)入操作）寫(xiě)入的最新數(shù)據(jù)。
將autocommit設(shè)置為T(mén)rue后，每個(gè)事務(wù)中只有1個(gè)查詢，下一個(gè)查詢屬于一個(gè)全新的事務(wù)，這樣就能讀到新事務(wù)開(kāi)始前的最新數(shù)據(jù)了。

最后

最開(kāi)始在網(wǎng)上搜a(bǔ)utocommit的那兩個(gè)鏈接提到repeatable read后，我在網(wǎng)上搜了些repeatable read的中文資料（里面并沒(méi)有提到快照，百度的資料果然不靠譜...），就解答了自己為啥每次查詢的結(jié)果好似有緩存一樣的困惑。但是又產(chǎn)生了新的困惑，repeatable read不能避免幻讀，那理論上，我的問(wèn)題應(yīng)該是符合幻讀的。后來(lái)看官方文檔，才發(fā)現(xiàn)repeatable read中提及了快照和一致性讀的概念，才解答了自己的疑惑。
關(guān)于一致性讀背后的原理，又涉及到mvcc的概念，為什么我的問(wèn)題不屬于幻讀，感興趣的同學(xué)可以參考下面2篇文章：
MYSQL MVCC實(shí)現(xiàn)原理
MVCC 能解決幻讀嗎？