一文讀懂MySQL的事務隔離級別及MVCC機制

行無際發表於2020-10-19
MySqlMVC

回顧前文:
一文學會MySQL的explain工具

一文讀懂MySQL的索引結構及查詢優化

(同時再次強調，這幾篇關於MySQL的探究都是基於5.7版本，相關總結與結論不一定適用於其他版本)

就軟體開發而言，既要保證資料讀寫的效率，還要保證併發讀寫資料的可靠性、正確性。因此，除了要對MySQL的索引結構及查詢優化有所瞭解外，還需要對MySQL的事務隔離級別及MVCC機制有所認知。

MySQL官方文件中的詞彙表(https://dev.mysql.com/doc/refman/5.7/en/glossary.html)有助於我們對相關概念、理論的理解。下文中我會從概念表中摘錄部分原文描述，以加深對原理機制的理解。

事務隔離級別

事務是什麼

Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.

事務是由一組SQL語句組成的原子操作單元，其對資料的變更，要麼全都執行成功(Committed)，要麼全都不執行(Rollback)。

事務的示意圖

Database transactions, as implemented by InnoDB, have properties that are collectively known by the acronym ACID, for atomicity, consistency, isolation, and durability.

InnoDB實現的資料庫事務具有常說的ACID屬性，即原子性(atomicity)，一致性(consistency)、隔離性(isolation)和永續性(durability)。

  • 原子性：事務被視為不可分割的最小單元，所有操作要麼全部執行成功，要麼失敗回滾(即還原到事務開始前的狀態，就像這個事務從來沒有執行過一樣)
  • 一致性：在成功提交或失敗回滾之後以及正在進行的事務期間，資料庫始終保持一致的狀態。如果正在多個表之間更新相關資料，那麼查詢將看到所有舊值或所有新值，而不會一部分是新值，一部分是舊值
  • 隔離性：事務處理過程中的中間狀態應該對外部不可見，換句話說，事務在進行過程中是隔離的，事務之間不能互相干擾，不能訪問到彼此未提交的資料。這種隔離可通過鎖機制實現。有經驗的使用者可以根據實際的業務場景，通過調整事務隔離級別，以提高併發能力
  • 永續性：一旦事務提交，其所做的修改將會永遠儲存到資料庫中。即使系統發生故障，事務執行的結果也不能丟失

In InnoDB, all user activity occurs inside a transaction. If autocommit mode is enabled, each SQL statement forms a single transaction on its own. By default, MySQL starts the session for each new connection with autocommit enabled, so MySQL does a commit after each SQL statement if that statement did not return an error. If a statement returns an error, the commit or rollback behavior depends on the error

MySQL預設採用自動提交(autocommit)模式。也就是說，如果不顯式使用START TRANSACTION或BEGIN語句來開啟一個事務，那麼每個SQL語句都會被當做一個事務自動提交。

A session that has autocommit enabled can perform a multiple-statement transaction by starting it with an explicit START TRANSACTION or BEGIN statement and ending it with a COMMIT or ROLLBACK statement.

多個SQL語句開啟一個事務也很簡單，以START TRANSACTION或者BEGIN語句開頭，以COMMIT或ROLLBACK語句結尾。

If autocommit mode is disabled within a session with SET autocommit = 0, the session always has a transaction open. A COMMIT or ROLLBACK statement ends the current transaction and a new one starts.

使用SET autocommit = 0可手動關閉當前session自動提交模式。

併發事務的問題

引出事務隔離級別

相關文件：https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

Isolation is the I in the acronym ACID; the isolation level is the setting that fine-tunes the balance between performance and reliability, consistency, and reproducibility of results when multiple transactions are making changes and performing queries at the same time.

也就是說當多個併發請求訪問MySQL，其中有對資料的增刪改請求時，考慮到併發性，又為了避免髒讀、不可重複讀、幻讀等問題，就需要對事務之間的讀寫進行隔離，至於隔離到啥程度需要看具體的業務場景，這時就要引出事務的隔離級別了。

InnoDB offers all four transaction isolation levels described by the SQL:1992 standard: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. The default isolation level for InnoDB is REPEATABLE READ.

InnoDB儲存引擎實現了SQL標準中描述的4個事務隔離級別：讀未提交(READ UNCOMMITTED)、讀已提交(READ COMMITTED)、可重複讀(REPEATABLE READ)、可序列化(SERIALIZABLE)。InnoDB預設隔離級別是可重複讀(REPEATABLE READ)。

設定事務隔離級別

既然可以調整隔離級別，那麼如何設定事務隔離級別呢？詳情見官方文件：https://dev.mysql.com/doc/refman/5.7/en/set-transaction.html

MySQL5.7.18版本演示如下：

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.18    |
+-----------+
1 row in set (0.00 sec)

mysql> set global transaction isolation level REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> set session transaction isolation level READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.tx_isolation, @@session.tx_isolation, @@tx_isolation;
+-----------------------+------------------------+----------------+
| @@global.tx_isolation | @@session.tx_isolation | @@tx_isolation |
+-----------------------+------------------------+----------------+
| REPEATABLE-READ       | READ-COMMITTED         | READ-COMMITTED |
+-----------------------+------------------------+----------------+
1 row in set (0.00 sec)

MySQL8.0.21版本演示如下：

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.21    |
+-----------+
1 row in set (0.01 sec)

mysql> set global transaction isolation level REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> set session transaction isolation level READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.transaction_isolation, @@session.transaction_isolation, @@transaction_isolation;
+--------------------------------+---------------------------------+-------------------------+
| @@global.transaction_isolation | @@session.transaction_isolation | @@transaction_isolation |
+--------------------------------+---------------------------------+-------------------------+
| REPEATABLE-READ                | READ-COMMITTED                  | READ-COMMITTED          |
+--------------------------------+---------------------------------+-------------------------+
1 row in set (0.00 sec)

注意：

transaction_isolation was added in MySQL 5.7.20 as a synonym for tx_isolation, which is now deprecated and is removed in MySQL 8.0. Applications should be adjusted to use transaction_isolation in preference to tx_isolation.

Prior to MySQL 5.7.20, use tx_isolation and tx_read_only rather than transaction_isolation and transaction_read_only.

如果使用系統變數(system variables)來檢視或者設定事務隔離級別，需要注意MySQL的版本。在MySQL5.7.20之前，應使用tx_isolation；在MySQL5.7.20之後，應使用transaction_isolation。

You can set transaction characteristics globally, for the current session, or for the next transaction only.

事務的隔離級別範圍(Transaction Characteristic Scope)可以精確到全域性(global)、當前會話(session)、甚至是僅針對下一個事務生效(the next transaction only)。

  • 含global關鍵詞時，事務隔離級別的設定應用於所有後續session，已存在的session不受影響
  • 含session關鍵詞時，事務隔離級別的設定應用於在當前session中執行的所有後續事務，不會影響當前正在進行的事務
  • 不含global以及session關鍵詞時，事務隔離級別的設定僅應用於在當前session中執行的下一個事務

資料準備

為了演示髒讀、不可重複讀、幻讀等問題，準備了一些初始化資料如下：

-- ----------------------------
--  create database
-- ----------------------------
create database `transaction_test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- switch database
use `transaction_test`;

-- ----------------------------
--  table structure for `tb_book`
-- ----------------------------
CREATE TABLE `tb_book` (
  `book_id` int(11) NOT NULL,
  `book_name` varchar(64) DEFAULT NULL,
  `author` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`book_id`),
  UNIQUE KEY `uk_book_name` (`book_name`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

BEGIN;
INSERT INTO `tb_book`(`book_id`, `book_name`, `author`) VALUES (1, '多情劍客無情劍', '古龍');
INSERT INTO `tb_book`(`book_id`, `book_name`, `author`) VALUES (2, '笑傲江湖', '金庸');
INSERT INTO `tb_book`(`book_id`, `book_name`, `author`) VALUES (3, '倚天屠龍記', '金庸');
INSERT INTO `tb_book`(`book_id`, `book_name`, `author`) VALUES (4, '射鵰英雄傳', '金庸');
INSERT INTO `tb_book`(`book_id`, `book_name`, `author`) VALUES (5, '絕代雙驕', '古龍');
COMMIT;

髒讀(read uncommitted)

事務A讀到了事務B已經修改但尚未提交的資料

操作：

  1. session A事務隔離級別設定為read uncommitted並開啟事務，首次查詢book_id為1的記錄；
  2. 然後session B開啟事務，並修改book_id為1的記錄，不提交事務，在session A中再次查詢book_id為1的記錄；
  3. 最後讓session B中的事務回滾，再在session A中查詢book_id為1的記錄。

session A：

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from tb_book where book_id = 1;
+---------+-----------------------+--------+
| book_id | book_name             | author |
+---------+-----------------------+--------+
|       1 | 多情劍客無情劍        | 古龍   |
+---------+-----------------------+--------+
1 row in set (0.00 sec)

mysql> select * from tb_book where book_id = 1;
+---------+-----------------------+--------+
| book_id | book_name             | author |
+---------+-----------------------+--------+
|       1 | 多情刀客無情刀        | 古龍   |
+---------+-----------------------+--------+
1 row in set (0.00 sec)

mysql> select * from tb_book where book_id = 1;
+---------+-----------------------+--------+
| book_id | book_name             | author |
+---------+-----------------------+--------+
|       1 | 多情劍客無情劍        | 古龍   |
+---------+-----------------------+--------+
1 row in set (0.00 sec)

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

session B：

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

mysql> update tb_book set book_name = '多情刀客無情刀' where book_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

結果：事務A讀到了事務B還沒提交的中間狀態，即產生了髒讀。

不可重複讀(read committed)

事務A讀到了事務B已經提交的修改資料

操作：

  1. session A事務隔離級別設定為read committed並開啟事務，首次查詢book_id為1的記錄；
  2. 然後session B開啟事務，並修改book_id為1的記錄，不提交事務，在session A中再次查詢book_id為1的記錄；
  3. 最後提交session B中的事務，再在session A中檢視book_id為1的記錄。

session A：

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.01 sec)

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

mysql> select * from tb_book where book_id = 1;
+---------+-----------------------+--------+
| book_id | book_name             | author |
+---------+-----------------------+--------+
|       1 | 多情劍客無情劍        | 古龍   |
+---------+-----------------------+--------+
1 row in set (0.00 sec)

mysql> select * from tb_book where book_id = 1;
+---------+-----------------------+--------+
| book_id | book_name             | author |
+---------+-----------------------+--------+
|       1 | 多情劍客無情劍        | 古龍   |
+---------+-----------------------+--------+
1 row in set (0.00 sec)

mysql> select * from tb_book where book_id = 1;
+---------+-----------------------+--------+
| book_id | book_name             | author |
+---------+-----------------------+--------+
|       1 | 多情刀客無情刀        | 古龍   |
+---------+-----------------------+--------+
1 row in set (0.00 sec)

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

session B：

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

mysql> update tb_book set book_name = '多情刀客無情刀' where book_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

結果：事務B沒有提交事務時，事務A不會讀到事務B修改的中間狀態，即read committed解決了上面所說的髒讀問題，但是當事務B中的事務提交後，事務A讀到了修改後的記錄，而對於事務A來說，僅僅讀了兩次，卻讀到了兩個不同的結果，違背了事務之間的隔離性，所以說該事務隔離級別下產生了不可重複讀的問題。

幻讀(repeatable read)

事務A讀到了事務B提交的新增資料

操作：

  1. session A事務隔離級別設定為repeatable read並開啟事務，並查詢book列表
  2. session B開啟事務，先修改book_id為5的記錄，再插入一條新的資料，提交事務，在session A中再次查詢book列表
  3. 在session A中更新session B中新插入的那條資料，再查詢book列表

session A：

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from tb_book;
+---------+-----------------------+--------+
| book_id | book_name             | author |
+---------+-----------------------+--------+
|       1 | 多情刀客無情刀        | 古龍   |
|       2 | 笑傲江湖              | 金庸   |
|       3 | 倚天屠龍記            | 金庸   |
|       4 | 射鵰英雄傳            | 金庸   |
|       5 | 絕代雙驕              | 古龍   |
+---------+-----------------------+--------+
5 rows in set (0.00 sec)

mysql> select * from tb_book;
+---------+-----------------------+--------+
| book_id | book_name             | author |
+---------+-----------------------+--------+
|       1 | 多情刀客無情刀        | 古龍   |
|       2 | 笑傲江湖              | 金庸   |
|       3 | 倚天屠龍記            | 金庸   |
|       4 | 射鵰英雄傳            | 金庸   |
|       5 | 絕代雙驕              | 古龍   |
+---------+-----------------------+--------+
5 rows in set (0.00 sec)

mysql> update tb_book set book_name = '圓月彎劍' where book_id = 6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tb_book;
+---------+-----------------------+--------+
| book_id | book_name             | author |
+---------+-----------------------+--------+
|       1 | 多情刀客無情刀        | 古龍   |
|       2 | 笑傲江湖              | 金庸   |
|       3 | 倚天屠龍記            | 金庸   |
|       4 | 射鵰英雄傳            | 金庸   |
|       5 | 絕代雙驕              | 古龍   |
|       6 | 圓月彎劍              | 古龍   |
+---------+-----------------------+--------+
6 rows in set (0.00 sec)

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

session B：

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

mysql> update tb_book set book_name = '絕代雙雄' where book_id = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> insert into tb_book values (6, '圓月彎刀', '古龍');
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from tb_book;
+---------+-----------------------+--------+
| book_id | book_name             | author |
+---------+-----------------------+--------+
|       1 | 多情刀客無情刀        | 古龍   |
|       2 | 笑傲江湖              | 金庸   |
|       3 | 倚天屠龍記            | 金庸   |
|       4 | 射鵰英雄傳            | 金庸   |
|       5 | 絕代雙雄              | 古龍   |
|       6 | 圓月彎刀              | 古龍   |
+---------+-----------------------+--------+
6 rows in set (0.00 sec)

結果：事務B已提交的修改記錄(即絕代雙驕修改為絕代雙雄)在事務A中是不可見的，說明該事務隔離級別下解決了上面不可重複讀的問題，但魔幻的是一開始事務A中雖然讀不到事務B中的新增記錄，卻可以更新這條新增記錄，執行更新(update)後，在事務A中居然可見該新增記錄了，這便產生了所謂的幻讀問題。

為什麼會出現這樣莫名其妙的結果？ 別急，後文會慢慢揭開這個神祕的面紗。先看如何解決幻讀問題。

序列化(serializable)

serializable事務隔離級別可以避免幻讀問題，但會極大的降低資料庫的併發能力。

SERIALIZABLE: the isolation level that uses the most conservative locking strategy, to prevent any other transactions from inserting or changing data that was read by this transaction, until it is finished.

操作：

  1. session A事務隔離級別設定為serializable並開啟事務，並查詢book列表，不提交事務；
  2. 然後session B中分別執行insert、delete、update操作

session A：

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from tb_book;
+---------+-----------------------+--------+
| book_id | book_name             | author |
+---------+-----------------------+--------+
|       1 | 多情刀客無情刀        | 古龍   |
|       2 | 笑傲江湖              | 金庸   |
|       3 | 倚天屠龍記            | 金庸   |
|       4 | 射鵰英雄傳            | 金庸   |
|       5 | 絕代雙雄              | 古龍   |
|       6 | 圓月彎刀              | 古龍   |
+---------+-----------------------+--------+
6 rows in set (0.00 sec)

session B：

mysql> insert into tb_book values (7, '神鵰俠侶', '金庸');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> delete from tb_book where book_id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> update tb_book set book_name = '絕代雙驕' where book_id = 5;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

結果：只要session A中的事務一直不提交，session B中嘗試更改資料(insert、delete、update)的事務都會被阻塞至超時(timeout)。顯然，該事務隔離級別下能有效解決上面幻讀、不可重複讀、髒讀等問題。

注意：除非是一些特殊的應用場景需要serializable事務隔離級別，否則很少會使用該隔離級別，因為併發性極低。

事務隔離級別小結

事務隔離級別 髒讀 不可重複讀 幻讀
read uncommitted 可能 可能 可能
read committed 不可能 可能 可能
repeatable read 不可能 不可能 可能
serializable 不可能 不可能 不可能

MVCC機制

上面在演示幻讀問題時，出現的結果讓人捉摸不透。原來InnoDB儲存引擎的預設事務隔離級別可重複讀(repeatable read)，是通過 "行級鎖+MVCC"一起實現的。這就不得不去了解MVCC機制了。

相關文件：https://dev.mysql.com/doc/refman/5.7/en/innodb-multi-versioning.html

參考：
《MySQL中MVCC的正確開啟方式（原始碼佐證）》 https://blog.csdn.net/Waves___/article/details/105295060

《InnoDB事務分析-MVCC》http://www.leviathan.vip/2019/03/20/InnoDB的事務分析-MVCC/

《Innodb中的事務隔離級別和鎖的關係》 https://tech.meituan.com/2014/08/20/innodb-lock.html

MVCC概念

多版本併發控制(multiversion concurrency control，即MVCC): 指的是一種提高併發的技術。最早期的資料庫系統，只有讀讀之間可以併發，讀寫、寫讀、寫寫都要阻塞。引入多版本之後，只有寫寫之間相互阻塞，其他三種操作都可以並行，這樣大幅度提高了InnoDB的併發效能。在內部實現中，InnoDB通過undo log儲存每條資料的多個版本，並且能夠提供資料歷史版本給使用者讀，每個事務讀到的資料版本可能是不一樣的。在同一個事務中，使用者只能看到該事務建立快照之前已經提交的修改和該事務本身做的修改。

簡單來說，MVCC表達的是維持一個資料的多個版本，使得讀寫操作沒有衝突這麼一個思想。

MVCC在read committed和repeatable read兩個事務隔離級別下工作。

隱藏欄位

Internally, InnoDB adds three fields to each row stored in the database. A 6-byte DB_TRX_ID field indicates the transaction identifier for the last transaction that inserted or updated the row. Also, a deletion is treated internally as an update where a special bit in the row is set to mark it as deleted. Each row also contains a 7-byte DB_ROLL_PTR field called the roll pointer. The roll pointer points to an undo log record written to the rollback segment. If the row was updated, the undo log record contains the information necessary to rebuild the content of the row before it was updated. A 6-byte DB_ROW_ID field contains a row ID that increases monotonically as new rows are inserted. If InnoDB generates a clustered index automatically, the index contains row ID values. Otherwise, the DB_ROW_ID column does not appear in any index.

InnoDB儲存引擎在每行資料的後面新增了三個隱藏欄位，如下圖所示：

表中某行資料示意圖

  1. DB_TRX_ID(6位元組)：表示最近一次對本記錄行做修改(insert或update)的事務ID。至於delete操作，InnoDB認為是一個update操作，不過會更新一個另外的刪除位，將行標識為deleted。並非真正刪除。

  2. DB_ROLL_PTR(7位元組)：回滾指標，指向當前記錄行的undo log資訊。

  3. DB_ROW_ID(6位元組)：隨著新行插入而單調遞增的行ID。當表沒有主鍵或唯一非空索引時，InnoDB就會使用這個行ID自動產生聚集索引。前文《一文讀懂MySQL的索引結構及查詢優化》中也有所提及。這個DB_ROW_ID跟MVCC關係不大。

undo log

undo log中儲存的是老版本資料，當一個事務需要讀取記錄行時，如果當前記錄行不可見，可以順著undo log連結串列找到滿足其可見性條件的記錄行版本。

對資料的變更操作主要包括insert/update/delete，在InnoDB中，undo log分為如下兩類：

  • insert undo log: 事務對insert新記錄時產生的undo log, 只在事務回滾時需要, 並且在事務提交後就可以立即丟棄。
  • update undo log: 事務對記錄進行delete和update操作時產生的undo log，不僅在事務回滾時需要，快照讀也需要，只有當資料庫所使用的快照中不涉及該日誌記錄，對應的回滾日誌才會被purge執行緒刪除。

Purge執行緒：為了實現InnoDB的MVCC機制，更新或者刪除操作都只是設定一下舊記錄的deleted_bit，並不真正將舊記錄刪除。為了節省磁碟空間，InnoDB有專門的purge執行緒來清理deleted_bit為true的記錄。purge執行緒自己也維護了一個read view，如果某個記錄的deleted_bit為true，並且DB_TRX_ID相對於purge執行緒的read view可見，那麼這條記錄一定是可以被安全清除的。

不同事務或者相同事務的對同一記錄行的修改形成的undo log如下圖所示：

undo log的示意圖

可見鏈首就是最新的記錄，鏈尾就是最早的舊記錄。

Read View結構

Read View(讀檢視)提供了某一時刻事務系統的快照，主要是用來做可見性判斷的, 裡面儲存了"對本事務不可見的其他活躍事務"。

MySQL5.7原始碼中對Read View定義如下(詳情見https://github.com/mysql/mysql-server/blob/5.7/storage/innobase/include/read0types.h#L306)：

class ReadView {
	private:
		/** The read should not see any transaction with trx id >= this
		value. In other words, this is the "high water mark". */
		trx_id_t	m_low_limit_id;

		/** The read should see all trx ids which are strictly
		smaller (<) than this value.  In other words, this is the
		low water mark". */
		trx_id_t	m_up_limit_id;

		/** trx id of creating transaction, set to TRX_ID_MAX for free
		views. */
		trx_id_t	m_creator_trx_id;

		/** Set of RW transactions that was active when this snapshot
		was taken */
		ids_t		m_ids;

		/** The view does not need to see the undo logs for transactions
		whose transaction number is strictly smaller (<) than this value:
		they can be removed in purge if not needed by other views */
		trx_id_t	m_low_limit_no;

		/** AC-NL-RO transaction view that has been "closed". */
		bool		m_closed;

		typedef UT_LIST_NODE_T(ReadView) node_t;

		/** List of read views in trx_sys */
		byte		pad1[64 - sizeof(node_t)];
		node_t		m_view_list;
};

重點解釋下面幾個變數(建議仔細看上面的原始碼註釋，以下僅為個人理解，有理解不到位的地方歡迎指出(●´ω｀●))：

(1) m_ids: Read View建立時其他未提交的活躍事務ID列表。具體說來就是建立Read View時，將當前未提交事務ID記錄下來，後續即使它們修改了記錄行的值，對於當前事務也是不可見的。注意：該事務ID列表不包括當前事務自己和已提交的事務。

(2) m_low_limit_id：某行資料的DB_TRX_ID >= m_low_limit_id的任何版本對該查詢不可見。那麼這個值是怎麼確定的呢？其實就是讀的時刻出現過的最大的事務ID+1，即下一個將被分配的事務ID。見https://github.com/mysql/mysql-server/blob/5.7/storage/innobase/read/read0read.cc#L459

/**
Opens a read view where exactly the transactions serialized before this
point in time are seen in the view.
@param id		Creator transaction id */

void
ReadView::prepare(trx_id_t id)
{
	m_creator_trx_id = id;

	m_low_limit_no = m_low_limit_id = trx_sys->max_trx_id;
}

max_trx_id見https://github.com/mysql/mysql-server/blob/5.7/storage/innobase/include/trx0sys.h#L576中的描述，翻譯過來就是“還未分配的最小事務ID”，也就是下一個將被分配的事務ID。（注意，m_low_limit_id並不是活躍事務列表中最大的事務ID）

struct trx_sys_t {
/*!< The smallest number not yet
					assigned as a transaction id or
					transaction number. This is declared
					volatile because it can be accessed
					without holding any mutex during
					AC-NL-RO view creation. */
	volatile trx_id_t max_trx_id;
}

(3) m_up_limit_id：某行資料的DB_TRX_ID < m_up_limit_id的所有版本對該查詢可見。同樣這個值又是如何確定的呢？m_up_limit_id是活躍事務列表m_ids中最小的事務ID，如果trx_ids為空，則m_up_limit_id為m_low_limit_id。程式碼見https://github.com/mysql/mysql-server/blob/5.7/storage/innobase/read/read0read.cc#L485

void
ReadView::complete()
{
	/* The first active transaction has the smallest id. */
	m_up_limit_id = !m_ids.empty() ? m_ids.front() : m_low_limit_id;

	ut_ad(m_up_limit_id <= m_low_limit_id);

	m_closed = false;
}

這樣就有下面的可見性比較演算法了。程式碼見https://github.com/mysql/mysql-server/blob/5.7/storage/innobase/include/read0types.h#L169

/** Check whether the changes by id are visible.
	@param[in]	id	transaction id to check against the view
	@param[in]	name	table name
	@return whether the view sees the modifications of id. */
bool changes_visible(
	trx_id_t		id,
	const table_name_t&	name) const
	MY_ATTRIBUTE((warn_unused_result))
{
	ut_ad(id > 0);


	/* 假如 trx_id 小於 Read view 限制的最小活躍事務ID m_up_limit_id 或者等於正在建立的事務ID m_creator_trx_id
     * 即滿足事務的可見性.
     */
	if (id < m_up_limit_id || id == m_creator_trx_id) {
		return(true);
	}

	/* 檢查 trx_id 是否有效. */
	check_trx_id_sanity(id, name);

	if (id >= m_low_limit_id) {
		/* 假如 trx_id 大於等於m_low_limit_id, 即不可見. */
		return(false);

	} else if (m_ids.empty()) {
		/* 假如目前不存在活躍的事務，即可見. */
		return(true);
	}

	const ids_t::value_type*	p = m_ids.data();

	/* 利用二分查詢搜尋活躍事務列表
	 * 當 trx_id 在 m_up_limit_id 和 m_low_limit_id 之間
   * 如果 id 在 m_ids 陣列中, 表明 ReadView 建立時候，事務處於活躍狀態，因此記錄不可見.
   */
	return (!std::binary_search(p, p + m_ids.size(), id));
}

事務可見性比較演算法圖示

完整梳理一下整個過程。

在InnoDB中，建立一個新事務後，執行第一個select語句的時候，InnoDB會建立一個快照（read view），快照中會儲存系統當前不應該被本事務看到的其他活躍事務id列表（即m_ids）。當使用者在這個事務中要讀取某個記錄行的時候，InnoDB會將該記錄行的DB_TRX_ID與該Read View中的一些變數進行比較，判斷是否滿足可見性條件。

假設當前事務要讀取某一個記錄行，該記錄行的DB_TRX_ID（即最新修改該行的事務ID）為trx_id，Read View的活躍事務列表m_ids中最早的事務ID為m_up_limit_id，將在生成這個Read Vew時系統出現過的最大的事務ID+1記為m_low_limit_id（即還未分配的事務ID）。

具體的比較演算法如下:

  1. 如果trx_id < m_up_limit_id,那麼表明“最新修改該行的事務”在“當前事務”建立快照之前就提交了，所以該記錄行的值對當前事務是可見的。跳到步驟5。

  2. 如果trx_id >= m_low_limit_id, 那麼表明“最新修改該行的事務”在“當前事務”建立快照之後才修改該行，所以該記錄行的值對當前事務不可見。跳到步驟4。

  3. 如果m_up_limit_id <= trx_id < m_low_limit_id, 表明“最新修改該行的事務”在“當前事務”建立快照的時候可能處於“活動狀態”或者“已提交狀態”；所以就要對活躍事務列表trx_ids進行查詢（原始碼中是用的二分查詢，因為是有序的）

(1) 如果在活躍事務列表m_ids中能找到id為trx_id的事務，表明①在“當前事務”建立快照前，“該記錄行的值”被“id為trx_id的事務”修改了，但沒有提交；或者②在“當前事務”建立快照後，“該記錄行的值”被“id為trx_id的事務”修改了（不管有無提交）；這些情況下，這個記錄行的值對當前事務都是不可見的，跳到步驟4；

(2) 在活躍事務列表中找不到，則表明“id為trx_id的事務”在修改“該記錄行的值”後，在“當前事務”建立快照前就已經提交了，所以記錄行對當前事務可見，跳到步驟5。

  1. 在該記錄行的DB_ROLL_PTR指標所指向的undo log回滾段中，取出最新的的舊事務號DB_TRX_ID, 將它賦給trx_id，然後跳到步驟1重新開始判斷。

  2. 將該可見行的值返回。

read committed與repeatable read的區別

有了上面的知識鋪墊後，就可以從本質上區別read committed與repeatable read這兩種事務隔離級別了。

With REPEATABLE READ isolation level, the snapshot is based on the time when the first read operation is performed. With READ COMMITTED isolation level, the snapshot is reset to the time of each consistent read operation.

在InnoDB中的repeatable read級別, 事務begin之後，執行第一條select（讀操作）時, 會建立一個快照(read view)，將當前系統中活躍的其他事務記錄起來；並且在此事務中之後的其他select操作都是使用的這個read view物件，不會重新建立，直到事務結束。

在InnoDB中的read committed級別, 事務begin之後，執行每條select（讀操作）語句時，快照會被重置，即會基於當前select重新建立一個快照(read view)，所以顯然該事務隔離級別下會讀到其他事務已經提交的修改資料。

那麼，現在能解釋上面演示幻讀問題時，出現的詭異結果嗎？我的理解是，因為是在repeatable read隔離級別下，肯定還是快照讀，即第一次select後建立的read view物件還是不變的，但是在當前事務中update一條記錄時，會把當前事務ID設定到更新後的記錄的隱藏欄位DB_TRX_ID上，即id == m_creator_trx_id顯然成立，於是該條記錄就可見了，再次執行select操作時就多出這條記錄了。

if (id < m_up_limit_id || id == m_creator_trx_id) {
  return(true);
 }

另外，有了這樣的基本認知後，如果你在MySQL事務隔離相關問題遇到一些其他看似很神奇的現象，也可以試試能不能解釋得通。

總結

通過學習MySQL事務隔離級別及MVCC原理機制，有助於加深對MySQL的理解與掌握，更為重要的是，如果讓你編寫一個併發讀寫的儲存程式，MVCC的設計與實現或許能給你一些啟發。

相關文章