MySQL事務學習筆記(二) 相識篇

北冥有隻魚發表於2022-03-13
MySql筆記
天色將晚， 在我看著你的眼裡色彩斑斕 《娛樂天空》

序

在《MySQL事務學習筆記(一) 初遇篇》我們已經交代瞭如何開啟事務、提交、回滾。但是還有一個小尾巴被遺漏了，就是如何設定事務的隔離級別。本篇我們就來介紹MySQL中是如何設定隔離級別ySQL中是如何實現事務的ACID以及隔離級別。寫作這篇文章的時候，我也在思考如何組織這些內容，是再組織一下自己看的資料上的內容，還是筆記式的，羅列一下知識點。坦率的說我不是很喜歡羅列知識點這種形式的，感覺沒有一條線組織起來，我個人比較喜歡的是像是樹一般的知識組織結構，有一條主幹。所以本篇在介紹MySQL是實現事務實現的時候，會先從巨集觀上介紹其組織，部分知識點不會太詳細，這樣的方式可以讓我們先把握其主幹，不會迷失在細節中。

設定事務隔離級別

select @@tx_isolation;

事務的隔離級別

我的MySQL預設隔離級別為可重複讀，SQL事務的隔離級別:

  • 未提交讀
  • 已提交讀
  • 可重複讀
  • 可序列化。

MySQL支援在執行時和啟動時設定隔離級別:

  • 啟動時設定隔離級別:

    windows下的配置檔案取 my.ini

    Linux下的配置檔案取 my.cnf

    在配置檔案中新增: transaction-isolation = 隔離級別

    隔離級別的候選值: READ COMMITTED， REPEATABLE READ， READ UNCOMMITTED，SERIALIZABLE

  • 執行時設定隔離級別

​ SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;

​ LEVEL的候選值: READ-COMMITTED， REPEATABLE READ， READ UNCOMMITTED，SERIALIZABLE

​ GLOBAL的關鍵字在全域性範圍內影響，在執行完下面語句之後:

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

後面所有的會話的隔離級別都會變為可序列化；

讀已提交

隔離級別變為可序列化

​ 而SESSION關鍵字則是隻在會話範圍內影響，如果事務還未提交則只對後面的事務有效。

​ 如果GLOBAL和SESSION都沒有，則只對當前會話中下一個即將開啟的事務有效，下一個事務執行完畢，後序事務將恢復到之前的隔離級別。該語句不能在已經開啟的事務中執行，會報錯。

事務的隔離級別報錯

​ 下面我們就來演示事務在不同的隔離級別會出現的問題:

  • 事務的隔離級別為未提交讀:
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

​ 這個事務還未提交

​ 然後再開啟一個視窗:

​ 查到了一個未提交的事務提交的資料

​ 發生了髒讀

  • 事務的隔離級別為已提交讀
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

已提交讀

再開一個會話:

​ Snipaste_2022-03-12_15-52-17

不可重複讀

出現了不可重複讀:

不可重複讀-1

  • 隔離級別為可重複讀
SET GLOBAL TRANSACTION ISOLATION LEVEL  REPEATABLE READ;

可重複讀測試

Snipaste_2022-03-12_16-06-09

上面我們講到MySQL在該級別下可以做到禁止幻讀的，我們這裡來測試一下:

幻讀演示-1

​ 這張圖打錯了，是5和6才對。

幻讀演示-2

下面我們來分別講述MySQL是如何實現隔離級別、ACID的。

redo 原子性 永續性

在《MySQL優化學習手札(一)》，我們講到MySQL以頁為單位作為磁碟和記憶體的基本的互動單位，增刪改查事實上都是在訪問頁面(讀、寫、建立新頁面)，雖然我們是訪問頁面但是我們訪問的並不是磁碟的頁面，而是快取池的頁面，由工作執行緒定時將快取池的更新頁面重新整理到磁碟上，那麼問題來了，某個頁面的資料被改變，還沒有來得及將此頁面重新整理到磁碟上，碰到了一些故障，MySQL是如何保證永續性呢？ 所謂永續性就是指對一個已經提交的事務，在事務提交後，即使系統發生了崩潰，這個事務對資料庫中所做的更改也不能丟失。

簡單而無腦的做法是在更新buffer pool的資料頁之後，立刻將該頁重新整理到磁碟上，但是重新整理一個完整的資料頁太浪費了，有的時候我們可能只改動了某個頁面中的某行資料的一個欄位，這重新整理到磁碟上花費的代價有點大。 其次假設這個事務雖然只有一條語句，但是修改了很多頁的資料，又不巧，這些頁不相鄰，這就很慢。

MySQL的做法是儲存修改資料的元資訊，比如將Student的id = 1這一列的name改為張三, MySQL就會儲存這條資料在那個資料頁的某某行資料的某某列改為張三，取增量，記錄變化。這樣在我們事務提交後，我們將改變重新整理到磁碟中，即使工作執行緒還沒有來得及將快取池的頁重新整理到磁碟上，系統崩潰了，再重啟的時候我們根據這些記錄的改變再恢復一下資料即可，記錄改變的資料在MySQL中被稱為重做日誌，特就是redo log。 與事務提交時將所有修改過的記憶體中頁面重新整理到磁碟中相比，直接將事務執行過程中產生的redo日誌重新整理到磁碟好處如下:

  • redo 日誌佔用的空間非常小
  • redo日誌按順序寫入磁碟

那原子性呢，其實也是藉助redo日誌，在執行這些保證原子性的操作時必須以組的形式來記錄redo 日誌，在進行資料恢復的時候，系統中的某個組的日誌要麼全部恢復，要麼全部不恢復。redo 日誌也有自己的快取區，也並不是直接重新整理到磁碟上。

undo 日誌 回滾

如果事務執行了一半，系統斷電了怎麼辦，又或者手動執行了回滾，我們該如何回滾，答案是記錄一下改變，即將什麼改變成了什麼(這裡的改動指的是UPDATE INSERT，UPDATE)，MySQL將這些記錄改變的資料稱為undo log ，不同型別的update log 不同。如果某個事務對某個表執行了增、刪、改這樣的操作，InnoDB引擎為這個事務分配一個唯一的事務id。上面我們嘮叨了，MySQL以頁為單位作為磁碟和記憶體的基本互動單位，頁裡面是行記錄，每行會有多個隱藏列:

  • trx_id： 每次一個事務對某條聚簇索引記錄進行改動時，都會把該事務得到事務id賦值給trx_id隱藏列。
  • roll_pointer: 每次對某條聚簇索引記錄進行改動時，都會把舊的版本寫入到undo 日誌中，然後這個隱藏列就相當於一個指標，可以通過它來記錄修改前的資訊。

那可能有同學會問，那多條事務更新一條記錄怎麼辦，MySQL會讓他們排隊執行，可以理解為鎖，我們來試試看，兩個事務同時更新一條記錄怎麼辦?

先不提交

另一條語句卡在這裡

過了一會就會出現 Lock wait timeout exceeded; try restarting transaction.

每次對記錄進行改動，都會記錄一條undo 日誌，每條undo 日誌 也都會有roll_pointer屬性，這些日誌可以串起來成一條連結串列。版本鏈的頭結點記錄的是當前記錄最新的值，每個版本還包含一個事務 ID。對於隔離級別是READ UNCOMMITED的事務來說，由於可以讀取到未提交事務修改過的資料，所以直接讀取最新版本就好。對於READ COMMITED和REPEATABLE READ隔離級別的事務來說，都必須保證讀到已經提交過的事務，也就是說如果當前事務未提交，是不能讀取最新的版本記錄的，那現在的問題就是該讀取連結串列中的哪條記錄，由此我們就引出READ VIEW這個概念。

READ VIEW的生成時機 MVCC

READ VIEW有四個比較重要的內容:

  • m_ids: 表示在生成ReadView時當前系統中活躍的讀寫事務的事務ID列表
  • min_trx_id: 表示在生成ReadView時當時系統活躍的讀寫事務中的最小事務ID，也就是m_ids的最小值。
  • max_trx_id: 表示在生成ReadView時系統應該分配給下一個事務的ID。

  • creator_trx_id: 表示生成該ReadView的事務的事務Id。

    如果訪問版本的trx_id與READ VIEW中的creator_trx_id表名當前事務再訪問它自己修改的記錄，直接訪問連結串列最新的頭結點即可。

    如果被訪問版本的trx_id小於Read View中的min_trx_id值，表明生成該版本的事務在當前事務生成ReadView之前已經提交，所以該版本可以被當前事務訪問。

如果被訪問版本的trx_id 大於等於或Read View中的max_trx_id，表明生成版本的事務在當前事務生成Read View才開啟，所以該版本不可以被當前事務訪問。

如果被訪問版本的trx_id屬性值在ReadView的min_trx_id和max_trx_id之間，那就需要判斷trx_id的屬性值在不在m_ids中，如果在，說明建立ReadView時生成該版本的事務還是活躍的，該版本不可以訪問，如果不在，說明建立ReadView時生成該版本的事務已經被提交。

現在訪問資料的方式就是在遍歷資料對應的undo 連結串列，按照步驟判斷可見性，如果遍歷到最後都不可見，那就是真的不可見。

在MySQL中， READ COMMITED 和REPEATABLE READ隔離級別的一個非常大的區別就是生成ReadView時機不同。事務在執行過程中，只有在第一次真正修改記錄時(INSERT DELETE UPDATE)，才會被分配一個單獨的事務id, 這個事務id是遞增的。

下面我們舉一些例子來說明在不同隔離級別下，查詢時的過程。在故事的開始我們依然是準備一個表:

CREATE TABLE `student`  (
  `id` int(11) NOT NULL COMMENT '唯一標識',
  `name` varchar(255) COMMENT '姓名',
  `number` varchar(255) COMMENT '學號',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB

READ COMMITTED 每次查詢都生成一個 Read View

現在有兩個事務ID為200和300的正在執行，像下面這樣:

# 事務ID 為 200， id = 1 的name 在這個事務開始之前為 王哈哈
BEGIN;
update  student set name =  '李四' where id = 1;
update  student set name =  '王五' where id = 1;
# 事務ID 為 300
BEGIN;
# 做更新其他表的操作

這個時候id 為1這行記錄的版本鏈如下圖所示:

版本鏈

現在另一個事務開始查詢id=1這條記錄，執行SELECT語句即會生成一個Read View，Read View的值為[200,300],min_trx_id為200，max_trx_id為301，creator_trx_id為200。然後開始遍歷undo 連結串列，最新的版本是王五，trx_id = 200, 在min_ids中不符合可見性原則，訪問下一條記錄，下一條記錄的trx_id 為200，跳到下一個記錄。王哈哈的trx_id小於min_trx_id，然後將這行記錄返回給使用者。

REPEATABLE READ 第一次讀的時候生成一個Read View

還是上面的更新語句：

# 事務ID 為 200， id = 1 的name 在這個事務開始之前為 王哈哈
BEGIN;
update  student set name =  '李四' where id = 1;
update  student set name =  '王五' where id = 1;
# 事務ID 為 300
BEGIN;
# 做更新其他表的操作

然後使用REPEATABLE READ的隔離級別來查詢:

begin;
SELECT * FROM Student Where id = '1';

上面這個SELECT查詢會生成一個Read View: m_ids[200,300], min_trx_id=200,max_trx_id=301,creator_trx_id=0。

版本鏈

最新的版本的trx=id在min_ids中，該版本不可見，到下一條記錄，李四的trx_id也為200，也在min_id中，也不可見。王哈哈的版本id小於read view中的min_trx_id, 表明這個記錄在Reada View之前產生，返回該記錄。然後提交一下事務ID=200的操作。

BEGIN;
update  student set name =  '李四' where id = 1;
update  student set name =  '王五' where id = 1;
COMMIT;

然後事務ID 為 300也對id = 1進行修改。

begin;
update  student set name =  '徐四' where id = 1;
update  student set name =  '趙一' where id = 1;

現在的版本鏈就如下圖所示:

可重複讀-1

查詢id = 1的記錄:

begin;
SELECT * FROM Student Where id = '1';

之前已經產生過read view了，複用上面的read view， 然後當前記錄的事務id在min_ids[200,300]中，該記錄不可見, 跳到下一條記錄中，下一條的trx_id 為300，也在min_ids中，不可見，然後跳到下一條記錄，下條記錄的trx_id也在min_ids中，不可見。直到“王哈哈”，這也就是可重複讀的含義。即使事務ID為300的事務提交了，其他事務讀到了也會是“王哈哈”。對於這條記錄的事務全部提交之後，再次查詢該記錄會重新再產生Read View。這也就是MVCC(Multi-Version Concurrency Control 多版本併發訪問控制)，在READ COMMITTED、REPEATABLE READ隔離級別，避免髒讀、不可重複讀所採取的策略。READ COMMITE每次查詢都會生成一個Read View。 而REPEATABLE READ則是在第一次進行相關的記錄查詢的時候生成Read View，之後查詢複用這個Read View，被這些事務中查詢操作複用的Read View，在提交之後。再查詢對應的記錄的時候，再重新產生。

總結一下

MySQL下藉助undo、redo實現原子性、永續性、已提交讀，可重複讀。redo記錄記錄發生了什麼改變，undo用於回滾。為了支援MVCC，對應的記錄刪除之後刪掉不會立馬刪除而是會打上標記。好像是在剛畢業的時候就接觸到了MVCC，那個時候覺得這個很是高階和複雜，今天在寫這篇文章的時候，還是先從巨集觀入手，沒有介紹之前打算undo、redo日誌的格式相關的細節，根據我的經驗，介紹這些格式會很讓人頭暈，迷失在細節之中，其實初衷只是為了瞭解MySQL對ACID和事務的實現。所以本文只介紹了必要的內容，到後面的文章如果必須引用這些日誌的詳細介紹的時候會再介紹一遍。

參考資料

相關文章