迎面走來(lái)了一個(gè)風(fēng)塵仆仆的身穿格子衫的男子，手里拿著一個(gè)MacBook Pro，看著那稀少的發(fā)量，和那從容淡定的眼神。

我心里一顫，我去，這是架構(gòu)師，架構(gòu)師來(lái)面我技術(shù)面，我心里頓時(shí)不淡定了，表面很穩(wěn)實(shí)則心里慌得一批。

果然，他手里拿著我的簡(jiǎn)歷，快速的掃了一下，然后用眼角余光看了一下我，上來(lái)就開(kāi)問(wèn)。

面試官：看你簡(jiǎn)歷上說(shuō)精通Mysql優(yōu)化方法，你先來(lái)說(shuō)說(shuō)你對(duì)Mysql的事務(wù)的了解吧。

我心里喜了一下，這個(gè)簡(jiǎn)單啊，哥我可是北大(背大)的，再來(lái)面試之前，早就有準(zhǔn)備的，二話(huà)不說(shuō)，上去就是背。

我：好的，數(shù)據(jù)庫(kù)的事務(wù)是指一組sql語(yǔ)句組成的數(shù)據(jù)庫(kù)邏輯處理單元，在這組的sql操作中，要么全部執(zhí)行成功，要么全部執(zhí)行失敗。

我：這里的一組sql操作，舉個(gè)簡(jiǎn)單又經(jīng)典的例子就是轉(zhuǎn)賬了，事務(wù)A中要進(jìn)行轉(zhuǎn)賬，那么轉(zhuǎn)出的賬號(hào)要扣錢(qián)，轉(zhuǎn)入的賬號(hào)要加錢(qián)，這兩個(gè)操作都必須同時(shí)執(zhí)行成功，為了確保數(shù)據(jù)的一致性。

面試官：剛才你提到了數(shù)據(jù)一致性，你知道事務(wù)的特性嗎？說(shuō)說(shuō)你的理解。

我：在Mysql中事務(wù)的四大特性主要包含：原子性（Atomicity）、一致性（Consistent）、隔離性（Isalotion）、持久性(Durable)，簡(jiǎn)稱(chēng)為ACID。

我：原子性是指事務(wù)的原子性操作，對(duì)數(shù)據(jù)的修改要么全部執(zhí)行成功，要么全部失敗，實(shí)現(xiàn)事務(wù)的原子性，是基于日志的Redo/Undo機(jī)制。

我：一致性是指執(zhí)行事務(wù)前后的狀態(tài)要一致，可以理解為數(shù)據(jù)一致性。隔離性側(cè)重指事務(wù)之間相互隔離，不受影響，這個(gè)與事務(wù)設(shè)置的隔離級(jí)別有密切的關(guān)系。

我：持久性則是指在一個(gè)事務(wù)提交后，這個(gè)事務(wù)的狀態(tài)會(huì)被持久化到數(shù)據(jù)庫(kù)中，也就是事務(wù)提交，對(duì)數(shù)據(jù)的新增、更新將會(huì)持久化到數(shù)據(jù)庫(kù)中。

我：在我的理解中，原子性、隔離性、持久性都是為了保障一致性而存在的，一致性也是最終的目的。

心里暗自歡喜，背完了，平時(shí)背的多，面試就會(huì)說(shuō)，幸好難不倒我。

面試官：剛才你說(shuō)原子性是基于日志的Redo/Undo機(jī)制，你能說(shuō)一說(shuō)Redo/Undo機(jī)制嗎？

啊哈？我都說(shuō)了什么，不小心給自己埋了一顆大雷。不慌，哥腦子里還有貨，假裝若有所思的停了幾十秒，接著背。

我：Redo/Undo機(jī)制比較簡(jiǎn)單，它們將所有對(duì)數(shù)據(jù)的更新操作都寫(xiě)到日志中。

我：Redo log用來(lái)記錄某數(shù)據(jù)塊被修改后的值，可以用來(lái)恢復(fù)未寫(xiě)入 data file 的已成功事務(wù)更新的數(shù)據(jù)；Undo log是用來(lái)記錄數(shù)據(jù)更新前的值，保證數(shù)據(jù)更新失敗能夠回滾。

我：假如數(shù)據(jù)庫(kù)在執(zhí)行的過(guò)程中，不小心崩了，可以通過(guò)該日志的方式，回滾之前已經(jīng)執(zhí)行成功的操作，實(shí)現(xiàn)事務(wù)的一致性。

面試官：可以舉一個(gè)場(chǎng)景，說(shuō)一下具體的實(shí)現(xiàn)流程嗎？

我：可以的，假如某個(gè)時(shí)刻數(shù)據(jù)庫(kù)崩潰，在崩潰之前有事務(wù)A和事務(wù)B在執(zhí)行，事務(wù)A已經(jīng)提交，而事務(wù)B還未提交。當(dāng)數(shù)據(jù)庫(kù)重啟進(jìn)行 crash-recovery 時(shí)，就會(huì)通過(guò)Redo log將已經(jīng)提交事務(wù)的更改寫(xiě)到數(shù)據(jù)文件，而還沒(méi)有提交的就通過(guò)Undo log進(jìn)行roll back。

面試官：之前你還提到事務(wù)的隔離級(jí)別，你能說(shuō)一說(shuō)嗎？

我：可以的，在Mysql中事務(wù)的隔離級(jí)別分為四大等級(jí)，讀未提交（READ UNCOMMITTED）、讀提交 （READ COMMITTED）、可重復(fù)讀 （REPEATABLE READ）、串行化 （SERIALIZABLE）。

我：讀未提交會(huì)讀到另一個(gè)事務(wù)的未提交的數(shù)據(jù)，產(chǎn)生臟讀問(wèn)題，讀提交則解決了臟讀的，出現(xiàn)了不可重復(fù)讀，即在一個(gè)事務(wù)任意時(shí)刻讀到的數(shù)據(jù)可能不一樣，可能會(huì)受到其它事務(wù)對(duì)數(shù)據(jù)修改提交后的影響，一般是對(duì)于update的操作。

我：可重復(fù)讀解決了之前不可重復(fù)讀和臟讀的問(wèn)題，但是由帶來(lái)了幻讀的問(wèn)題，幻讀一般是針對(duì)inser操作。

我：例如：第一個(gè)事務(wù)查詢(xún)一個(gè)User表id=100發(fā)現(xiàn)不存在該數(shù)據(jù)行，這時(shí)第二個(gè)事務(wù)又進(jìn)來(lái)了，新增了一條id=100的數(shù)據(jù)行并且提交了事務(wù)。

我：這時(shí)第一個(gè)事務(wù)新增一條id=100的數(shù)據(jù)行會(huì)報(bào)主鍵沖突，第一個(gè)事務(wù)再select一下，發(fā)現(xiàn)id=100數(shù)據(jù)行已經(jīng)存在，這就是幻讀。

面試官：小伙子你能演示一下嗎？我不太會(huì)你能教教我嗎？我電腦在這里，你演示我看一看。

男人的嘴騙人的鬼，我信你個(gè)鬼，你這糟老頭子壞得很，出來(lái)裝X總是要還的，只能默默含淚把它敲完。

我：首先創(chuàng)建一個(gè)User表，最為一個(gè)測(cè)試表，測(cè)試表里面有三個(gè)字段，并插入兩條測(cè)試數(shù)據(jù)。

CREATE TABLE User ( id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), age INTDEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=gb2312; INSERT INTO `user` VALUES (1, 'zhangsan', 23); INSERT INTO `user` VALUES (2, 'lisi', 20);

我：在Mysql中可以先查詢(xún)一下他的默認(rèn)隔離級(jí)別，可以看出Mysql的默認(rèn)隔離級(jí)別是REPEATABLE-READ。

我：先來(lái)演示一下讀未提交，先把默認(rèn)的隔離級(jí)別修改為READ UNCOMMITTED。

我：他設(shè)置隔離級(jí)別的語(yǔ)句中set global transaction isolation level read uncommitted，這里的global也可以換成session，global表示全局的，而session表示當(dāng)前會(huì)話(huà)，也就是當(dāng)前窗口有效。

我：當(dāng)設(shè)置完隔離級(jí)別后對(duì)于之前打開(kāi)的會(huì)話(huà)，是無(wú)效的，要重新打開(kāi)一個(gè)窗口設(shè)置隔離級(jí)別才生效。

我：然后是開(kāi)啟事務(wù)，Mysql中開(kāi)啟事務(wù)有兩種方式begin/start transaction，最后提交事務(wù)執(zhí)行commit，或者回滾事務(wù)rollback。

我：在執(zhí)行begin/start transaction命令，它們并不是一個(gè)事務(wù)的起點(diǎn)，在執(zhí)行完它們后的第一個(gè)sql語(yǔ)句，才表示事務(wù)真正的啟動(dòng) 。

我：這里直接打開(kāi)兩個(gè)新的窗口，同時(shí)開(kāi)啟事務(wù)，在第一個(gè)窗口先update一個(gè)id=1的數(shù)據(jù)行name改為'非科班的科班'，執(zhí)行成功。

我：然后再第二個(gè)窗口執(zhí)行兩次的查詢(xún)，分別是窗口一update之前的查詢(xún)和update之后的查詢(xún)。

我：第一個(gè)session產(chǎn)生的未提交的事務(wù)的狀態(tài)就會(huì)直接影響到第二sesison，也就是臟讀。

我：對(duì)于讀提交也是一樣的，開(kāi)啟事務(wù)后，第一個(gè)事務(wù)先執(zhí)行查詢(xún)數(shù)據(jù)，然后第二個(gè)session執(zhí)行update操作，但是還沒(méi)有commit，這是第一個(gè)session再次select，數(shù)據(jù)并沒(méi)有改變，再第二個(gè)session執(zhí)行commit之后，第一個(gè)session再次select就是改變后的數(shù)據(jù)了。

我：這樣第一個(gè)事務(wù)的查詢(xún)結(jié)果就會(huì)收到第二事務(wù)的影響，這個(gè)也就是產(chǎn)生不可重復(fù)讀的問(wèn)題。

面試官：小伙子你能畫(huà)一下他執(zhí)行的過(guò)程圖嗎？你講的我有點(diǎn)亂，我還沒(méi)有徹底明白。

我心里一萬(wàn)只什么馬在飛過(guò)，欲哭無(wú)淚，這面試官真難伺候，說(shuō)時(shí)遲那時(shí)快，從左屁股兜抽出筆，從右屁股兜拿出紙，開(kāi)始畫(huà)。

我：這個(gè)是讀提交的時(shí)間軸圖，讀未提交的時(shí)間軸圖，原理也一樣的，第二個(gè)select的時(shí)候數(shù)據(jù)就已經(jīng)改變了。

這是面試官拿過(guò)我的圖看了一點(diǎn)，微微的點(diǎn)了點(diǎn)頭，嘴角露出思思的笑意，我想你這糟老頭子應(yīng)該不會(huì)再刁難我了吧。

面試官：嗯，你接著演示你的可重復(fù)讀吧。

我：嗯，好的，然后就是可重復(fù)讀，和之前一樣的操作。

我：將兩個(gè)session開(kāi)啟為REPEATABLE READ，同時(shí)開(kāi)啟事務(wù)，在第一個(gè)事務(wù)中先select，然后在第二個(gè)事務(wù)里面update數(shù)據(jù)行，可以發(fā)現(xiàn)即使第二個(gè)事務(wù)已經(jīng)commit，第一個(gè)事務(wù)再次select數(shù)據(jù)也還是沒(méi)有改變，這就解決了不可重復(fù)讀的問(wèn)題。

我：這里有個(gè)不同的地方就是在Mysql中，默認(rèn)的不可重復(fù)讀個(gè)隔離級(jí)別也解決了幻讀的問(wèn)題。

我：從上面的演示中可以看出第一個(gè)事務(wù)中先select一個(gè)id=3的數(shù)據(jù)行，這條數(shù)據(jù)行是不存在的，返回Empty set，然后第二個(gè)事務(wù)中insert一條id=3的數(shù)據(jù)行并且commit，第一個(gè)事務(wù)中再次select的，數(shù)據(jù)也好是沒(méi)有id=3的數(shù)據(jù)行。

我：最后的串行化，樣式步驟也是一樣的，結(jié)果也和Mysql中默認(rèn)的個(gè)可重復(fù)讀隔離級(jí)別的結(jié)果一樣，串行化的執(zhí)行流程相當(dāng)于把事務(wù)的執(zhí)行過(guò)程變?yōu)轫樞驁?zhí)行，我這邊就不再做演示了。

我：這四大等級(jí)從上到下，隔離的效果是逐漸增強(qiáng)，但是性能卻是越來(lái)越差。

面試官：哦？性能越來(lái)越差？為什么會(huì)性能越來(lái)越差？你能說(shuō)一說(shuō)原因嗎？

哎呀，我這嘴，少說(shuō)一句會(huì)死啊，這下好了，這個(gè)得說(shuō)底層實(shí)現(xiàn)原理了，從原來(lái)的假裝若有所思，變成了真正得若有所思。

我：這個(gè)得從Mysq的鎖說(shuō)起，在Mysql中的鎖可以分為分享鎖/讀鎖（Shared Locks）、排他鎖/寫(xiě)鎖（Exclusive Locks）、間隙鎖、行鎖（Record Locks）、表鎖。

我：在四個(gè)隔離級(jí)別中加鎖肯定是要消耗性能的，而讀未提交是沒(méi)有加任何鎖的，所以對(duì)于它來(lái)說(shuō)也就是沒(méi)有隔離的效果，所以它的性能也是最好的。

我：對(duì)于串行化加的是一把大鎖，讀的時(shí)候加共享鎖，不能寫(xiě)，寫(xiě)的時(shí)候，加的是排它鎖，阻塞其它事務(wù)的寫(xiě)入和讀取，若是其它的事務(wù)長(zhǎng)時(shí)間不能寫(xiě)入就會(huì)直接報(bào)超時(shí)，所以它的性能也是最差的，對(duì)于它來(lái)就沒(méi)有什么并發(fā)性可言。

我：對(duì)于讀提交和可重復(fù)讀，他們倆的實(shí)現(xiàn)是兼顧解決數(shù)據(jù)問(wèn)題，然后又要有一定的并發(fā)行，所以在實(shí)現(xiàn)上鎖機(jī)制會(huì)比串行化優(yōu)化很多，提高并發(fā)性，所以性能也會(huì)比較好。

我：他們倆的底層實(shí)現(xiàn)采用的是MVCC（多版本并發(fā)控制）方式進(jìn)行實(shí)現(xiàn)。

面試官：你能先說(shuō)一下先這幾個(gè)鎖的概念嗎？我不是很懂，說(shuō)說(shuō)你的理解。

我：哦，好的，共享鎖是針對(duì)同一份數(shù)據(jù)，多個(gè)讀操作可以同時(shí)進(jìn)行，簡(jiǎn)單來(lái)說(shuō)即讀加鎖，不能寫(xiě)并且可并行讀；排他鎖針對(duì)寫(xiě)操作，假如當(dāng)前寫(xiě)操作沒(méi)有完成，那么它會(huì)阻斷其它的寫(xiě)鎖和讀鎖，即寫(xiě)加鎖，其它讀寫(xiě)都阻塞 。

我：而行鎖和表鎖，是從鎖的粒度上進(jìn)行劃分的，行鎖鎖定當(dāng)前數(shù)據(jù)行，鎖的粒度小，加鎖慢，發(fā)生鎖沖突的概率小，并發(fā)度高，行鎖也是MyISAM和InnoDB的區(qū)別之一，InnoDB支持行鎖并且支持事務(wù) 。

我：而表鎖則鎖的粒度大，加鎖快，開(kāi)銷(xiāo)小，但是鎖沖突的概率大，并發(fā)度低。

我：間隙鎖則分為兩種：Gap Locks和Next-Key Locks。Gap Locks會(huì)鎖住兩個(gè)索引之間的區(qū)間，比如select * from User where id>3 and id<5 for update，就會(huì)在區(qū)間（3，5）之間加上Gap Locks。

我：Next-Key Locks是Gap Locks+Record Locks形成閉區(qū)間鎖select * from User where id>=3 and id=<5 for update，就會(huì)在區(qū)間[3,5]之間加上Next-Key Locks。

面試官：那Mysql中什么時(shí)候會(huì)加鎖呢？

我：在數(shù)據(jù)庫(kù)的增、刪、改、查中，只有增、刪、改才會(huì)加上排它鎖，而只是查詢(xún)并不會(huì)加鎖，只能通過(guò)在select語(yǔ)句后顯式加lock in share mode或者for update來(lái)加共享鎖或者排它鎖。

面試官：你在上面提到MVCC（多版本并發(fā)控制），你能說(shuō)一說(shuō)原理嗎？

我：在實(shí)現(xiàn)MVCC時(shí)用到了一致性視圖，用于支持讀提交和可重復(fù)讀的實(shí)現(xiàn)。

我：在實(shí)現(xiàn)可重復(fù)讀的隔離級(jí)別，只需要在事務(wù)開(kāi)始的時(shí)候創(chuàng)建一致性視圖，也叫做快照，之后的查詢(xún)里都共用這個(gè)一致性視圖，后續(xù)的事務(wù)對(duì)數(shù)據(jù)的更改是對(duì)當(dāng)前事務(wù)是不可見(jiàn)的，這樣就實(shí)現(xiàn)了可重復(fù)讀。

我：而讀提交，每一個(gè)語(yǔ)句執(zhí)行前都會(huì)重新計(jì)算出一個(gè)新的視圖，這個(gè)也是可重復(fù)讀和讀提交在MVCC實(shí)現(xiàn)層面上的區(qū)別。

面試官：那你知道快照（視圖）在MVCC底層是怎么工作的嗎？

我：在InnoDB 中每一個(gè)事務(wù)都有一個(gè)自己的事務(wù)id，并且是唯一的，遞增的 。

我：對(duì)于Mysql中的每一個(gè)數(shù)據(jù)行都有可能存在多個(gè)版本，在每次事務(wù)更新數(shù)據(jù)的時(shí)候，都會(huì)生成一個(gè)新的數(shù)據(jù)版本，并且把自己的數(shù)據(jù)id賦值給當(dāng)前版本的row trx_id。

面試官：小伙子你可以畫(huà)個(gè)圖我看看嗎？我不是很明白。

我有什么辦法呢？完全沒(méi)辦法，只能又從屁股兜里拿出筆和紙，迅速的畫(huà)了起來(lái)，要是這次面試不過(guò)就血虧啊，浪費(fèi)了我兩張紙和筆水，現(xiàn)在的筆和紙多貴啊，只能豁出去了。

我：如圖中所示，假如三個(gè)事務(wù)更新了同一行數(shù)據(jù)，那么就會(huì)有對(duì)應(yīng)的三個(gè)數(shù)據(jù)版本。

我：實(shí)際上版本1、版本2并非實(shí)際物理存在的，而圖中的U1和U2實(shí)際就是undo log，這v1和v2版本是根據(jù)當(dāng)前v3和undo log計(jì)算出來(lái)的。

面試官：那對(duì)于一個(gè)快照來(lái)說(shuō)，你知道它要遵循什么規(guī)則嗎？

我：嗯，對(duì)于一個(gè)事務(wù)視圖來(lái)說(shuō)除了對(duì)自己更新的總是可見(jiàn)，另外還有三種情況：版本未提交的，都是不可見(jiàn)的；版本已經(jīng)提交，但是是在創(chuàng)建視圖之后提交的也是不可見(jiàn)的；版本已經(jīng)提交，若是在創(chuàng)建視圖之前提交的是可見(jiàn)的。

面試官：假如兩個(gè)事務(wù)執(zhí)行寫(xiě)操作，又怎么保證并發(fā)呢？

我：假如事務(wù)1和事務(wù)2都要執(zhí)行update操作，事務(wù)1先update數(shù)據(jù)行的時(shí)候，先回獲取行鎖，鎖定數(shù)據(jù)，當(dāng)事務(wù)2要進(jìn)行update操作的時(shí)候，也會(huì)取獲取該數(shù)據(jù)行的行鎖，但是已經(jīng)被事務(wù)1占有，事務(wù)2只能wait。

我：若是事務(wù)1長(zhǎng)時(shí)間沒(méi)有釋放鎖，事務(wù)2就會(huì)出現(xiàn)超時(shí)異常 。

面試官：這個(gè)是在update的where后的條件是在有索引的情況下吧？

我：嗯，是的 。

面試官：那沒(méi)有索引的條件下呢？沒(méi)辦法快速定位到數(shù)據(jù)行呢？

我：若是沒(méi)有索引的條件下，就獲取所有行，都加上行鎖，然后Mysql會(huì)再次過(guò)濾符合條件的的行并釋放鎖，只有符合條件的行才會(huì)繼續(xù)持有鎖。

我：這樣的性能消耗也會(huì)比較大。

面試官：嗯嗯

此時(shí)面試官看看手表一個(gè)多鐘已經(jīng)過(guò)去了，也已經(jīng)到了飯點(diǎn)時(shí)刻，我想他應(yīng)該是肚子餓了，不會(huì)繼續(xù)追問(wèn)吧，兩人持續(xù)僵了三十秒，他終于開(kāi)口了。

面試官：小伙子，現(xiàn)在時(shí)間也已經(jīng)到了飯點(diǎn)了，今天的面試就到此結(jié)束吧，你回去等通知吧。

我：。。。。。。。。。。

責(zé)任編輯：xj

原文標(biāo)題：我以為我對(duì) MySQL 事務(wù)很熟，直到我遇到了阿里面試官

文章出處：【微信公眾號(hào)：數(shù)據(jù)分析與開(kāi)發(fā)】歡迎添加關(guān)注！文章轉(zhuǎn)載請(qǐng)注明出處。