關(guān)于MySQL的酸與MVCC和面試官小戰(zhàn)三十回合

我，小Y。

此刻，正坐在辦公室里等待面試，心情xue微有點(diǎn)忐忑，不知道待會(huì)兒老面試官經(jīng)不經(jīng)得住我的折磨。

只見一抹光亮閃過(guò)，面試官推門而入，我抬頭望去，強(qiáng)者的氣息鋪面而來(lái)，沒(méi)錯(cuò)是那味兒。

看到面試官頭上那“傲然矗立”的頭發(fā)，腦海中止不住幻想他在無(wú)數(shù)個(gè)凌晨于電腦前挑燈夜碼的高大形象，一種敬佩感油然而生， 竟忍不住站起來(lái)給他敬了個(gè)禮。

面試官：有??？

我：沒(méi)沒(méi)沒(méi)，我謝頂反應(yīng)綜合征犯了，面試官好，我是小 Y ，請(qǐng)多多指教。

面試官：哦哦，確實(shí)是有病啊，沒(méi)事，記得吃藥就行。我看你簡(jiǎn)歷寫你 MySQL 挺懂的，那我先問(wèn)問(wèn)你 MySQL 吧。

我：好嘞，您請(qǐng)。

面試官：你知道什么是 MySQL 的酸嗎？

這一來(lái)就這么猛的嗎？腦海中一頓搜索，只能想起張含韻的我喜歡酸的甜這就是真的我之《酸酸甜甜就是我》，算了蒙一個(gè)。

我：事務(wù)?

面試官：?jiǎn)?，最近好多諧音梗，我特意玩了個(gè)英語(yǔ)單詞短語(yǔ)梗，腦子轉(zhuǎn)的挺快啊小伙子。

酸，英文 acid，說(shuō)的就是事務(wù)！這都蒙對(duì)了，等下就去買彩票！趁這個(gè)機(jī)會(huì)再表現(xiàn)一下！

我：是啊，國(guó)外的人就有拼湊單詞的習(xí)慣，其實(shí)事務(wù)主要是為了實(shí)現(xiàn) C ，也就是一致性，具體是通過(guò)AID，即原子性、隔離性和持久性來(lái)達(dá)到一致性的目的，所以這四個(gè)不應(yīng)該相提并論，但是他們就想拼成單詞，就把它們排好序搞在一起來(lái)念。

嘿嘿，這個(gè)B裝的我有點(diǎn)舒服，果然面試官有點(diǎn)驚訝。

面試官：可以呀，那你知道 MVCC 吧？

我：知道，Multi-Version  Concurrency Control （多版本并發(fā)控制）。

面試官：能先簡(jiǎn)短的解釋下什么是 MVCC 嗎？

我：多版本并發(fā)控制，其實(shí)指的是一條記錄會(huì)有多個(gè)版本，每次修改記錄都會(huì)存儲(chǔ)這條記錄被修改之前的版本，多版本之間串聯(lián)起來(lái)就形成了一條版本鏈。

這樣不同時(shí)刻啟動(dòng)的事務(wù)可以無(wú)鎖地獲得不同版本的數(shù)據(jù)(普通讀)。此時(shí)讀(普通讀)寫操作不會(huì)阻塞，寫操作可以繼續(xù)寫，無(wú)非就是多加了一個(gè)版本，歷史版本記錄可供已經(jīng)啟動(dòng)的事務(wù)讀取。

（為保持簡(jiǎn)短，簡(jiǎn)化了SQL語(yǔ)句，下文也同樣簡(jiǎn)化）

面試官：那你知道事務(wù)四種隔離級(jí)別吧？

我：讀未提交、讀已提交、可重復(fù)讀、可串行化。

面試官：MVCC 用來(lái)實(shí)現(xiàn)哪幾個(gè)隔離級(jí)別？

我：用來(lái)實(shí)現(xiàn)讀已提交和可重復(fù)讀。首先隔離級(jí)別如果是讀未提交的話，直接讀最新版本的數(shù)據(jù)就行了，壓根就不需要保存以前的版本。可串行化隔離級(jí)別事務(wù)都串行執(zhí)行了，所以也不需要多版本，因此 MVCC 是用來(lái)實(shí)現(xiàn)讀已提交和可重復(fù)讀的。

面試官：那為什么需要 MVCC ？如果沒(méi)有 MVCC 會(huì)怎樣？

我：如果沒(méi)有 MVCC 讀寫操作之間就會(huì)沖突。想象一下有一個(gè)事務(wù)1正在執(zhí)行，此時(shí)一個(gè)事務(wù)2修改了記錄A，還未提交，此時(shí)事務(wù)1要讀取記錄A，因?yàn)槭聞?wù)2還未提交，所以事務(wù)1無(wú)法讀取最新的記錄A，不然就是發(fā)生臟讀的情況，所以應(yīng)該讀記錄A被事務(wù)2修改之前的數(shù)據(jù)，但是記錄A已經(jīng)被事務(wù)2改了呀，所以事務(wù)1咋辦？只能用鎖阻塞等待事務(wù)2的提交，這種實(shí)現(xiàn)叫 LBCC(Lock-Based Concurrent Control)。

如果有多版本的話，就不一樣了。事務(wù)2修改的記錄 A，還未提交，但是記錄 A 被修改之前的版本還在，此時(shí)事務(wù)1就可以讀取之前的版本數(shù)據(jù)，這樣讀寫之間就不會(huì)阻塞啦，所以說(shuō) MVCC 提高了事務(wù)的并發(fā)度，提升數(shù)據(jù)庫(kù)的性能。

面試官：你對(duì)這個(gè)多版本有沒(méi)有什么別的理解？

我：（面試官要開始操作我了嗎？不過(guò)就這，我早有準(zhǔn)備！）有點(diǎn)個(gè)人的小理解(假裝謙虛)。其實(shí)這個(gè)多版本不是很準(zhǔn)確，只是為了便于理解或者說(shuō)展現(xiàn)出來(lái)像多版本的樣子而已。

實(shí)際上 InnoDB 不會(huì)真的存儲(chǔ)了多個(gè)版本的數(shù)據(jù)，只是借助 undolog 記錄每次寫操作的反向操作，所以索引上對(duì)應(yīng)的記錄只會(huì)有一個(gè)版本，即最新版本。只不過(guò)可以根據(jù) undolog 中的記錄反向操作得到數(shù)據(jù)的歷史版本，所以看起來(lái)是多個(gè)版本。

面試官：那你能詳細(xì)的說(shuō)下 MVCC 是如何實(shí)現(xiàn)的嗎？

我：您聽好啦。

拿上面的insert （1，XX）這條語(yǔ)句舉例，成功插入之后數(shù)據(jù)頁(yè)的記錄上不僅存儲(chǔ) ID 1，name XX，還有 trx_id 和 roll_pointer 這兩個(gè)隱藏字段：

• trx_id：當(dāng)前事務(wù)ID。
• roll_pointer：指向 undo log 的指針。

從圖中可以得知此時(shí)插入的事務(wù)ID是1，此時(shí)插入會(huì)生成一條 undolog ，并且記錄上的 roll_pointer 會(huì)指向這條 undolog ，而這條 undolog  是一個(gè)類型為TRX_UNDO_INSERT_REC的 log，代表是 insert 生成的，里面存儲(chǔ)了主鍵的長(zhǎng)度和值(還有其他值，不提)。

所以 InnoDB 可以根據(jù) undolog  里的主鍵的值，找到這條記錄，然后把它刪除來(lái)實(shí)現(xiàn)回滾(復(fù)原)的效果。因此可以簡(jiǎn)單地理解 undolog 里面存儲(chǔ)的就是當(dāng)前操作的反向操作，所以認(rèn)為里面存了個(gè)delete 1 就行。

此時(shí)事務(wù)1提交，然后另一個(gè) ID 為 5 的事務(wù)再執(zhí)行 update NO where id 1 這個(gè)語(yǔ)句，此時(shí)的記錄和 undolog 就如下圖所示：

沒(méi)錯(cuò)，之前 insert 產(chǎn)生的 undolog 沒(méi)了，insert 的事務(wù)提交了之后對(duì)應(yīng)的 undolog 就回收了，因?yàn)椴豢赡苡袆e的事務(wù)會(huì)訪問(wèn)比這還要早的版本了，訪問(wèn)插入之前的版本？訪問(wèn)個(gè)寂寞嗎？

而 update 產(chǎn)生的 undolog 不一樣，它的類型為 TRX_UNDO_UPD_EXIST_REC。

此時(shí)事務(wù) 5 提交，然后另一個(gè) ID 為 11 的事務(wù)執(zhí)行update Yes where id 1 這個(gè)語(yǔ)句，此時(shí)的記錄和 undolog 就如下圖所示：

沒(méi)錯(cuò)，update 產(chǎn)生的 undolog 不會(huì)馬上刪除，因?yàn)榭赡苡袆e的事務(wù)需要訪問(wèn)之前的版本，所以不能刪。這樣就串成了一個(gè)版本鏈，可以看到記錄本身加上兩條 undolog，這條 id 為 1 的記錄共有三個(gè)版本。

版本鏈搞清楚了，這時(shí)候還需要知道一個(gè)概念 readView，這個(gè) readView 就是用來(lái)判斷哪個(gè)版本對(duì)當(dāng)前事務(wù)可見的，這里有四個(gè)概念：

• creator_trx_id，當(dāng)前事務(wù)ID。
• m_ids，生成 readView 時(shí)還活躍的事務(wù)ID集合，也就是已經(jīng)啟動(dòng)但是還未提交的事務(wù)ID列表。
• min_trx_id，當(dāng)前活躍ID之中的最小值。
• max_trx_id，生成 readView 時(shí) InnoDB 將分配給下一個(gè)事務(wù)的 ID 的值（事務(wù) ID 是遞增分配的，越后面申請(qǐng)的事務(wù)ID越大）

對(duì)于可見版本的判斷是從最新版本開始沿著版本鏈逐漸尋找老的版本，如果遇到符合條件的版本就返回。

判斷條件如下：

• 如果當(dāng)前數(shù)據(jù)版本的 trx_id ==  creator_trx_id 說(shuō)明修改這條數(shù)據(jù)的事務(wù)就是當(dāng)前事務(wù)，所以可見。
• 如果當(dāng)前數(shù)據(jù)版本的 trx_id < min_trx_id，說(shuō)明修改這條數(shù)據(jù)的事務(wù)在當(dāng)前事務(wù)生成 readView 的時(shí)候已提交，所以可見。
• 如果當(dāng)前數(shù)據(jù)版本的 trx_id 在 m_ids 中，說(shuō)明修改這條數(shù)據(jù)的事務(wù)此時(shí)還未提交，所以不可見。
• 如果當(dāng)前數(shù)據(jù)版本的 trx_id >= max_trx_id，說(shuō)明修改這條數(shù)據(jù)的事務(wù)在當(dāng)前事務(wù)生成 readView 的時(shí)候還未啟動(dòng)，所以不可見(結(jié)合事務(wù)ID遞增來(lái)看)。

來(lái)看一個(gè)簡(jiǎn)單的案例，練一練上面的規(guī)則。

讀已提交隔離級(jí)別下的MVCC

現(xiàn)在的隔離級(jí)別是讀已提交。

假設(shè)此時(shí)上文的事務(wù)1已經(jīng)提交，事務(wù) 5 已經(jīng)執(zhí)行，但還未提交，此時(shí)有另一個(gè)事務(wù)在執(zhí)行update YY where id 2，也未提交，它的事務(wù) ID 為 6，且也是現(xiàn)在最大的事務(wù) ID。

現(xiàn)在有一個(gè)查詢開啟了事務(wù)，語(yǔ)句為select name where id 1，那么這個(gè)查詢語(yǔ)句：

• 此時(shí) creator_trx_id 為 0，因?yàn)橐粋€(gè)事務(wù)只有當(dāng)有修改操作的時(shí)候才會(huì)被分配事務(wù) ID。
• 此時(shí) m_ids 為 [5，6]，這兩個(gè)事務(wù)都未提交，為活躍的。
• 此時(shí) min_trx_id，為 5。
• 此時(shí) max_trx_id，為 7，因?yàn)樽钚路峙涞氖聞?wù) ID 為 6，那么下一個(gè)就是7，事務(wù) ID 是遞增分配的。

由于查詢的是 ID 為 1 的記錄，所以先找到 ID 為 1 的這條記錄，此時(shí)的版本如下：

此時(shí)最新版本的記錄上 trx_id 為 5，不比 min_trx_id 小，在 m_ids 之中，表明還是活躍的，未提交，所以不可訪問(wèn)，根據(jù) roll_pointer 找到上一個(gè)版本。

于是找到了圖上的那條 undolog，這條log上面記錄的 trx_id 為 1，比 min_trx_id 還小，說(shuō)明在生成 readView 的時(shí)候已經(jīng)提交，所以可以訪問(wèn)，因此返回結(jié)果 name 為 XX。

然后事務(wù) 5 提交。

此時(shí)再次查詢 select name where id 1，這時(shí)候又會(huì)生成新的 readView。

• 此時(shí) creator_trx_id 為 0，因?yàn)檫€是沒(méi)有修改操作。
• 此時(shí) m_ids 為 [6]，因?yàn)槭聞?wù)5提交了。
• 此時(shí) min_trx_id，為 6。
• 此時(shí) max_trx_id，為 7，此時(shí)沒(méi)有新的事務(wù)申請(qǐng)。

同樣還是查詢的是 ID 為 1 的記錄，所以還是先找到 ID 為 1 的這條記錄，此時(shí)的版本如下(和上面一樣，沒(méi)變)：

此時(shí)最新版本的記錄上 trx_id 為 5，比 min_trx_id 小，說(shuō)明事務(wù)已經(jīng)提交了，是可以訪問(wèn)的，因此返回結(jié)果 name 為 NO。

這就是讀已提交的 MVCC 操作，可以看到一個(gè)事務(wù)中的兩次查詢得到了不同的結(jié)果，所以也叫不可重復(fù)讀。

可重復(fù)讀隔離級(jí)別下的MVCC

現(xiàn)在的隔離級(jí)別是可重復(fù)讀。

可重復(fù)讀和讀已提交的 MVCC 判斷版本的過(guò)程是一模一樣的，唯一的差別在生成 readView 上。

上面的讀已提交每次查詢都會(huì)重新生成一個(gè)新的 readView ，而可重復(fù)讀在第一次生成  readView 之后的所有查詢都共用同一個(gè) readView 。

也就是說(shuō)可重復(fù)讀只會(huì)在第一次 select 時(shí)候生成一個(gè) readView ，所以一個(gè)事務(wù)里面不論有幾次 select ，其實(shí)看到的都是同一個(gè) readView 。

套用上面的情況，差別就在第二次執(zhí)行select name where id 1，不會(huì)生成新的 readView，而是用之前的 readView，所以第二次查詢時(shí)：

• m_ids 還是為 [5，6]，雖說(shuō)事務(wù) 5 此時(shí)已經(jīng)提交了，但是這個(gè)readView是在事務(wù)5提交之前生成的，所以當(dāng)前還是認(rèn)為這兩個(gè)事務(wù)都未提交，為活躍的。
• 此時(shí) min_trx_id，為 5。

(對(duì)于判斷過(guò)程有點(diǎn)卡頓的同學(xué)可以再拉上去看看，判斷版本的過(guò)程和讀已提交一致)。

所以在可重復(fù)級(jí)別下，兩次查詢得到的 name 都為 XX，所以叫可重復(fù)讀。

說(shuō)完之后，我對(duì)面試官挑了挑眉。

面試官瞥了我一眼：可以，那按你這么說(shuō)其實(shí) undolog 算是熱點(diǎn)資源，多個(gè)事務(wù)不就會(huì)爭(zhēng)搶 undolog 了嗎？

我：對(duì)呀，所以為了提高 undolog 的寫入性能，每個(gè)事務(wù)都有屬于自己的 undolog 頁(yè)面鏈表，這樣就提高了寫入并發(fā)度啦，再細(xì)一點(diǎn)就是 insert 類型的 undolog 和 update 類型的 undolog 屬于不同的鏈表。

面試官：還能細(xì)嗎？

我：再細(xì)一點(diǎn)就是普通表和臨時(shí)表各有一條 insert 類型的 undolog 和 update 類型的 undolog ，所以最多一個(gè)事務(wù)可以有四條 undolog 頁(yè)面鏈表。

之所以分普通表和臨時(shí)表是因?yàn)槠胀ū淼?undolog 寫入是需要記錄到redolog 中的需要保證崩潰恢復(fù)，而臨時(shí)表則不需要記錄，反正就是臨時(shí)的。

面試官：對(duì)了，你上面說(shuō) insert 和 update ，那 delete 呢？

我：delete 其實(shí)是屬于 update 的，不過(guò)分了好幾種情況，反正 delete 只會(huì)給記錄上打個(gè)標(biāo)記，表明這條記錄被刪除了，不會(huì)馬上刪除這條記錄，因?yàn)橛涗涍€得存著給別的事務(wù)作為版本鏈訪問(wèn)呢。

面試官：那這條被刪除的記錄就永遠(yuǎn)存在了？

我：不會(huì)的，后臺(tái)有一個(gè) purge 線程，如果探測(cè)出當(dāng)前沒(méi)有事務(wù)會(huì)訪問(wèn)這個(gè)記錄了，就會(huì)把它真正的刪除。

面試官：你這么細(xì)，應(yīng)該沒(méi)有女朋友的吧？

我：（？？？，不對(duì)，面試官應(yīng)該沒(méi)有人身攻擊我，只是說(shuō)我天天刻苦學(xué)習(xí)，沒(méi)時(shí)間找女朋友，但是我還是有點(diǎn)不爽）沒(méi)呢，面試官您頭發(fā)這么多，應(yīng)該也還沒(méi)找到吧？

“我在仰望，月亮之上....”，此時(shí)面試官手機(jī)響起。

面試官：“喂，親愛的，來(lái)了來(lái)了，馬上下班了，待會(huì)老地方見哈?！蹦巧段矣悬c(diǎn)事，你先回去吧。

我：（？？？小丑竟是我自己)好嘞好嘞。

面試官：對(duì)了，這一面還沒(méi)結(jié)束，undolog看你挺熟的，下次詳細(xì)問(wèn)你，還有 MySQL 鎖啊我都還沒(méi)問(wèn)，等通知下次再來(lái)吧。

我：（還給我布置家庭作業(yè)呢？）我一定回去好好準(zhǔn)備準(zhǔn)備，等待您的寵幸。

這老面試官可以，竟然沒(méi)折磨到他，等著，下次 undolog 和  MySQL 鎖我一定好好招待他！