一文說清 InnoDB 的事務機制

rickiyang發表於2020-09-11

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

隔壁小王從美團上找到了一家水餃店，準備中午吃水餃。下單成功，支付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 存在的主要意圖：

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

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

更新--插入--選擇--插入--

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

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

  • 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日誌有兩個最重要的使用場景：

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

binlog 日誌包括兩類檔案：

  1. 二進位制日誌索引檔案（檔名字尾為 .index）用於記錄所有的二進位制檔案。
  2. 二進位制日誌檔案（檔名字尾為 .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)

一個更新事務的整體流程

2

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

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

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

兩階段提交

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

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

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

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

    但是如果需要用這個 binlog 來恢復臨時庫的話，由於這個語句的 binlog 丟失，恢復出來的值就與原庫值不同。

  2. 先寫 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 全都支援。這四種隔離級別分別是：

  1. 讀未提交（READ UNCOMMITTED）
  2. 讀提交 （READ COMMITTED）
  3. 可重複讀 （REPEATABLE READ）
  4. 序列化 （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='測試表';

插入兩條資料：

3

還記得我們上面說過 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 陣列的上下界。

記錄行修改的具體流程

  1. 首先當前事務對記錄行加排他鎖；
  2. 然後把該行資料拷貝到 undo lo g中，作為舊版本；
  3. 拷貝完畢後，修改該行的資料，並且修改記錄行最新的修改事務 id ，也就是 DB_TRX_ID 為當前事務 id；
  4. 事務提交，提交前用 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。

具體的比較演算法如下:

  1. 如果 trx_id < up_limit_id, 那麼表明 “最新修改該行的事務” 在 “當前事務” 建立快照之前就提交了，所以該記錄行的值對當前事務是可見的。直接標識為可見，返回true；
  2. 如果 trx_id >= low_limit_id, 那麼表明 “最新修改該行的事務” 在 “當前事務” 建立快照之後才被建立且修改該行的，所以該記錄行的值對當前事務不可見。應該通過回滾指標找到上個記錄行版本，判斷是否可見。迴圈往復，直到可見；
  3. 如果 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。所有的實現都是基於特定的場景和需求，站在需求場景下去理解這些概念就會更容易感受到設計者的初衷。

相關文章