MySQL事務ACID原理深度決議-有解無憂

什么是MySQL事務？

事務是指對資料庫的一組操作的集合，集合中的SQL陳述句要么全部執行成功，要么就全部失敗，如果集合中任一操作出錯，則此集合所有對資料庫的操作全部回滾，

以常見的購物操作舉例，用戶下單后要執行訂單創建、減庫存等一系列操作，這些操作就是一個事務，以原子的方式執行，要么全部成功，要么失敗回滾，避免出現用戶下單了但是庫存沒有扣減的問題，當然真實環境中的業務要比這個復雜的多，在微服務專案中還會涉及到分布式事務問題，

事務的特性

首先來了解下什么是事務的特性，SQL標準中定義了事務應具有 原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、持久性（Durability）四個特性，簡稱 ACID

原子性：

指一個事務是一個不可分割的操作集合，其中的操作要么都做，要么都不做；如果其中任意一個SQL執行失敗，則整個事務必須回滾，將資料庫狀態恢復至事務開始之前，

一致性：

指事務執行完成后，資料庫完整性約束沒有被破壞，事務執行前后都是合法的資料狀態，

隔離性：

指不同事務間的操作互相不可見，互不影響，資料庫隔離級別主要涉及的就是事務間的隔離性問題，

持久性：

指事務提交后對資料庫的修改時永久的，接下來的其他操作或故障不應該對其有任何影響，

一、原子性

MySQL的日志有很多種，例如Binlog、錯誤日志、慢查詢日志、查詢日志等；MySQL還提供了事務日志：redo log（重做日志）和undo log（回滾日志），undo log就是實作事務原子性的關鍵，

事務執行時對資料庫所做的修改，都會寫入undo log，例如INSERT、UPDATE、DELETE；如果事務執行失敗回滾，則會利用undo log中的資訊回滾，執行相反操作：對于每個insert，回滾時會執行delete；對于每個delete，回滾時會執行insert；對于每個update，回滾時會執行一個相反的update把資料改回去，

例如UPDATE操作：當事務執行UPDATE時，undo log 會記錄被修改行的主鍵，修改的列以及修改前后的資訊，在事務回滾時使用這些資訊回滾，

二、持久性

類似于undo log，redo log也屬于事務日志，

首先介紹下redo log存在的背景，

InnoDB引擎的資料是存在磁盤中的，我們都知道磁盤IO的資料是很慢的，如果每次讀寫資料都去做IO，效率會很低，因此，InnoDB采用了快取機制（Buffer Pool），Buffer Pool中有磁盤資料頁的映射，從資料庫中讀資料前會先從Buffer Pool中讀取，如果沒有，則從磁盤中讀取后放入Buffer Pool中；資料寫入時，也是會先寫入Buffer Pool中，再由MySQL定期刷入磁盤中，稱為“刷臟”，

任何新技術的參考總是會帶來新的問題，Buffer Pool機制的使用雖然大幅提升了MySQL的讀寫效率，但是一旦遇到MySQL宕機，但是Buffer Pool中的資料還沒刷入磁盤中，就會導致資料丟失，則破壞了事務的持久性，

綜上所述，redo log就是為了解決這個問題，資料修改前，會先將修改記錄寫入Buffer Pool中，事務提交后，MySQL會將redo log的記錄刷入磁盤中，這樣即使MySQL宕機，也能保證資料不丟失，啟動后根據redo log中的記錄恢復資料即可，

三、隔離性

隔離性是事務中最關鍵的一個特性，我們常說的事務的隔離級別就是面向事務的隔離性來討論的，InnoDB在不同的隔離級別下使用了不同的實作機制，這一節也是本文的重點內容，

首先我們來了解下事務的隔離級別

SQL標準定義了四種事務間的隔離級別，MySQL都支持：

讀未提交（READ UNCOMMITTED）
讀已提交（READ COMMITED）
可重復讀（REPEATABLE READ）
串行化（SERIALIZABLE）

從1 - 4隔離強度遞增，并發性能遞減，MySQL的InnoDB默認的隔離級別是 可重復讀（REPEATABLE READ），

事務的隔離級別是為了解決事務并發中可能會產生的問題：

臟讀：

指事務的SELECT讀取到了其他事務未提交的資料，如果其他事物回滾，則產生臟讀，

可重復讀：

指在一個事務內，任意時刻讀到的資料都是一致的，例如在同一時刻內，事務A和事務B修改了同一行記錄，但是互相的修改不可見，這就是可重復讀，通常指的是更新（UPDATE）操作

不可重復讀：

與可重復讀相反，任意時刻讀到的資料不一致，也指更新（UPDATE）操作

幻讀：

指事務A中，執行了一次查詢，之后事務B又執行了一次插入（INSERT）操作并提交，下一時刻事務A又執行了一次查詢，查到了事務B插入的資料，好像發生幻覺一樣，就叫做幻讀，

事務隔離級別就是為了解決上述問題，不同隔離級別下能解決的程度不同，見下表，

隔離級別	臟讀	不可重復讀	幻讀
讀未提交	×	×	×
讀已提交	√	×	×
可重復讀	√	√	×
串行	√	√	√

讀未提交與串行由于資料一致性與性能的問題，基本不用，所以本文重點探討讀已提交和可重復讀的實作原理，

隔離性探討要分兩個部分來說：

1.事務寫與寫之間的隔離，這主要是通過鎖機制來實作的，

2.事務寫與讀之間的隔離，這主要是通過MVCC機制實作的，

1、寫與寫的隔離

首先我們來了解下InnoDB解決事務間寫與寫隔離的鎖機制：

事務在修改資料行之前，必須先獲得鎖才可以操作；獲得鎖之后，事務便可以操作資料，在此期間其他需要操作此行資料的事務只能阻塞等待，在事務完成或回滾后即可釋放鎖，讓下一個事務繼續爭奪鎖，

表鎖與行鎖

從鎖粒度的角度來說，鎖分為行鎖與表鎖，行鎖只會鎖定對應行的資料，在此期間其他事物不可修改此行資料；表鎖會鎖定整個表的資料，在此期間其他任何事務的修改操作都會阻塞，性能極差，

但是由于加鎖本身需要消耗資源(獲得鎖、檢查鎖、釋放鎖等)，因此在鎖定資料較多情況下使用表鎖可以節省大量資源，

如下陳述句可以查看鎖資訊：

select * from sys.innodb_lock_waits; //8.0之后的陳述句

select * from information_schema.innodb_locks;

舉個例子，以 8.0.18 的MySQL為例：

現有表資訊如下，id列是主鍵欄位：

執行以下陳述句后：

start transaction;
update t set a = 122 where id = 1;

start transaction;
update t set a = 1222 where id = 1;

可以看到記錄添加了一個排它鎖(x)，鎖型別為行鎖(record):

間隙鎖（GAP LOCK）與臨鍵鎖（NEXT-KEY LOCK）

間隙鎖與臨鍵鎖也可以理解為行鎖，只是鎖的資料行多了些，

注意：間隙鎖與臨鍵鎖只在非唯一索引上有效，

間隙鎖基于非唯一索引，注意：使用間隙鎖鎖住的是一個區間，而不僅僅是這個區間中的每一條資料，

select * from account where id between 1 and 10 for update;

所有在（1，10）區間內的行都會鎖住，所有id 為 2、3、4、5、6、7、8、9 的資料行的插入會被阻塞，但是 1 和 10 兩條記錄行并不會被鎖住，

間隙鎖的鎖定范圍為索引上命中或未命中的資料行的左最近一個記錄和右最近的一行記錄的左開右開區間，

例如：

圖中是一個age索引列上的資料，其中，(1,5)、(5,9)、(10,15) 就是間隙，在一個事務內執行如下陳述句時：

select * from user where age > 5 and age < 9 for update;

這個時候區間( 5, 9 )是加了間隙鎖的，任何其他事務的修改(insert 、update、delete)都被阻塞，無法進行，直到持有鎖的事務提交或者回滾釋放鎖后，其他事務才能執行操作，

臨鍵鎖在使用非唯一索引進行范圍查詢，且命中了記錄的情況下才會使用，相當于記錄鎖 + 間隙鎖，

臨鍵鎖的鎖定范圍為左開右閉區間，目的是為了解決幻讀的問題，

臨鍵鎖有兩種退化的情況：

1. 如果是唯一性索引，等值查詢匹配到一條記錄的時候，退化成記錄鎖，

2. 如果沒有匹配到任何記錄的時候，退化成間隙鎖，

考慮如下SQL：

select * from user where age > 5 and age < 15;

上面的SQL命中了age = 9的資料，也包含了不存在的記錄的區間，所以(5, 9] 和 (10, 15]區間會被同時鎖定，這期間別的事務插入不了資料，也更新不了資料，

以上介紹的行鎖(RECORD LOCK)、間隙鎖(GAP LOCK)、臨鍵鎖(NEXT KEY LOCK)的使用解決了事務間寫與寫的隔離性問題，接下來介紹事務間寫與讀的隔離機制，

2. 讀與寫的隔離

InnoDB解決事務間讀寫的隔離采用的是MVCC(Multi-Version Concurrency Control)機制，即多版本并發控制協議，

用一個例子來說明MVCC的特點：

同一時刻，不同事務可以讀到不同版本的資料，在T5時刻，事務A和C可以讀到不同版本的資料，

MVCC的優勢在于讀不加鎖，通過對資料行的版本控制實作讀寫的隔離，并發性能優異，下面我們來深度分析一下MVCC的實作原理，

先來了解幾個概念：

1. 隱藏列：InnoDB中每行記錄都有隱藏列，包含本行資料當前事務的事務id、指向undo log的指標等，

2. 基于undo log的版本鏈：隱藏列中包含指向undo log的指標，每條undo log也包含指向前一版本的指標，由此形成了一條版本鏈，

3.ReadView：指事務在某一時刻給整個事務系統（trx_sys）打快照，后續讀操作會將讀取到的資料事務id與快照作比較，借此判斷是否資料是否對當前事務可見，如不可見則遍歷undo log指標到該資料的前一個版本號，

trx_sys中的主要內容如下：

low_limit_id：表示生成ReadView時事務系統即將分配給下一個事務的事務id，事務系統對事務的id分配是順序遞增的，

up_limit_id：表示生成ReadView時事務系統中活躍的事務中最小的事務id，

rw_trx_ids：表示生成ReadView時活躍的事務id串列，

判斷可見性的邏輯如下：

1. 如果資料的事務id大于等于low_limit_id，則對該ReadView不可見，

2. 如果資料事務id小于up_limit_id，則對該ReadView可見，

3. 如果資料事務id在low_limit_id和up_limit_id之間，則需要判斷事務id是否在rw_trx_ids中，如果在，表明生成ReadView時該事務仍在活躍，所以該資料對ReadView不可見；如果不在，表明生成ReadView時該事務已經提交了，則可見，

前面提到MVCC用于解決事務間寫與讀的隔離性問題，在可重復讀（REPEATABLE Read）級別下，MVCC解決了臟讀、不可重復讀、幻讀的問題，下面一一舉例來說明，

參考以上表格，事務A與B在同時開始，事務A在T3時刻查詢余額，會生成ReadView，此時事務B未提交仍在活躍，因此事務B的id會在rw_trx_ids中，所以事務B的修改對事務A不可見，事務A判斷不可見后會根據隱藏列的undo log指標查詢前一版本

的資料，得到值為100，這樣就避免了讀到事務B未提交的資料，避免了臟讀，

參考以上表格，事務A在T2時刻查詢余額，查詢執行前會生成ReadView；事務B在T3時刻修改余額，隨后提交事務，事務A在T5時刻再次查詢了余額，使用首次查詢生成的ReadView來判斷，此時資料的事務id大于ReadView的low_limit_id，事務A

即從undo log的指標查詢前一版本的資料，余額依舊查詢為100，避免了不可重復讀，

參考以上表格，事務A在T2時刻查詢資料前會生成一個ReadView；此時事務B在T3時刻插入了一個新用戶，且用戶主鍵在事務的查詢區間中，事務B可以分兩種情況來討論：

1. 一種是如圖中所示，事務已經開始但沒有提交，此時其事務id在ReadView的rw_trx_ids中；

2. 一種是事務B還沒有開始，此時其事務id大于等于ReadView的low_limit_id，

無論哪種情況，事務B的修改都是不可見的，

事務A在T5時刻再次讀取余額時，會根據首次查詢生成的ReadView判斷出事務B的修改是不可見的，因此會根據undo log指標查詢上一版本的資料，發現上一版本沒有資料，不作任何處理，避免了幻讀，

總結：

前文介紹了InnoDB事務隔離性的大致實作原理，需要注意的是，MVCC在非加鎖讀的情況下生效，如果對select陳述句顯式的執行了 for update或for share關鍵字，InnoDB會采用鎖的形式來控制隔離，

在讀已提交和可重復讀的MVCC實作中對ReadView的生成是有些區別的，讀已提交在每次Select都會重新生成ReadView，從而實作對已提交的事務資料的可見，可重復讀則只會在事務首次Select時生成ReadView，從而保證

事務生命周期中對其他事務的修改的完全隔離，

四、一致性

一致性的實作其實是基于前文所提及的原子性、持久性和隔離性，換句話說，只有保證了原子性、持久性與隔離性，才能保證一致性，

此外，應用層面的一致性保證也是需要的，例如常見的轉賬操作，扣減庫存等，需要參考層面的并發控制機制來實作，

結語：

本文是對近期MySQL學習的總結和梳理，受本人水平所限，難免有出入之處，煩請各位讀者不吝賜教，

參考文獻：

https://www.cnblogs.com/kismetv/p/10331633.html

https://segmentfault.com/a/1190000040129107

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/445396.html

標籤：其他

上一篇：關于SQL Server 各種安裝失敗均失敗，報錯“等待資料庫引擎恢復句柄失敗”的經驗分享

下一篇：MySQL優化之索引決議