MySQL事務 - tw511教學網

1. 事務

事務是指一個或者多個資料庫操作，要麼全部沒有執行，要麼全部成功執行。

中途失敗需要回滾到指定狀態，全部執行成功需要確保持久儲存在資料庫中。

事務擁有四個特性，習慣上被稱之為ACID特性。

2. ACID特性

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

假設A有10元，B有15元，C有8元

A給B轉賬5元，操作記為T1。

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

T1操作的大體流程，先讀取A到賬戶餘額，將A的賬戶餘額扣減5元后再寫入資料庫中，

讀取B的賬戶餘額，將B的賬戶餘額增加5元后再寫入到資料庫中。

同時，C給B轉賬4元，操作記為T2。

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

T1操作的大體流程，先讀取C的賬戶餘額，將C的賬戶餘額扣減4元后再寫入資料庫中，

讀取B的賬戶餘額，將B的賬戶餘額增加4元后再寫入到資料庫中。

2.1 原子性(Atomicity)

事務作為一個整體被執行，包含在其中的對資料庫的操作要麼全部被執行，要麼都不執行，是一個最小執行單元，不可分割。

A給B轉賬5元的操作T1是包含多個讀寫操作，這些操作要麼全部執行，要麼全部不執行。

假設由於斷電等意外事件，導致T1只執行了部分操作，如T1:read(A), A=A-5, write(A)

這就會導致A憑空少了5元，並且B沒有收到A轉的5元，

因此事務需要保證保證在事務執行過程中出現錯誤時，將已經執行的操作「復原」，恢復到原始狀態。

2.2 一致性(Consistency)

事務應確保資料庫的狀態從一個一致狀態轉變為另一個一致狀態。

假設A有10元，B有15元，C有8元，不管A, B, C之間如何進行轉賬(沒有其他人蔘與)，三個點賬戶總餘額一定是33元，而不會是其它值。

2.3 隔離性(Isolation)

多個事務並行執行時，一個事務的執行不應影響其他事務的執行。

A給B轉賬5元，同時C給B轉賬4元，這個兩個事務應該是互相隔離的，互不影響。

最終A餘額為5元，C餘額為4元，B總共收到兩次轉賬，餘額應該為24元。

假設T1, T2可以交叉執行，如下圖所示。最終結果看起來B只收到了A的5元轉賬，餘額為20元。

2.4 永續性(Durability)

已被提交的事務對資料庫的修改應該永久儲存在資料庫中。

MySQL操作一般是先寫入快取，滿足指定條件後才將快取更新到磁碟

磁碟寫操作相當耗時，而且同一個事務可能修改多個資料頁面，而且可能執行頁面中一個位元組的資料。

因此每次資料庫提交執行快取重新整理磁碟操作不太合理，

MySQL設計人員通過redo紀錄檔來持久化最小量的資料來達到相同的效果。

3. redo紀錄檔

為了保證事務的永續性，在事務提交動作完成之前，需要把該事務修改所有頁面都重新整理到磁碟，但是存在以下問題

隨機I/O比較慢，一個事務可能修改到多個頁面，這些頁面在磁碟中可能不相鄰，可能需要多次長距離移動磁碟讀寫磁頭。
重新整理完整的資料頁面較慢，一個事務也可能值修改頁面中一個位元組，卻要同步整個頁面到磁碟上。

為了解決上述到問題，InnoDB設計了redo紀錄檔，把事務修改的內容採用特定的格式按順序儲存到磁碟上，

即使在系統崩潰之後，按照redo紀錄檔重新更改資料頁進行資料恢復即可。

3.1 redo紀錄檔格式

redo紀錄檔通用格式如下圖所示

type，redo紀錄檔型別，通過定義不同型別可以達到節省空間的目的
space id，表空間id
page number，頁號
data，redo紀錄檔具體內容

往表中插入一條記錄，可能產生多條redo紀錄檔，因為可能產生聚簇索引對應B+樹頁面的分裂操作，

可能需要性申請資料頁，金額可能要修改各種段、區的統計資訊等，

最終插入一條記錄可能產生多條redo紀錄檔，這些紀錄檔是不可分割的，

在崩潰恢復時，也是將這一組紀錄檔作為不可分割的整體來處理，

類似的，將一組不可分割的redo紀錄檔稱為Mini-Transaction，即MTR

3.2 redo紀錄檔緩衝區

為了避免頻繁的磁碟IO，並不是每生成一條redo紀錄檔就同步到磁碟上。

而是先將redo紀錄檔放到緩衝區，在特定時機重新整理到磁碟。

redo紀錄檔緩衝區頁面結構如下圖所示。

redo紀錄檔是以MTR為單位寫入到redo紀錄檔緩衝區的，redo紀錄檔緩衝區是有若干個512B大小的block構成的一片連續的記憶體空間，

InnoDB引擎使用lsn(log sequence number)來記錄系統當前有多少redo紀錄檔寫入到緩衝區

3.3 redo紀錄檔檔案

3.3.1 flush連結串列中的lsn

InnoDB會將lsn相關資訊寫入到flush連結串列中，進而方便判斷哪些redo紀錄檔檔案可以被重複使用，

因為只要髒頁被重新整理到磁碟，相應的redo紀錄檔內容就沒有存在的意義了，而且redo紀錄檔檔案大小也有限。

Buffer Pool中頁面會在控制塊中記錄頁面的修改資訊

oldest_modification: 第一次修改Buffer Pool中某個頁面時，將MTR開始時的lsn寫入該變數
newest_modification: 每次修改Buffer Pool中某個頁面時，將MTR結束時的lsn寫入該變數

flush連結串列與oldest_modification(o_m)和newest_modification(n_m)的關係如下圖所示。

flush連結串列的基節點start指標出發，flush連結串列的髒頁時按照第一次修改發生的時間倒序排列的，也就是按照oldest_modification代表的lsn值倒敘排列，

當頁面被多次更新時，會更新對應頁面的newest_modification變數的值。

當頁面1被屬性到磁碟，從頁面2的控制塊可以看出，lsn低於8916的redo紀錄檔可以被覆蓋，系統會將8916賦值到redo檔案的checkpoint_lsn的操作。

InnoDB將檢查flush連結串列最小的oldest_modification的lsn值稱為checkpoint操作。

3.3.2 redo紀錄檔檔案格式

磁碟上存在多個redo紀錄檔檔案，會被迴圈使用，這一組redo紀錄檔檔案稱為redo紀錄檔檔案組。

和redo紀錄檔緩衝區一樣，redo紀錄檔檔案也是由若干個512B構成的block組成

其中，redo紀錄檔檔案的頭2048個位元組用於儲存一些管理資訊，系統會將checkpoint操作得到的checkpoint_lsn賦值到checkpoint1的checkpoint_lsn上。

崩潰恢復會從checkpoint_lsn在紀錄檔檔案組中對應的偏移量開始。

除了前面闡述的checkpoint，redo紀錄檔刷盤時機還包括

redo紀錄檔快取不足時
事務提交時
後臺執行緒週期性刷盤
正常關閉伺服器

3.3.3 奔潰恢復

當遇到異常情況導致伺服器掛掉，在重啟時可以根據redo紀錄檔檔案恢復到奔潰前的狀態。

InnoDB從redo紀錄檔檔案組的第一個檔案的checkpoint資訊，然後從checkpoint_lsn在紀錄檔檔案組中對應的偏移量開始，

一直掃描紀錄檔檔案中的，直到某個block的寫入量的值不等於512，根據redo紀錄檔格式將修改的內容恢復到奔潰前狀態。

4. undo紀錄檔

在事務執行過程中可能遇到各種錯誤，導致中途就結束事務了，但是在遇到錯誤退出前，可能修改多個行記錄，

但是為了保證事務的原子性，需要將資料恢復到事務開啟前，這個恢復過程就稱為回滾。

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

儲存事務執行過程中修改內容的東西稱為undo紀錄檔。

4.1 undo紀錄檔格式

4.1.1 聚簇索引行結構

InnoDB會將聚簇索引行結構如下圖所示

InnoDB會將聚簇索引行結構補充trx_id和roll_pointer兩個隱藏列

trx_id: 一個事務某次對某條聚簇索引記錄進行改動時，都會把該事務的事務ID賦值給trx_id，事務ID是單調遞增的
roll_pointer: 每次對某條聚簇索引進行改動時，都會把舊版本寫入到undo紀錄檔中，並以聚簇索引為起點構成一個從最新到最舊的單向連結串列結構，這個連結串列就稱為版本鏈

roll_pointer結構如下圖所示

is_insert, 表示該指標指向的undo紀錄檔是否是TRX_UNDO_INSERT大類的undo紀錄檔
resg id, 表示該指標指向的undo紀錄檔的回滾段編號
page number, 表示該指標指向的undo紀錄檔所在頁面的頁號
offset, 表示該指標指向的undo紀錄檔在頁面中的偏移量

4.1.2 插入操作

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

因此在記錄undo紀錄檔時，只需要記錄插入的記錄的主鍵資訊即可，通過主鍵能找到唯一的記錄

插入操作的對應的undo紀錄檔型別為TRX_UNDO_INSERT_REC，結構如下圖所示

undo type, 即TRX_UNDO_INSERT_REC
undo no, 事務執行過程中，每生成一條undo紀錄檔，undo no就增加1，且從0開始
table id, 該undo紀錄檔對應的記錄所在表的table id

4.1.3 刪除操作

在事務中執行刪除操作，會將記錄的deleted_flag標識為值為1，但該記錄依然在正常記錄連結串列，並沒有移動到垃圾記錄連結串列，這個過程稱為delete mark。

在事務提交後，才把該記錄從正常記錄連結串列挪到垃圾記錄連結串列

刪除操作產生TRX_UNDO_DEL_MARK_REC型別的紀錄檔，結構如下圖所示，

在對一條記錄進行delete mark操作前，將該記錄的trx_id和roll_pointer的舊值儲存到undo紀錄檔的trx_id和roll_pointer變數中。

假設一個事務對某條記錄先更新再刪除，這樣就能通過TRX_UNDO_DEL_MARK_REC找到更新的undo紀錄檔。

4.2.4 更新操作

更新操作的場景較複雜，InnoDB將其分為更新主鍵和不更新主鍵兩種場景。

不更新主鍵
- 更新的每個列在更新前後佔用的儲存空間不變，則可以進行就地更新
- 更新前後佔用儲存空間有變，需要將舊記錄從聚簇索引刪除，再建立一條新的記錄
更新主鍵，舊記錄執行delete mark操作，建立新紀錄插入聚簇索引

針對以上各種情況，InnnoDB設計了對應的undo紀錄檔格式，限於篇幅這裡就不展開說明。

4.2 undo紀錄檔頁面

和InnoDB普通頁面結構型別，undo紀錄檔頁面結構及頁面連結串列如下圖所示。

一個undo紀錄檔頁面只能儲存一種型別，InnoDB將undo紀錄檔分為兩大類，

TRX_UNDO_INSERT，由insert語句產生undo紀錄檔，或者update語句更新主鍵也會產生該型別的undo紀錄檔
TRX_UNDO_UPDATE，除了TRX_UNDO_INSERT型別的undo紀錄檔，其它型別的undo紀錄檔都屬於這個大類

InnoDB對臨時表和普通表產生的undo紀錄檔分開記錄，因此一個事務最多可能需要4個undo頁面連結串列。

4.3 回滾

同一個時刻，可能存在多個事務在執行，為了更好的管理undo頁面連結串列，

InnoDB設計了Rollback Segment Header頁面用於存放各個Undo頁面連結串列的第一個undo頁面的頁號，即undo slot。

在奔潰恢復時，需要將未提交事務的修改回滾掉，通過undo slot找到undo頁面連結串列，

通過判斷undo頁面連結串列的Undo Log SegmentHeader的TRX_UNDO_STATE屬性值，

如果為TRX_UNDO_ACTIVE，則進一步通過undo連結串列最後一個頁面的Undo Log Header中找到該事務對應的事務ID，

然後通過undo紀錄檔內容將該事務修改的內容全部復原，從而保證事務的原子性。

5. 事務隔離級別和MVCC

5.1 常見一致性問題

髒寫(Dirty Write)

一個事務修改了另外一個未提交事務修改過的資料。

髒讀(Dirty Read)

一個事務讀取了另外一個未提交事務修改過的資料。

不可重複讀(Non-repeatable Read)

一個事務修改了另一個未提交事務讀取的資料。

幻讀(Phantom)

一個事務根據某些搜尋條件查詢出一些記錄，在該事務未提交時，另一個事務寫入了一些符合搜尋條件的記錄，

再次以相同條件查詢，前後兩次結果不一致。

5.2 事務隔離級別

SQL標準中定義了四種隔離級別

讀未提交(Read Uncommitted), 讀已提交(Read Committed), 可重複讀(Repeatable Read), 序列化(Serializable)。

不同隔離級別對應的可能和不可能發生的一致性問題如下圖所示。

其中髒寫問題是不允許發生的。

5.3 MVCC

5.3.1 版本鏈

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

假設在某個時刻，事務321、315、301對某條記錄進行Updata操作後，形成的版本鏈如下圖所示。其中事務301對這條記錄更新了兩次

5.3.2 MVCC和ReadView

Multi-Version Concurrency Control, 即多版本並行控制，

多版本並行控制機制利用聚簇索引的版本鏈來控制並行事務存取相同記錄時的行為，從而解決髒讀和不可重複讀的不一致性問題。

ReadView，即一致性檢視，通過這個檢視可以判斷版本鏈的某個版本是否可被當前事務存取，中ReadView包含以下4個比較重要的資料

creator_trx_id，生成該ReadView的事務對應的事務ID
m_ids，在生成ReadView時當前系統中活躍的事務ID列表
min_trx_id，m_ids的最小值
max_trx_id，生成ReadView時，系統的下一個事務ID值。

判斷是否可見的規則

如果被存取版本的trx_id值與ReadView中的creator_trx_id值相同，說明是當前事務在存取自己修改過的記錄，該版本可以被當前事務存取
如果被存取版本的trx_id值小於ReadView中min_trx_id值，說明該版本在當前事務生成ReadView前已經提交，因此該版本可以被存取
如果被存取版本的trx_id值不小於ReadView中的max_trx_id值，說明該版本的事務在當前事務生成ReadView後啟動，因此該版本不可存取
如果被存取版本的trx_id值在ReadView的min_trx_id和max_trx_id之間
1. 如果trx_id在m_ids列表中，說明ReadView生成時該版本的事務依然活躍，因此該版本不可存取
2. 如果trx_id不在在m_ids列表中，說明ReadView生成時該版本的事務已經提交，因此該版本可以被存取
順著版本鏈重複上述操作，直到找到可以存取的版本，或者到達版本鏈末尾。如果版本鏈最後一個版本依然不可見，則查詢結果為記錄不存在

在讀已提交和可重複讀隔離級別下，ReadView生成的時機有所不同，

讀已提交在每一次進行普通Select操作前都會生成一個ReadView，確保了讀取到都是已提交事務的資料
可重複讀只在第一次進行普通Select操作前生產一個ReadView, 之後查詢操作都重複使用這個ReadView，保證了同一個事務內不同時間讀到相同資料