SQL事物機(jī)制特性

1. 事務(wù)

事務(wù)是指一個(gè)或者多個(gè)數(shù)據(jù)庫(kù)操作，要么全部沒有執(zhí)行，要么全部成功執(zhí)行。

中途失敗需要回滾到指定狀態(tài)，全部執(zhí)行成功需要確保持久保存在數(shù)據(jù)庫(kù)中。

事務(wù)擁有四個(gè)特性，習(xí)慣上被稱之為ACID特性。

2. ACID特性

為了更直觀的解釋ACID特性，下面先說明A, B, C之間互相轉(zhuǎn)賬的過程。

假設(shè)A有10元，B有15元，C有8元

A給B轉(zhuǎn)賬5元，操作記為T1。

T1: read(A), A=A-5, write(A), read(B), B=B+5, write(B)。

T1操作的大體流程，先讀取A到賬戶余額，將A的賬戶余額扣減5元后再寫入數(shù)據(jù)庫(kù)中，

讀取B的賬戶余額，將B的賬戶余額增加5元后再寫入到數(shù)據(jù)庫(kù)中。

同時(shí)，C給B轉(zhuǎn)賬4元，操作記為T2。

T2: read(C), C=C-4, write(C), read(B), B=B+4, write(B)

T1操作的大體流程，先讀取C的賬戶余額，將C的賬戶余額扣減4元后再寫入數(shù)據(jù)庫(kù)中，

讀取B的賬戶余額，將B的賬戶余額增加4元后再寫入到數(shù)據(jù)庫(kù)中。

2.1 原子性(Atomicity)

事務(wù)作為一個(gè)整體被執(zhí)行，包含在其中的對(duì)數(shù)據(jù)庫(kù)的操作要么全部被執(zhí)行，要么都不執(zhí)行，是一個(gè)最小執(zhí)行單元，不可分割。

A給B轉(zhuǎn)賬5元的操作T1是包含多個(gè)讀寫操作，這些操作要么全部執(zhí)行，要么全部不執(zhí)行。

假設(shè)由于斷電等意外事件，導(dǎo)致T1只執(zhí)行了部分操作，如T1:read(A), A=A-5, write(A)

這就會(huì)導(dǎo)致A憑空少了5元，并且B沒有收到A轉(zhuǎn)的5元，

因此事務(wù)需要保證保證在事務(wù)執(zhí)行過程中出現(xiàn)錯(cuò)誤時(shí)，將已經(jīng)執(zhí)行的操作“撤銷”，恢復(fù)到原始狀態(tài)。

2.2 一致性(Consistency)

事務(wù)應(yīng)確保數(shù)據(jù)庫(kù)的狀態(tài)從一個(gè)一致狀態(tài)轉(zhuǎn)變?yōu)榱硪粋€(gè)一致狀態(tài)。

假設(shè)A有10元，B有15元，C有8元，不管A, B, C之間如何進(jìn)行轉(zhuǎn)賬(沒有其他人參與)，三個(gè)點(diǎn)賬戶總余額一定是33元，而不會(huì)是其它值。

2.3 隔離性(Isolation)

多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)，一個(gè)事務(wù)的執(zhí)行不應(yīng)影響其他事務(wù)的執(zhí)行。

A給B轉(zhuǎn)賬5元，同時(shí)C給B轉(zhuǎn)賬4元，這個(gè)兩個(gè)事務(wù)應(yīng)該是互相隔離的，互不影響。

最終A余額為5元，C余額為4元，B總共收到兩次轉(zhuǎn)賬，余額應(yīng)該為24元。

假設(shè)T1, T2可以交叉執(zhí)行，如下圖所示。最終結(jié)果看起來B只收到了A的5元轉(zhuǎn)賬，余額為20元。

2.4 持久性(Durability)

已被提交的事務(wù)對(duì)數(shù)據(jù)庫(kù)的修改應(yīng)該永久保存在數(shù)據(jù)庫(kù)中。

MySQL操作一般是先寫入緩存，滿足指定條件后才將緩存更新到磁盤

磁盤寫操作相當(dāng)耗時(shí)，而且同一個(gè)事務(wù)可能修改多個(gè)數(shù)據(jù)頁(yè)面，而且可能執(zhí)行頁(yè)面中一個(gè)字節(jié)的數(shù)據(jù)。

因此每次數(shù)據(jù)庫(kù)提交執(zhí)行緩存刷新磁盤操作不太合理，

MySQL設(shè)計(jì)人員通過redo日志來持久化最小量的數(shù)據(jù)來達(dá)到相同的效果。

3. redo日志

為了保證事務(wù)的持久性，在事務(wù)提交動(dòng)作完成之前，需要把該事務(wù)修改所有頁(yè)面都刷新到磁盤，但是存在以下問題

隨機(jī)I/O比較慢，一個(gè)事務(wù)可能修改到多個(gè)頁(yè)面，這些頁(yè)面在磁盤中可能不相鄰，可能需要多次長(zhǎng)距離移動(dòng)磁盤讀寫磁頭。

刷新完整的數(shù)據(jù)頁(yè)面較慢，一個(gè)事務(wù)也可能值修改頁(yè)面中一個(gè)字節(jié)，卻要同步整個(gè)頁(yè)面到磁盤上。

為了解決上述到問題，InnoDB設(shè)計(jì)了redo日志，把事務(wù)修改的內(nèi)容采用特定的格式按順序保存到磁盤上，

即使在系統(tǒng)崩潰之后，按照redo日志重新更改數(shù)據(jù)頁(yè)進(jìn)行數(shù)據(jù)恢復(fù)即可。

3.1 redo日志格式

redo日志通用格式如下圖所示

type，redo日志類型，通過定義不同類型可以達(dá)到節(jié)省空間的目的

space id，表空間id

page number，頁(yè)號(hào)

data，redo日志具體內(nèi)容

往表中插入一條記錄，可能產(chǎn)生多條redo日志，因?yàn)榭赡墚a(chǎn)生聚簇索引對(duì)應(yīng)B+樹頁(yè)面的分裂操作，

可能需要性申請(qǐng)數(shù)據(jù)頁(yè)，金額可能要修改各種段、區(qū)的統(tǒng)計(jì)信息等，

最終插入一條記錄可能產(chǎn)生多條redo日志，這些日志是不可分割的，

在崩潰恢復(fù)時(shí)，也是將這一組日志作為不可分割的整體來處理，

類似的，將一組不可分割的redo日志稱為Mini-Transaction，即MTR

3.2 redo日志緩沖區(qū)

為了避免頻繁的磁盤IO，并不是每生成一條redo日志就同步到磁盤上。

而是先將redo日志放到緩沖區(qū)，在特定時(shí)機(jī)刷新到磁盤。

redo日志緩沖區(qū)頁(yè)面結(jié)構(gòu)如下圖所示。

redo日志是以MTR為單位寫入到redo日志緩沖區(qū)的，redo日志緩沖區(qū)是有若干個(gè)512B大小的block構(gòu)成的一片連續(xù)的內(nèi)存空間，

InnoDB引擎使用lsn(log sequence number)來記錄系統(tǒng)當(dāng)前有多少redo日志寫入到緩沖區(qū)

3.3 redo日志文件

3.3.1 flush鏈表中的lsn

InnoDB會(huì)將lsn相關(guān)信息寫入到flush鏈表中，進(jìn)而方便判斷哪些redo日志文件可以被重復(fù)使用，

因?yàn)橹灰K頁(yè)被刷新到磁盤，相應(yīng)的redo日志內(nèi)容就沒有存在的意義了，而且redo日志文件大小也有限。

Buffer Pool中頁(yè)面會(huì)在控制塊中記錄頁(yè)面的修改信息

oldest_modification: 第一次修改Buffer Pool中某個(gè)頁(yè)面時(shí)，將MTR開始時(shí)的lsn寫入該變量

newest_modification: 每次修改Buffer Pool中某個(gè)頁(yè)面時(shí)，將MTR結(jié)束時(shí)的lsn寫入該變量

flush鏈表與oldest_modification(o_m)和newest_modification(n_m)的關(guān)系如下圖所示。

flush鏈表的基節(jié)點(diǎn)start指針出發(fā)，flush鏈表的臟頁(yè)時(shí)按照第一次修改發(fā)生的時(shí)間倒序排列的，也就是按照oldest_modification代表的lsn值倒敘排列，

當(dāng)頁(yè)面被多次更新時(shí)，會(huì)更新對(duì)應(yīng)頁(yè)面的newest_modification變量的值。

當(dāng)頁(yè)面1被屬性到磁盤，從頁(yè)面2的控制塊可以看出，lsn低于8916的redo日志可以被覆蓋，系統(tǒng)會(huì)將8916賦值到redo文件的checkpoint_lsn的操作。

InnoDB將檢查flush鏈表最小的oldest_modification的lsn值稱為checkpoint操作。

3.3.2 redo日志文件格式

磁盤上存在多個(gè)redo日志文件，會(huì)被循環(huán)使用，這一組redo日志文件稱為redo日志文件組。

和redo日志緩沖區(qū)一樣，redo日志文件也是由若干個(gè)512B構(gòu)成的block組成

其中，redo日志文件的頭2048個(gè)字節(jié)用于存儲(chǔ)一些管理信息，系統(tǒng)會(huì)將checkpoint操作得到的checkpoint_lsn賦值到checkpoint1的checkpoint_lsn上。

崩潰恢復(fù)會(huì)從checkpoint_lsn在日志文件組中對(duì)應(yīng)的偏移量開始。

除了前面闡述的checkpoint，redo日志刷盤時(shí)機(jī)還包括

redo日志緩存不足時(shí)

事務(wù)提交時(shí)

后臺(tái)線程周期性刷盤

正常關(guān)閉服務(wù)器

3.3.3 奔潰恢復(fù)

當(dāng)遇到異常情況導(dǎo)致服務(wù)器掛掉，在重啟時(shí)可以根據(jù)redo日志文件恢復(fù)到奔潰前的狀態(tài)。

InnoDB從redo日志文件組的第一個(gè)文件的checkpoint信息，然后從checkpoint_lsn在日志文件組中對(duì)應(yīng)的偏移量開始，

一直掃描日志文件中的，直到某個(gè)block的寫入量的值不等于512，根據(jù)redo日志格式將修改的內(nèi)容恢復(fù)到奔潰前狀態(tài)。

4. undo日志

在事務(wù)執(zhí)行過程中可能遇到各種錯(cuò)誤，導(dǎo)致中途就結(jié)束事務(wù)了，但是在遇到錯(cuò)誤退出前，可能修改多個(gè)行記錄，

但是為了保證事務(wù)的原子性，需要將數(shù)據(jù)恢復(fù)到事務(wù)開啟前，這個(gè)恢復(fù)過程就稱為回滾。

為了回滾，就需要將事務(wù)修改的內(nèi)容記錄下來，包括插入的行記錄、修改行記錄的內(nèi)容、刪除的行記錄，

保存事務(wù)執(zhí)行過程中修改內(nèi)容的東西稱為undo日志。

4.1 undo日志格式

4.1.1 聚簇索引行結(jié)構(gòu)

InnoDB會(huì)將聚簇索引行結(jié)構(gòu)如下圖所示

InnoDB會(huì)將聚簇索引行結(jié)構(gòu)補(bǔ)充trx_id和roll_pointer兩個(gè)隱藏列

trx_id: 一個(gè)事務(wù)某次對(duì)某條聚簇索引記錄進(jìn)行改動(dòng)時(shí)，都會(huì)把該事務(wù)的事務(wù)ID賦值給trx_id，事務(wù)ID是單調(diào)遞增的

roll_pointer: 每次對(duì)某條聚簇索引進(jìn)行改動(dòng)時(shí)，都會(huì)把舊版本寫入到undo日志中，并以聚簇索引為起點(diǎn)構(gòu)成一個(gè)從最新到最舊的單向鏈表結(jié)構(gòu)，這個(gè)鏈表就稱為版本鏈

roll_pointer結(jié)構(gòu)如下圖所示

is_insert, 表示該指針指向的undo日志是否是TRX_UNDO_INSERT大類的undo日志

resg id, 表示該指針指向的undo日志的回滾段編號(hào)

page number, 表示該指針指向的undo日志所在頁(yè)面的頁(yè)號(hào)

offset, 表示該指針指向的undo日志在頁(yè)面中的偏移量

4.1.2 插入操作

如果需要回滾插入操作，只需要將插入的記錄刪除即可，

因此在記錄undo日志時(shí)，只需要記錄插入的記錄的主鍵信息即可，通過主鍵能找到唯一的記錄

插入操作的對(duì)應(yīng)的undo日志類型為TRX_UNDO_INSERT_REC，結(jié)構(gòu)如下圖所示

undo type, 即TRX_UNDO_INSERT_REC

undo no, 事務(wù)執(zhí)行過程中，每生成一條undo日志，undo no就增加1，且從0開始

table id, 該undo日志對(duì)應(yīng)的記錄所在表的table id

4.1.3 刪除操作

在事務(wù)中執(zhí)行刪除操作，會(huì)將記錄的deleted_flag標(biāo)識(shí)為值為1，但該記錄依然在正常記錄鏈表，并沒有移動(dòng)到垃圾記錄鏈表，這個(gè)過程稱為delete mark。

在事務(wù)提交后，才把該記錄從正常記錄鏈表挪到垃圾記錄鏈表

刪除操作產(chǎn)生TRX_UNDO_DEL_MARK_REC類型的日志，結(jié)構(gòu)如下圖所示，

在對(duì)一條記錄進(jìn)行delete mark操作前，將該記錄的trx_id和roll_pointer的舊值保存到undo日志的trx_id和roll_pointer變量中。

假設(shè)一個(gè)事務(wù)對(duì)某條記錄先更新再刪除，這樣就能通過TRX_UNDO_DEL_MARK_REC找到更新的undo日志。

4.2.4 更新操作

更新操作的場(chǎng)景較復(fù)雜，InnoDB將其分為更新主鍵和不更新主鍵兩種場(chǎng)景。

不更新主鍵

更新的每個(gè)列在更新前后占用的存儲(chǔ)空間不變，則可以進(jìn)行就地更新

更新前后占用存儲(chǔ)空間有變，需要將舊記錄從聚簇索引刪除，再創(chuàng)建一條新的記錄

更新主鍵，舊記錄執(zhí)行delete mark操作，創(chuàng)建新紀(jì)錄插入聚簇索引

針對(duì)以上各種情況，InnnoDB設(shè)計(jì)了對(duì)應(yīng)的undo日志格式，限于篇幅這里就不展開說明。

4.2 undo日志頁(yè)面

和InnoDB普通頁(yè)面結(jié)構(gòu)類型，undo日志頁(yè)面結(jié)構(gòu)及頁(yè)面鏈表如下圖所示。

一個(gè)undo日志頁(yè)面只能存儲(chǔ)一種類型，InnoDB將undo日志分為兩大類，

TRX_UNDO_INSERT，由insert語句產(chǎn)生undo日志，或者update語句更新主鍵也會(huì)產(chǎn)生該類型的undo日志

TRX_UNDO_UPDATE，除了TRX_UNDO_INSERT類型的undo日志，其它類型的undo日志都屬于這個(gè)大類

InnoDB對(duì)臨時(shí)表和普通表產(chǎn)生的undo日志分開記錄，因此一個(gè)事務(wù)最多可能需要4個(gè)undo頁(yè)面鏈表。

4.3 回滾

同一個(gè)時(shí)刻，可能存在多個(gè)事務(wù)在執(zhí)行，為了更好的管理undo頁(yè)面鏈表，

InnoDB設(shè)計(jì)了Rollback Segment Header頁(yè)面用于存放各個(gè)Undo頁(yè)面鏈表的第一個(gè)undo頁(yè)面的頁(yè)號(hào)，即undo slot。

在奔潰恢復(fù)時(shí)，需要將未提交事務(wù)的修改回滾掉，通過undo slot找到undo頁(yè)面鏈表，

通過判斷undo頁(yè)面鏈表的Undo Log SegmentHeader的TRX_UNDO_STATE屬性值，

如果為TRX_UNDO_ACTIVE，則進(jìn)一步通過undo鏈表最后一個(gè)頁(yè)面的Undo Log Header中找到該事務(wù)對(duì)應(yīng)的事務(wù)ID，

然后通過undo日志內(nèi)容將該事務(wù)修改的內(nèi)容全部撤銷，從而保證事務(wù)的原子性。

5. 事務(wù)隔離級(jí)別和MVCC

5.1 常見一致性問題

臟寫(Dirty Write)

一個(gè)事務(wù)修改了另外一個(gè)未提交事務(wù)修改過的數(shù)據(jù)。

臟讀(Dirty Read)

一個(gè)事務(wù)讀取了另外一個(gè)未提交事務(wù)修改過的數(shù)據(jù)。

不可重復(fù)讀(Non-repeatable Read)

一個(gè)事務(wù)修改了另一個(gè)未提交事務(wù)讀取的數(shù)據(jù)。

幻讀(Phantom)

一個(gè)事務(wù)根據(jù)某些搜索條件查詢出一些記錄，在該事務(wù)未提交時(shí)，另一個(gè)事務(wù)寫入了一些符合搜索條件的記錄，

再次以相同條件查詢，前后兩次結(jié)果不一致。

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

SQL標(biāo)準(zhǔn)中定義了四種隔離級(jí)別

讀未提交(Read Uncommitted), 讀已提交(Read Committed), 可重復(fù)讀(Repeatable Read), 串行化(Serializable)。

不同隔離級(jí)別對(duì)應(yīng)的可能和不可能發(fā)生的一致性問題如下圖所示。

其中臟寫問題是不允許發(fā)生的。

5.3 MVCC

5.3.1 版本鏈

InnnoDB存儲(chǔ)引擎的聚簇索引的版本鏈如下圖所示

假設(shè)在某個(gè)時(shí)刻，事務(wù)321、315、301對(duì)某條記錄進(jìn)行Updata操作后，形成的版本鏈如下圖所示。其中事務(wù)301對(duì)這條記錄更新了兩次

5.3.2 MVCC和ReadView

Multi-Version Concurrency Control, 即多版本并發(fā)控制，

多版本并發(fā)控制機(jī)制利用聚簇索引的版本鏈來控制并發(fā)事務(wù)訪問相同記錄時(shí)的行為，從而解決臟讀和不可重復(fù)讀的不一致性問題。

ReadView，即一致性視圖，通過這個(gè)視圖可以判斷版本鏈的某個(gè)版本是否可被當(dāng)前事務(wù)訪問，中ReadView包含以下4個(gè)比較重要的數(shù)據(jù)

creator_trx_id，生成該ReadView的事務(wù)對(duì)應(yīng)的事務(wù)ID

m_ids，在生成ReadView時(shí)當(dāng)前系統(tǒng)中活躍的事務(wù)ID列表

min_trx_id，m_ids的最小值

max_trx_id，生成ReadView時(shí)，系統(tǒng)的下一個(gè)事務(wù)ID值。

判斷是否可見的規(guī)則

如果被訪問版本的trx_id值與ReadView中的creator_trx_id值相同，說明是當(dāng)前事務(wù)在訪問自己修改過的記錄，該版本可以被當(dāng)前事務(wù)訪問

如果被訪問版本的trx_id值小于ReadView中min_trx_id值，說明該版本在當(dāng)前事務(wù)生成ReadView前已經(jīng)提交，因此該版本可以被訪問

如果被訪問版本的trx_id值不小于ReadView中的max_trx_id值，說明該版本的事務(wù)在當(dāng)前事務(wù)生成ReadView后啟動(dòng)，因此該版本不可訪問

如果被訪問版本的trx_id值在ReadView的min_trx_id和max_trx_id之間

如果trx_id在m_ids列表中，說明ReadView生成時(shí)該版本的事務(wù)依然活躍，因此該版本不可訪問

如果trx_id不在在m_ids列表中，說明ReadView生成時(shí)該版本的事務(wù)已經(jīng)提交，因此該版本可以被訪問

順著版本鏈重復(fù)上述操作，直到找到可以訪問的版本，或者到達(dá)版本鏈末尾。如果版本鏈最后一個(gè)版本依然不可見，則查詢結(jié)果為記錄不存在

在讀已提交和可重復(fù)讀隔離級(jí)別下，ReadView生成的時(shí)機(jī)有所不同，

讀已提交在每一次進(jìn)行普通Select操作前都會(huì)生成一個(gè)ReadView，確保了讀取到都是已提交事務(wù)的數(shù)據(jù)

可重復(fù)讀只在第一次進(jìn)行普通Select操作前生產(chǎn)一個(gè)ReadView, 之后查詢操作都重復(fù)使用這個(gè)ReadView，保證了同一個(gè)事務(wù)內(nèi)不同時(shí)間讀到相同數(shù)據(jù)