一文說清 InnoDB 的事務機制

2020 年 9 月 11 日
筆記
MYSQL 系列

我們從一個轉賬的故事開始。

隔壁小王從美團上找到了一家水餃店，準備中午吃水餃。下單成功，支付20元。

商家這裡響了一下：叮叮，您有美團外賣新訂單啦，請及時處理。水餃一份，好嘞，下鍋。

很快小王吃到外賣了，吃完美美地躺下開始睡覺。

突然手機一頓猛響。一個陌生的號碼打過來的，又是賣房的吧。小王想想沒理他，繼續睡。

可是這哥么鍥而不捨，一會又打過來了。小王忍無可忍準備接過電話罵他一頓。剛接電話聽到對面一陣急促的聲音傳來：你好你中午是不是點了一份我們店的水餃？

小王這才意識到感情是水餃店的。趕忙回復到是的啊，咋了。

老闆說：你中午下單付款了嗎？

小王：我肯定付款了啊，不然怎麼下單。

老闆說：我沒收到錢啊。你把付款的截圖發給我。

小王說：我吃飯還能不付錢嗎，你等着。

於是小王給老闆截圖了，老闆拿着截圖去找了美團技術，美團技術一查，轉賬失敗。跟老闆說不好意思，今天這代碼是實習生寫的，我們馬上開除他，稍後轉給你。這時候老闆一顆懸着的心才放下，可不能一天就賣一份水餃還沒收到錢，這不虧大了呢！

以上純屬虛構，沒有詆毀美團實習生的意思。

從上面的問題看，付款成功了，轉賬失敗了，這時候用戶吃到了飯，但是老闆沒收到錢。放在正常的堂食，你不先付款，估計人兒就的趕你出去，一手交錢一手交貨買賣不變的道理。

我們引申出一個概念：最小操作單元。即我們人為定義了一個業務場景，這個場景中的操作要麼全部成功，要麼全部失敗。

英語原文中把這種最小操作單元定義為：transaction ，在英語中的解釋是：

an occasion when someone buys or sells something, or when money is exchanged or the activity of buying or selling something:

a business transaction

Each transaction at the foreign exchange counter seems to take forever

We need to monitor the transaction of smaller deals.

通俗的說就是我們做某事所發生的這個時機或這個場景，代指這整個的發生過程。在 MySQL 中我們把 transaction 翻譯為事務，個人感覺中文意思總和英文有點不搭。

上面這個例子中我們可以了解到 transaction 存在的主要意圖：

在最小操作單元中保持穩定的操作，即使在故障時也能恢復到操作之前的狀態保持數據一致性。
保持各個最小操作單元之前互相隔離，以防止互相交互產生的覆蓋性錯誤。

一般需要事務來控制的場景發生在：

更新–插入–選擇–插入–

即一個最小操作單元中保持兩個及以上的非查詢操作。

事務結束的兩種可能方式：

commit：提交最小操作單元中的所有操作。
terminate：操作終止，最小操作單元中所有修改無效。

數據庫操作的環境：

共享-多用戶並發訪問
不穩定-潛在的硬件/軟件故障

事務所需環境：

不共享 – 一個事務內的操作不受其他事務影響
穩定 – 即使面對系統故障，當前事務的操作也能保留現場

一個事務一旦開始，則必須確保：

所有操作必須可回溯
所有操作對後續操作的影響必須是可見的

一個事務開始的過程中必須確保：

在該事務結束之前其他事務看不到它的結果。

如果事務中止：

必須確保當前事務所有可能影響數據一致性的操作都會被清理。

如果系統出現故障：

必須確保重新啟動時所有未提交的事務都會被清理。

針對以上事務操作過程中可能會出現的問題，抽象出事務如果滿足以下條件，則可以保證數據完整性：

Automicity（原子性）

要麼事務中的所有任務都必須發生，要麼都不發生。
Consistency（一致性）

每個事務都必須保留數據庫的完整性約束（已聲明的一致性規則）。它不能使數據處於矛盾狀態。在執行期間，一系列數據庫操作不會違反任何完整性約束。
Isolation（隔離性）

兩個同時進行的事務不能互相干擾。交易中的中間結果必須對其他交易不可見。其他一系列數據庫操作無法看到一系列數據庫操作的中間狀態。
Durability（持久性）

已完成的事務以後不能中止或放棄其結果。它們必須在崩潰後通過（例如）重新啟動DBMS持續存在。保證已提交的一系列數據庫操作將永久保留。

特意查證了一下，關於事務四大特性的提出最早是在 1983 年由 Andreas Reuter 和 Theo Haerder 兩位關係型數據庫研發的鼻祖在論文：Principles of transaction-oriented database recovery 中提出。論文鏈接，感興趣的可以下載來看看。

事務的 ACID 特性概念簡單，但不是很好理解，主要是因為這幾個特性不是一種平級關係：

只有滿足一致性，事務的執行結果才是正確的。
在無並發的情況下，事務串行執行，隔離性一定能夠滿足。此時只要能滿足原子性，就一定能滿足一致性。在並發的情況下多個事務並行執行，事務不僅要滿足原子性，還需要滿足隔離性，才能滿足一致性。
事務滿足持久化是為了能應對數據庫崩潰的情況。

InnoDB 如何實現事務

鑒於 MyISAM 引擎不支持事務，支持事務的引擎只有 InnoDB，所以下面關於事務的講解都是基於 InnoDB引擎。

在 InnoDB引擎中實現事務最重要的東西就是日誌文件，保證事務的四大特性主要依靠這兩大日誌：

redo log ：保證事務持久性
undo log：回滾日誌，保證事務原子性

兩大日誌系統分別保證了持久性和原子性，另外還有兩大特性是通過什麼來保證的呢？

一致性和隔離性是通過 MVCC 機制和鎖機制來一起控制。先提前介紹，後面我們詳解討論。

典型的事務操作會遵循如下流程：

start transaction;
...... # do your business
commit;

start transaction 標識事務的開始，直到遇到 commit 才會提交事務。在該事務過程中如果出現問題，會自動調用 rollback 邏輯回滾該事物已完成的 sql。

非顯式開啟事務

MySQL 中默認採用的是自動提交的模式：

mysql > show variables like 'autocommit';
+------------------+-------+
|   Variable_name  | Value |
+------------------+-------+
|   autocomment    | ON    |
+------------------+-------+

自動模式下，你無需顯式的輸入 start transaction 作為開頭和使用 commit 作為結尾來標識一個事務。每個sql 語句都會被作為一個事務提交。

當然你也可以關閉自動提交事務機制：

mysql > set autocommit = 0;

需要注意的是：autocommit 參數的修改指只針對當前連接，在一個連接中修改該屬性並不會影響別的連接。

不被 autocommit 影響的操作

MySQL 中提供了一些不會被 autocommit 屬性值所影響的特殊指令，這些指定即使在事務中執行，他們也會立刻執行而不是等到 commit 語句之後再提交，這些特殊指令包括：DDL(create table / drop table / alter table)、lock tables等等。

我們探討事務到底在探討什麼？

事務的定義我們已經了解，無非就是把幾個有上下文關聯的 sql 放在一起操作要麼全部成功，要麼全部失敗。道理很簡單，那我們分析這麼多到底在分析什麼呢？貌似難的點不在於打包執行，在於如果讓這些打包命中不互相影響，事務執行過程中失敗如何回滾操作且不污染現有數據。這些才是我們討論事務應該關注的地方。

這些問題的根本其實又回到了事務的四大特性，不得不說 Theo Haerder 在 1983 年就能抽象出來如此高度凝練的總結實在是讓當下汗顏。

下面我就從 InnoDB 如何保證四大特性入手，逐一分析事務機制的實現。

保證原子性的關鍵技術 – undo log

對於事務的原子性來說，該事務內所有操作要麼全部成功要麼全部失敗就是事務的原子性。

全部成功這個毋庸置疑，如果中間突然失敗，原子性該如何保證呢？是否該回滾當前已經執行成功的操作。

InnoDB 提供了一種日誌：undo log，它有兩個作用：提供回滾和多個行版本控制(MVCC)。

比如一條 delete 操作在 undo log 中會對應一條 insert 記錄，反之亦然。當 update 操作時，它會記錄一條相反的 update 記錄。

當執行 rollback 時，就可以從 undo log 中的邏輯記錄讀取到相應的內容並進行回滾。

有時候應用到行版本控制的時候，也是通過 undo log 來實現的：當讀取的某一行被其他事務鎖定時，它可以從 undo log 中分析出該行記錄以前的數據是什麼，從而提供該行版本信息，讓用戶實現非鎖定一致性讀取。

undo log 的存儲方式

InnoDB 存儲引擎對 undo log 的管理採用段的方式。rollback segment 稱為回滾段，每個回滾段中有 1024 個 undo log slot 。

在以前老版本，只支持 1 個 rollback segment，這樣就只能記錄 1024 個 undo log slot。後來 MySQL5.5 可以支持 128 個 rollback slot，即支持 128 * 1024 個 undo log 操作。

MySQL5.6 之前，undo log 表空間位於共享表空間的回滾段中，共享表空間的默認的名稱是 ibdata，位於數據文件目錄中。
MySQL5.6 之後，undo log 表空間可以配置成獨立的文件，但是提前需要在配置文件中配置，完成數據庫初始化後生效且不可改變 undo log 文件的個數。如果初始化數據庫之前沒有進行相關配置，那麼就無法配置成獨立的表空間了。
MySQL5.7 之後的獨立 undo log 表空間配置參數如下：

innodb_undo_directory = /data/undospace/ #undo獨立表空間的存放目錄
innodb_undo_logs = 128 #回滾段為128KB
innodb_undo_tablespaces = 4 #指定有4個undo log文件

undo log 的刪除時機

undo log 文件的個數是有限制的，所以不用無限堆積日誌文件。undo log 記錄的是當前事務操作的反向記錄，理論上當前事務結束，undo log 日誌就可以廢棄。上面也提到過的多版本並發控制機制在隔離級別為 repeatable read 的時候事務讀取的數據都是該事務最新提交的版本，那麼只要該事務不結束，行版本記錄就不能刪除。

另外不同的 sql 語句對應的 undo log 類型也不一樣，比如：

insert 語句：因為 insert 操作本身只對該事務可見，事務提交之前別的連接是看不到的，所以 insert 操作產生的 undo log 日誌在事務提交之後會馬上直接刪除，後續不會再被別的功能使用。
update / delete 語句：delete 操作在事務中並不會真的先刪除數據，而是將該條數據打上「delete_bit」標識，後續的刪除操作是在事務提交後的 purge 線程獨立操作。這兩種操作產生的 undo log 日誌都可以用反向的 update 來代替，這種操作上面說過 MVCC 機制可能會用上，所以就不能在事務結束之後直接刪除。

在事務提交之後，也不是馬上就刪除該事務對應的 undo log 日誌，而是將該事務對應的文件塊放入到刪除列表中，未來通過 purge 來刪除。並且提交事務時，還會判斷 undo log 分配的頁是否可以重用，如果可以重用，則會分配給後面來的事務，避免為每個獨立的事務分配獨立的 undo log 頁而浪費存儲空間和性能。

持久性 – redo log

redo log 即重做日誌，重做日誌記錄每次操作的物理修改。

說 redo log 之前其實是要先說一下 binlog，不然就不知道為什麼要引入 redo log。

bin log = binary log，二進制日誌，它記錄了除了 select 之外所有的 DDL 和 DML 語句。以事件形式記錄，還包含語句所執行的消耗的時間，MySQL 的二進制日誌是事務安全型的。

binlog日誌有兩個最重要的使用場景：

mysql 主從複製： mysql replication 在 master 端開啟 binlog，master 把它的二進制日誌傳遞給 slaves 來達到 master-slave 數據一致的目的。
數據恢復：通過 mysqlbinlog 工具來恢複數據。

binlog 日誌包括兩類文件：

二進制日誌索引文件（文件名後綴為 .index）用於記錄所有的二進制文件。
二進制日誌文件（文件名後綴為 .00000*）記錄數據庫所有的 DDL 和 DML 語句事件。

binlog 文件是通過追加的方式寫入的，可通過配置參數max_binlog_size設置每個 binlog 文件的大小，當文件大小大於給定值後，日誌會發生滾動，之後的日誌記錄到新的文件上。
binlog 有兩種記錄模式，statement 格式的話是記 sql 語句，row 格式會記錄行的內容。

持久性問題一般在發生故障的情況才會重視。在啟動 MySQL 之後無論上次是否正常關閉都會進行恢復操作，我們假設現在沒有 redo log 只有 binlog，那麼數據文件的更新和寫入 binlog 只有兩種情況：

先更新數據文件，再寫入 binlog；
先寫入 binlog，再更新數據文件。

如果先更新數據文件，接着服務器宕機，則導致 binlog 中缺少最後的更新信息；如果先寫 binlog 再更新數據則可能導致數據文件未被更新。

所以在只有 binlog 的環境中 MySQL 是不具備 crash-safe 的能力。另外一開始的 MySQL 使用 MyISAM 引擎，它只有 binlog，所以自然不支持事務。後面引入了 InnoDB 之後才開始使用另外一套日誌系統- redo log 來實現 crash-safe 功能。

redo log 和 binlog 的區別：

redo log 是 InnoDB 引擎特有的，binlog 是MySQL server 層實現的功能，與引擎無關。
redo log 是物理日誌，記錄「在某個數據頁做了什麼修改」；binlog 是邏輯日誌，記錄 sql 語句的原始邏輯，比如「給 ID = 1 這一行的 name value set 『xiaoming』」。
redo log 空間是固定的，用完之後會覆蓋之前的數據；binlog 是追加寫，當前文件寫完之後會開啟一個新文件繼續寫。

redo log 由兩部分組成：

內存中的重做日誌緩衝(redo log buffer)
重做日誌文件(redo log file)

一個更新事務的整體流程

從一個事務的更新過程出發看看一個事務更新過程中 redo log 處於什麼地位。

首先檢查 Buffer cache 中是否存在這條數據，如果存在直接返回，如果不存在則去索引樹中讀取這條數據並加載到 Buffer Cache。
執行器拿到這條行數據之後對它執行相應的更新操作。
將這條待更新的行數據調用執行引擎更新到 Buffer Cache 中，同時將這個記錄更新到 redo log 裏面，redo log 包含兩個部分的更新，更新完畢，此時 redo log 處於 prepare 的狀態，然後告訴執行器，你可以提交事務。
執行器生成這個操作的 binlog 日誌，並把 binlog 寫入磁盤。
執行器調用引擎的提交事務接口，引擎把剛寫入的 redo log 改為 commit 狀態，整個事務提交完成。

這裡我們注意到在 redo log 的提交過程中引入了兩階段提交。

兩階段提交

為什麼必須有「兩階段提交」呢？這是為了讓兩份日誌之間的邏輯一致。

前面我們說過了，binlog 會記錄所有的邏輯操作，並且是採用「追加寫」的形式。如果你的 DBA 承諾說半個月內可以恢復，那麼備份系統中一定會保存最近半個月的所有binlog，同時系統會定期做整庫備份。

由於 redo log 和 binlog 是兩個獨立的邏輯，如果不用兩階段提交，要麼就是先寫完 redo log 再寫 binlog，或者採用反過來的順序，我們看看這兩種方式會有什麼問題，用上面的 update 示例做假設：

先寫 redo log 後寫 binlog。假設在 redo log 寫完，binlog 還沒有寫完的時候，MySQL 進程異常重啟。因為 redo log 已經寫完，系統即使崩潰仍然能夠把數據恢復回來。但是 binlog 裏面就沒有記錄這個語句，因此備份日誌的時候 binlog 裏面就沒有這條語句。

但是如果需要用這個 binlog 來恢復臨時庫的話，由於這個語句的 binlog 丟失，恢復出來的值就與原庫值不同。
先寫 binlog 後寫 redo log。如果在 binlog 寫完之後宕機，由於 redo log 還沒寫，崩潰恢復以後這個事務無效，所以這一行的值還是未更新以前的值。但是 binlog 裏面已經記錄了崩潰前的更新記錄， binlog 來恢復的時候就多了一個事務出來與原庫的值不同。

可以看到，兩階段提交就是為了防止 binlog 和 redo log 不一致發生。同時我們也注意到為了這個崩潰恢復的一致性問題引入了很多新的東西，也讓系統複雜了很多，所以有得有失。

InnoDB通過 Force Log at Commit 機制保證持久性：當事務提交(COMMIT)時，必須先將該事務的所有日誌緩衝寫入到重做日誌文件進行持久化，才能 COMMIT 成功。

為了確保每次日誌都寫入 redo log 文件，在每次將 redo log buffer cache 寫入重做日誌文件後，InnoDB 引擎都需要調用一次 fsync 操作。因此磁盤的性能決定了事務提交的性能，也就是數據庫的性能。

innodb_flush_log_at_trx_commit 參數控制重做日誌刷新到磁盤的策略：

0：事務提交時不進行寫入重做日誌操作，僅在 master thread 每秒進行一次。
1：事務提交時必須調用一次fsync操作。
2：僅寫入文件系統緩存，不進行fsync操作。

log buffer 根據如下規則寫入到磁盤重做日誌文件中：

事務提交時。
當 log buffer 中有一半的內存空間已經被使用。
log checkpoint 時，checkpoint在一定程度上代表了刷到磁盤時日誌所處的LSN位置。

一致性和隔離性實現 – 鎖機制和 MVCC

實現一致性和隔離性是保證數據準確性的關鍵一環，前面兩個特性保證數據恢復不出問題，這兩個特性要保證數據插入和讀取不出問題。實現一致性和隔離性主要使用了兩個機制：

鎖機制
多版本並發控制

下面我們就事務會產生哪些問題，MySQL 提出什麼方式來解決問題，這些方式的實現方案又是什麼來講解。

並發下事務會產生哪些問題

事務 A 和事務 B 同時操作一個資源，根據不同的情況可能會出現不同問題，總結下來有以下幾種：

臟讀

事務 A 讀到了事務 B 還未提交的數據。
幻讀

在當前事務中發現了不屬於當前事務操作的數據。幻讀是針對數據 insert 操作來說的。假設事務A對某些行的內容作了更改，但是還未提交，此時事務 B 插入了與事務 A 更改前的記錄相同的記錄行，並且在事務 A 提交之前先提交了，而這時，在事務A中查詢，會發現好像剛剛的更改對於某些數據未起作用，但其實是事務 B 剛插入進來的，讓用戶感覺出現了幻覺，這就叫幻讀。
可重複讀

可重複讀指的是在一個事務內，最開始讀到的數據和事務結束前的任意時刻讀到的同一批數據都是一致的。通常針對數據 update 操作。
不可重複讀

在同一個事務中兩次讀取一個數據的結果不一樣。對比可重複讀，不可重複讀指的是在同一事務內，不同的時刻讀到的同一批數據可能是不一樣的，可能會受到其他事務的影響，比如其他事務改了這批數據並提交了。

為什麼會提出隔離級別的概念

為了解決事務並發過程中可能會產生的這些問題，SQL 標準定義的四種隔離級別被 ANSI（美國國家標準學會）和 ISO/IEC（國際標準）採用，每種級別對事務的處理能力會有不同程度的影響。

SQL 標準定義了四種隔離級別，MySQL 全都支持。這四種隔離級別分別是：

讀未提交（READ UNCOMMITTED）
讀提交（READ COMMITTED）
可重複讀（REPEATABLE READ）
串行化（SERIALIZABLE）

從上往下，隔離強度逐漸增強，性能逐漸變差。採用哪種隔離級別要根據系統需求權衡決定，其中，可重複讀是 MySQL 的默認級別。

事務隔離其實就是為了解決上面提到的臟讀、不可重複讀、幻讀這幾個問題，下面展示了 4 種隔離級別對這三個問題的解決程度。

隔離級別	臟讀	不可重複讀	幻讀
讀未提交	會發生	會發生	會發生
讀提交	不會發生	會發生	會發生
可重複讀	不會發生	不會發生	會發生
串行化	不會發生	不會發生	不會發生

只有串行化的隔離級別解決了全部這 3 個問題，其他的 3 個隔離級別都有缺陷。

如何設置事務隔離級別

我們可以通過以下語句查看當前數據庫的隔離級別，通過下面語句可以看出我使用的 MySQL 的隔離級別是 REPEATABLE-READ，也就是可重複讀，這也是 MySQL 的默認級別。

mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.02 sec)

或者：

mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

當然我們也能手動修改事務的隔離級別：

set [作用域] transaction isolation level [事務隔離級別];
作用域包含：
SESSION：SESSION 只針對當前回話窗口
GLOBAL：全局生效

隔離級別包含：
READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE

我們來測試一下各個隔離級別對事務的影響。

新建表：

CREATE TABLE `test_db` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'name',
  PRIMARY KEY (`id`),
  KEY `name_idx` (`name`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='測試表';

插入一些測試數據。

讀未提交（READ UNCOMMITTED）

首先設置事務隔離級別：

set global transaction isolation level READ UNCOMMITTED;

注意：設置完全局隔離級別只對新打開的 session 有效，歷史打開的是不會受到影響的。

首先關閉事務自動提交：

set autocommit = 0;

開啟事務 A：

Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> insert test_db (name) values ('xiaocee');
Query OK, 1 row affected (0.01 sec)

在事務A 中插入一條數據，並未提交事務。

接着開啟事務B：

mysql> select * from test_db;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | xiaocee   |
+----+-----------+
9 rows in set (0.00 sec)

事務 B 中能夠查到這條數據。即不同的事務能讀到對方未提交的數據。連臟讀都無法解決，可重複讀和幻讀更沒法解決。

讀已提交

讀已提交的數據肯定能解決臟讀問題，但是對於幻讀和不可重複讀無法將解決。

首先設置事務隔離級別：

set global transaction isolation level READ COMMITTED;

現在數據庫數據如下：

mysql> select * from test_db;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | xiaoming2 |
|  2 | xiaohong  |
|  3 | xiaowei   |
|  4 | xiaowei1  |
|  5 | xiaoli    |
|  6 | xiaoche   |
|  8 | xiaoche   |
| 10 | xiaoche   |
| 12 | xiaocee   |
+----+-----------+
9 rows in set (0.00 sec)

開啟事務 A 將 id=1 的數據改為「xiaoming3」：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_db set name = 'xiaoming3' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

這裡事務 A 未提交，接着開啟事務B 做第一次查詢：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_db where id = 1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | xiaoming2 |
+----+-----------+
9 rows in set (0.00 sec)

事務B查詢還是原始值。

下面提交事務 A：

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

接着在事務 B 中再查詢一次：

mysql> select * from test_db where id = 1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | xiaoming3 |
+----+-----------+
1 row in set (0.00 sec)

當然這次查到的肯定是人家已提交的數據。這裡發生的問題就是不可重複讀：即同一個事務內每次讀取同一條數據的結果不一樣。

可重複讀

可重複讀隔離級別的含義就是重讀每次都一樣不會有問題。這就意味着一個事務不會讀取到別的事務未提交的修改。但是這裡就會有另一個問題：在別的事務提交之前它讀到的數據不會發生變化，那麼另一個事務如果將結果 a 改為 b，接着又改為了 a，對於當前事務來說直到另一個事務提交之後它再讀才會獲取到最新結果，但是它並不知道這期間別的事務對數據做了更新，這就是幻讀的問題。

首先設置事務隔離級別：

set global transaction isolation level REPEATABLE READ;

現在數據庫數據如下：

mysql> select * from test_db;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | xiaoming3 |
|  2 | xiaohong  |
|  3 | xiaowei   |
|  4 | xiaowei1  |
|  5 | xiaoli    |
|  6 | xiaoche   |
|  8 | xiaoche   |
| 10 | xiaoche   |
| 12 | xiaocee   |
+----+-----------+
9 rows in set (0.00 sec)

開啟事務 A 將 id=1 的數據改為「xiaoming4」：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_db set name = 'xiaoming3' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

這裡事務 A 未提交，接着開啟事務B 做第一次查詢：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_db where id = 1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | xiaoming3 |
+----+-----------+
9 rows in set (0.00 sec)

事務B查詢還是原始值。

下面提交事務 A：

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

接着在事務 B 中再查詢一次：

mysql> select * from test_db where id = 1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | xiaoming3 |
+----+-----------+
1 row in set (0.00 sec)

查詢到還是一樣的結果，下面提交事務B ，然後再查詢：

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_db where id = 1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | xiaoming4 |
+----+-----------+
1 row in set (0.00 sec)

提交完之後再查就是「xiaoming4」。

這也意味着在事務B未提交期間，事務A做任何操作對B來說都是盲視的。

串行化讀

串行化讀意味着將所有事務變為順序執行，所以就不存在上述的四種問題，當然這也意味着效率是最低的。

有了隔離級別的概念，那隔離級別又是怎麼實現的呢？我們接下來要講的鎖機制就是實現隔離級別的重要手段。

鎖的類型

從鎖定資源的角度看， MySQL 中的鎖分類：

表級鎖
行級鎖
頁面鎖

表級鎖 的特點是每次都整張表加鎖，加鎖速度快，但是鎖的粒度太大，並發性就低，發生鎖衝突的概率大。

表鎖的種類主要包含兩種：

讀鎖（共享鎖）：同一份數據多個讀操作同時進行不會互相影響，但是讀操作會阻塞寫操作。
寫鎖（排他鎖）：當前寫操作沒有完成之前會阻塞其他讀和寫操作。

行級鎖 的特點是對一行數據加鎖，加鎖的開銷會大但是鎖粒度小發生鎖衝突的概率就低並發度提高了。

行鎖的種類包含：

讀鎖（S 共享鎖）：允許一個事務讀取某一行，其他事務在讀取期間無法修改該行數據但可以讀。
寫鎖（X 排他鎖）：允許當前獲得排它鎖的事務操作數據，其他事務在操作期間無法更改或者讀取。
意向排它鎖（IX）：一個事務給該數據行加排它鎖之前，必須先獲得 IX 鎖。
意向共享鎖（IS）：一個事務給該數據行加共享鎖之前必須先獲得 IS 鎖。

頁面鎖 因為MySQL 數據文件存儲是按照頁去劃分的，所以這個鎖是 MySQL 特有的。開銷和加鎖時間界於表鎖和行鎖之間，鎖定粒度界於表鎖和行鎖之間，並發度一般。

在 InnoDB 引擎中默認使用行級鎖，我們重點就行級鎖的加鎖、解鎖來做一些說明。

行級鎖上鎖分為隱式上鎖和顯式上鎖。

隱式上鎖是默認的上鎖方式，select不會自動上鎖，insert、update、delete 都會自動加排它鎖。在語句執行完畢會釋放。

顯式上鎖即通過手動的方式給 sql 語句加鎖，比如：

共享鎖：

select * from tableName lock in share mode;

排他鎖：

select * from tableName for update;

行級鎖的實現方式

在 InnoDB 中行級鎖的具體實現分為三種類型：

鎖定單個行記錄：Record Lock。
鎖定一個範圍，不包含記錄本身：Gap Lock。
同時鎖住一行數據 + 該數據上下浮動的間隙：next-Key Lock。

接下來我們通過一個示例來測試 InnoDB 中這三種鎖的實現。

先創建一個測試表：

CREATE TABLE `test_db` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'name',
  PRIMARY KEY (`id`),
  KEY `name_idx` (`name`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='測試表';

插入兩條數據：

還記得我們上面說過 MySQL 是自動提交事務，為了測試鎖我們需要關閉自動提交：

set autocommit = 0;

這個設置只在當前連接中生效，記得每開一個連接都要設置一下。

Record Lock 測試

開啟一個事務：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test_db set name = 'xiaoming1' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

查看事務狀態：

mysql> show engine innodb status;
------------
TRANSACTIONS
------------
Trx id counter 25355
Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283540073944880, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 25354, ACTIVE 40 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 5, OS thread handle 12524, query id 757 localhost ::1 root starting
show engine innodb status
--------

事務狀態顯示有一行在被鎖定。

下面我們在當前連接中查詢一下現在的數據庫：

mysql> select * from test_db;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | xiaoming1 |
|  2 | xiaohong  |
+----+-----------+
2 rows in set (0.00 sec)

發現當前數據庫已經被修改了，是事務並沒有提交。別急我們繼續看看。

下面在一個新的連接開啟第二個事務：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> mysql>  update test_db set name = 'xiaoming2' where id = 1;

這時候發現這一條語句卡住了無法執行。

查看事務狀態：

mysql> show engine innodb status;
......

------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `test_db`.`test_db` trx id 2072 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000000817; asc       ;;
 2: len 7; hex 02000001080422; asc       ";;
 3: len 9; hex 7869616f6d696e6732; asc xiaoming2;;

------------------
---TRANSACTION 2071, ACTIVE 50318 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
MySQL thread id 10, OS thread handle 123145423929344, query id 96 localhost 127.0.0.1 root starting
show engine innodb status
Trx read view will not see trx with id >= 2073, sees < 2072

從事務狀態上可以看到對 id = 1 的這一行加了 record lock。

再看這一句：

trx id 2072 lock_mode X locks rec but not gap waiting

X 鎖就是我們上面說的排它鎖，只對當前記錄加鎖，並不對間隙加鎖。

Gap Lock 測試

測試 Gap Lock 我發現如果 where 條件是主鍵的時候，只會有 record lock 不會有gap lock。

所以 gap lock 的條件是 where 條件必須是非唯一鍵。

首先查詢一下當前的數據:

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select * from test_db;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | xiaoming4 |
|  2 | xiaohong  |
|  3 | xiaowei   |
|  4 | xiaowei1  |
|  5 | xiaoli    |
|  6 | xiaoche   |
| 10 | xiaohai   |
| 12 | xiaocee   |
+----+-----------+
8 rows in set (0.00 sec)

開啟事務A：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_db where name ='xiaohai' for update;
+----+---------+
| id | name    |
+----+---------+
| 10 | xiaohai |
+----+---------+
1 row in set (0.00 sec)

這裡我們做的事情是對 name 列做查詢條件，它是非唯一索引可以被間隙鎖命中。現在的 id=10 列 name=xiaohai，如果被間隙鎖命中的話，xiaoc* — xiaoh*中間的字符應該都是不能插入的。所以我們就用這種方式來試試。

開啟事務B：

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert test_db (id, name) values (8, 'xiaodai');

插入「xiaodai」，可以發現「卡住了」，查詢一下事務狀態：

mysql> show engine innodb status;
------------
TRANSACTIONS
------------
......
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 32, OS thread handle 123145425444864, query id 385 localhost 127.0.0.1 root update
insert test_db (id, name) values (8, 'xiaodai')
------- TRX HAS BEEN WAITING 24 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 5 n bits 80 index name_idx of table `test_db`.`test_db` trx id 2133 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 7; hex 7869616f686169; asc xiaohai;;
 1: len 4; hex 8000000a; asc     ;;
......
------------------

這裡的事務日誌說了在插入之前這個索引已經被gap lock 鎖住了，所以我們的測試是有效的。

那麼 gap lock 的邊界是多少呢？這裡我實測是當前記錄往前找到一個邊界和往後找到一個邊界，對於上面的測試數據來說就是：往前到 “xiaoche” ，往後到「xiaohong」，且你再插入一個等於當前鎖定記錄「xiaohai」的值也是可以的，這個就留給大家動手試試。

Gap Lock 解決了什麼問題呢？上面我們說到讀已提交級別有不可重複讀的問題。Gap Lock 就是為了防止在本事務還未提交之前，別的事務在當前事務周邊插入或修改了數據造成讀不一致。

Next-key Lock 測試

Next-key Lock 實際上是 Record Lock 和 gap Lock 的組合。

Next-key Lock 是在下一個索引記錄本身和索引之前的 gap Lock 加上 S 鎖或是 X 鎖 ( 如果是讀就加上 S 鎖，如果是寫就加 X 鎖)。

默認情況下，InnoDB 的事務隔離級別為 RR，系統參數 innodb_locks_unsafe_for_binlog=false。InnoDB 使用 next-key Lock 對索引進行掃描和搜索，這樣就讀取不到幻象行，避免了幻讀的發生。

這就相當於對當前數據和當前數據周圍的數據都做了保護，當前數據不會發生幻讀，當前數據周圍的數據不會出現修改或新增從而導致讀不一致。

但是需要注意的是，上面測試 Gap Lock 也說過，Gap Lock 只對非唯一索引列生效，同樣 Next-key Lock如果也是作用於非唯一索引那麼會自動降級為 Record Lock。

MVCC機制

什麼是 MVCC?

MVCC，Multi-Version Concurrency Control，多版本並發控制。同一份數據臨時保留多版本的一種方式，進而實現並發控制，簡稱一致性非鎖定讀。

上面我們討論過在多個事務的場景下，通過鎖機制可以保證當前事務讀不到未提交的事務。但是加鎖也會帶來壞處，那就是阻塞，只有讀讀之間可以並發，讀寫，寫讀，寫寫都不能並發操作。引入多版本機制就是為了解決這個問題，減少阻塞時間，通過這個機制，只有寫寫是會阻塞，其餘情況都不會阻塞操作。

比如我們還用 RR 隔離級別下的例子來說，事務A寫了一個數據未提交，事務B讀取數據，這時候是讀不到A事務未提交的記錄。B事務只能讀到A事務未提交之前的版本。這裡就使用了版本管理機制，每個連接在某個瞬間看到的是是數據庫在當前的一個快照，每個事務在提交之前對其他的讀者來說是不可見的。

一般來說 MVCC 只在 Read Committed 和 Repeatable Read 兩個隔離級別下工作。Read Uncommitted 總是能讀取到未提交的記錄，不需要版本控制；Serializable 對所有的讀取都對加鎖，單獨靠 MVCC 無法完成。

MVCC 的實現，是通過保存數據在某一個時間點的快照來實現的。因此每一個事務無論執行多長時間看到的數據，都是一樣的。所以 MVCC 實現可重複讀。

MVCC 的實現

隱藏字段

為了實現多版本控制，InnoDB 引擎在每一行數據中都添加了幾個隱藏字段：

DB_TRX_ID：記錄最近一次對本記錄做（insert/upadte）的事務 ID，大小為 6 位元組；
DB_ROLL_PTR：回滾指針，指向回滾段的 undo log，大小為 7 位元組；
DB_ROW_ID：單調遞增的行 ID，大小為 6 位元組，當表沒有主鍵索引或者非空唯一索引的時候 InnoDB 就用這個字段創聚簇索引，這個字段跟MVCC的實現沒有關係。

MVCC 在 InnoDB 的實現依賴 undo log 和 read view。undo log 中記錄的是數據表記錄行的多個版本，也就是事務執行過程中的回滾段,其實就是MVCC 中的一行原始數據的多個版本鏡像數據。read view 主要用來判斷當前版本數據的可見性。

undo log

undo log 上面講解的時候說go會用於 MVCC 機制。因為 undo log 中存儲的是老版本的數據，如果一個事務讀取當前行，但是當前行記錄不可見，那麼可以順着 undo log 鏈表找到滿足其可見性的版本。

版本鏈

每條 undo log 也都有一個 old_trx_id 屬性和一個 old_roll_pointer 屬性（INSERT 操作對應的 undo log 沒有這些屬性，因為該記錄沒有更早的版本）用於記錄上一個 undo log。最終這些 undo log 就連接起來形成了一個鏈表，這個鏈表稱之為版本鏈，版本鏈的頭節點就是當前記錄的最新值。

Read View（讀視圖）

如果一個事務修改了記錄但尚未提交，其他事務是不能讀取記錄的最新版本的。此時就需要判斷版本鏈中的哪個版本是可以被當前事務訪問的，為此 InnoDB 提出了 ReadView 的概念。 Read View 裏面保存了「對本事務不可見的其他活躍事務」，主要是用來做可見性判斷。

Read View 底層定義了一些關鍵字段：

ReadView 字段	描述
trx_ids	在生成 ReadView 時當前系統中活躍的讀寫事務，即Read View初始化時當前未提交的事務列表。所以當進行RR讀的時候，trx_ids中的事務對於本事務是不可見的（除了自身事務，自身事務對於表的修改對於自己當然是可見的）。理解起來就是創建RV時，將當前活躍事務ID記錄下來，後續即使他們提交對於本事務也是不可見的。
low_limit_id	在生成 ReadView 時當前系統中活躍的讀寫事務中最小的事務 ID，事務號 >= low_limit_id 的記錄，對於當前 Read View 都是不可見的
up_limit_id	系統應該給下一個事務分配的 ID 值，事務號 < up_limit_id ，對於當前Read View都是可見的
creator_trx_id	生成該 ReadView 的事務 ID

一旦一個 Read View 被創建，這三個參數將不再發生變化，理解這點很重要，其中 min_trx_id 和 max_trx_id 分別是 trx_Ids 數組的上下界。

記錄行修改的具體流程

首先當前事務對記錄行加排他鎖；
然後把該行數據拷貝到 undo lo g中，作為舊版本；
拷貝完畢後，修改該行的數據，並且修改記錄行最新的修改事務 id ，也就是 DB_TRX_ID 為當前事務 id；
事務提交，提交前用 CAS 機制判斷記錄行當前最新修改的事務 id 是否發生了變化，如果沒變，則提交成功；如果變了，說明存在其他事務修改了這個記錄行，那麼就應該回滾這個事務。也就是當前事務沒有生效。

記錄行查詢時的可見性判斷算法

在 InnoDB 中創建一個新事務後，執行第一個 select 語句的時候，InnoDB 會創建一個快(readView)，快照中會保存系統當前不應該被本事務看到的其他活躍事務 id 列表（即trx_ids）。當用戶在這個事務中要讀取某個記錄行的時候，InnoDB 會將該記錄行的 DB_TRX_ID 與該 ReadView 中的一些變量進行比較，判斷是否滿足可見性條件。

假設當前事務要讀取某一個記錄行，該記錄行的 DB_TRX_ID（即最新修改該行的事務ID）為 trx_id，ReadView 的活躍事務列表 trx_ids 的上下界分別為 min_trx_id 和 max_trx_id。

具體的比較算法如下:

如果 trx_id < up_limit_id, 那麼表明「最新修改該行的事務」在「當前事務」創建快照之前就提交了，所以該記錄行的值對當前事務是可見的。直接標識為可見，返回true；
如果 trx_id >= low_limit_id, 那麼表明「最新修改該行的事務」在「當前事務」創建快照之後才被創建且修改該行的，所以該記錄行的值對當前事務不可見。應該通過回滾指針找到上個記錄行版本，判斷是否可見。循環往複，直到可見；
如果 up_limit_id <= trx_id < low_limit_id, 那就得通過二分查找判斷 trx_id 是否在 trx_ids 列表出現過。
1. 如果出現過，說明是當前read view 中某個活躍的事務提交了，那當然是不可見的，應該通過回滾指針找到上個記錄行版本，判斷是否可見，循環往複，直到可見；
2. 如果沒有出現過，說明這個事務是已經提交了的，表示為可見，返回 true。

需要注意的是，新建事務(當前事務)與正在內存中 commit 的事務不在活躍事務鏈表中。

不同隔離級別下 read view 生成原則：

RC 級別

每個快照讀操作都會生成最新的 read view，所以在 RC 級別中能看到別的事務提交的記錄。

RR 級別

同一個事務中的第一個快照讀才會創建 Read View, 之後的快照讀獲取的都是同一個Read View。

關於MVCC 的總結

上面介紹了 MVCC 在 innoDB 中的實現，我們回顧一下理想中的 MVCC 應該是什麼樣的：

每行數據都有一個版本，每次更新都更新該版本
每個事務只在當前版本上更新，各個事務無干擾
多個事務提交時比較版本號，如果成功則覆蓋原紀錄，否則放棄

MVCC 的理論聽起來和樂觀鎖一致。但是反觀 InnoDB 中的實現，事務修改數據首先藉助排它鎖，事務失敗還藉助到 undo log 來實現回滾。理論上如果一個完整的 MVCC 實現應該藉助版本號就可以，如果使用上了 X 鎖那何必還浪費時間再使用樂觀鎖呢？

事實上理想的 MVCC 可能會引發bug，單純依靠版本控制無法完成一致性非鎖定讀。任何一個複雜的系統在摻雜各種變量的情況總會引發一些旁支問題。

比如，在理想的MVCC 模式下，TX1執行修改 Row1成功，修改Row2失敗，此時需要回滾Row1；

但因為Row1沒有被鎖定，其數據可能又被 TX2 修改，如果此時回滾 Row1的內容，則會破壞 TX2 的修改結果。

MVCC 機制提供了讀的非阻塞能力，對於寫來說如果不用鎖，肯定會出錯。但是對於數據庫系統來說，讀才是大頭，這已經解決了生產力的要求。

總結

以上從數據庫多事務並發可能會產生什麼問題分析，數據庫奠基者總結出事務的四大特性，為了性能和數據準確性的協調總結出不同的隔離級別，為了實現不同的隔離級別分別使用了什麼技術。這些問題環環相扣，從問題出發尋找解決思路，鎖機制，MVCC機制，都是為了解決數據一致性問題同時兼顧讀效率而存在。為了持久性提出了兩階段提交弄出了 redo log，為了實現原子性和 MVCC 又多出了 undo log。所有的實現都是基於特定的場景和需求，站在需求場景下去理解這些概念就會更容易感受到設計者的初衷。

Tags: MYSQL 系列