資料庫多版本並發控制MVCC

2019 年 10 月 11 日
筆記

多版本並發控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存儲引擎實現隔離級別的一種具體方式，用於實現提交讀和可重複讀這兩種隔離級別。而未提交讀隔離級別總是讀取最新的數據行，無需使用 MVCC。可串列化隔離級別需要對所有讀取的行都加鎖，單純使用 MVCC 無法實現。

MVCC提供並發訪問資料庫時，對事務內讀取的到的記憶體做處理，用來避免寫操作堵塞讀操作的並發問題。MVCC可以在大多數情況下代替行級鎖,使用MVCC,能降低其系統開銷。

一、創建表結構

資料庫表創建時，內部的隱藏列ROW_ID（行號）、DB_TRX_ID（事務id）、DB_ROLL_PTR（回滾指針）；行號，模擬數據的存在的地址，事務ID，存放事務ID，回滾指針，上次提交數據事務的ID，方便回滾，類似鏈表的指針，指向上一條數據。

create table mvcc (             -- INNODB 隱藏列         ROW_ID bigint not null auto_increment primary key, -- 行號，模擬指針地址         MY_DB_TRX_ID int not null, -- 事務id         MY_DB_ROLL_PTR bigint, -- 回滾指針         DELETED bit, -- 刪除標識         -- 真實欄位         id bigint, -- 主鍵id        name varchar(32) -- 名稱  ) charset 'utf8', engine 'innodb';

二、初始化數據

insert into mvcc (MY_DB_TRX_ID, MY_DB_ROLL_PTR, DELETED, id, name) values (1, null, null, 1, '用來修改'), (2, null, null, 2, '用來刪除'), (3, null, null, 3, 'test');

行號ID模擬，事務ID，處理該條記錄的事務的ID，該條數據的事務ID為累加，不可縮減。回滾指針為空，是因為該條數據沒有上一次事務，因此回滾指針為空。id、name為用戶能夠看到的數據。

三、模擬查詢數據

舉例，當累計查詢，修改後，事務ID到目前為11；即MY_DB_TRX_ID = 11；

目標：查詢ID = 1的數據

要執行的sql：select * from mvcc where id = 1;

1.實際內部查詢的邏輯，查看比當前事務ID小的最近的一條ID = 1的數據，翻譯成sql為：

select * from mvcc where id = 1 and MY_DB_TRX_ID <= 11 order by MY_DB_TRX_ID desc limit 1;

查詢結果：

四、事務ID = 12修改數據

當前事務ID = 12 ，修改ID = 1的數據

要執行的sql: update mvcc set name = '修改後的數據' where id = 1;

1.查詢到滿足條件的行

select ROW_ID from mvcc where id = 1 and MY_DB_TRX_ID <= 12 order by MY_DB_TRX_ID desc limit 1

2.複製目標數據

insert into mvcc (MY_DB_TRX_ID, MY_DB_ROLL_PTR, DELETED, id, name) select MY_DB_TRX_ID, MY_DB_ROLL_PTR, DELETED, id, name from mvcc where ROW_ID = 1;

執行結果：

3.修改數據，並將DB_TRX_ID改為當前事務id，將當前行DB_ROLL_PTR指向複製的行

update mvcc set name = '修改後的數據', MY_DB_TRX_ID = 12, MY_DB_ROLL_PTR = 4 where ROW_ID = 1;

執行結果：

4.當事務12查詢數據時

執行的思路，翻譯為sql為：

select * from mvcc where id = 1 and MY_DB_TRX_ID <= 12 order by MY_DB_TRX_ID desc limit 1;

查到的數據是正常的更新後的數據。id = 1對應的內容是「修改後的數據」；

5.當事務11查詢時

select * from mvcc where id = 1 and MY_DB_TRX_ID <= 11 order by MY_DB_TRX_ID desc limit 1;

查詢到的是原本的數據id = 1對應的內容為：「用來修改」。保證了可重複讀。

五、事務12刪除數據

要執行的sql:delete from mvcc where id = 2;

1.1、查詢到滿足條件的行並複製一份

select ROW_ID from mvcc where id = 2 and MY_DB_TRX_ID <= 12 and DELETED is null order by MY_DB_TRX_ID desc limit 1;  insert into mvcc (MY_DB_TRX_ID, MY_DB_ROLL_PTR, DELETED, id, name)  select MY_DB_TRX_ID, MY_DB_ROLL_PTR, DELETED, id, name  from mvcc where ROW_ID = 2; -- 5

執行結果：